© Laurenz Albe 2021 Since v10, PostgreSQL has provided support for scram-sha-256 for password hashing and authentication. This article describes how you can adapt your application safely. Why do we need scram-sha-256? PostgreSQL uses cryptographic hashing for two purposes: The actual database password is a hash of the clear text password entered by the user. […]

© Laurenz Albe 2021 When analyzing customer’s problems, I have seen the mess you can get into if you disable autocommit in your interactive client, so I’d like to bring this topic to a wider audience. What is autocommit? In PostgreSQL, like in any other ACID-complicant database, each statement runs in a transaction: if the […]

© Laurenz Albe 2021 EXPLAIN ANALYZE is the key to optimizing SQL statements in PostgreSQL. This article does not attempt to explain everything there is to it. Rather, I want to give you a brief introduction, explain what to look for and show you some helpful tools to visualize the output. How to call EXPLAIN […]

Bulk loading is the quickest way to import large amounts of data into a PostgreSQL database. There are various ways to facilitate large-scale imports, and many different ways to scale are also available. This post will show you how to use some of these tricks, and explain how fast importing works. You can use this […]

© Laurenz Albe 2021 Sometimes customers ask me about the best choice for auto-generated primary keys. In this article, I’ll explore the options and give recommendations. Why auto-generated primary keys? Every table needs a primary key. In a relational database, it is important to be able to identify an individual table row. If you wonder […]

Performance tuning does not only mean adjusting postgresql.conf properly, or making sure that your kernel parameters are properly tuned. Performance tuning also implies that we have to find performance bottlenecks first, isolate slow queries and understand what the system is doing. I believe the best and most efficient way to detect performance problems is to […]

There are two types of replication available in PostgreSQL at the moment: Streaming replication & Logical replication. If you are looking to set up streaming replication for PostgreSQL 13, this is the page you have been looking for. This tutorial will show you how to configure PostgreSQL replication and how to set up your database servers […]

Many might have seen PostgreSQL issue the following error message: “ERROR: deadlock detected”. But what does it really mean? How can we prevent a deadlock and how can we reproduce the problem? Let’s dive into PostgreSQL locking and understand what deadlock and deadlock_timeout really mean. How does a deadlock happen? Many people approach us because […]

© Laurenz Albe 2021 In this article, I’ll talk about row level triggers, which are the most frequently used kind of triggers. I will describe what the return value of the trigger function means and suggest a useful code simplification. Triggers in PostgreSQL A trigger in PostgreSQL consists of two parts: a trigger function the […]

I don’t usually post about upcoming PostgreSQL features and rather concentrate on tools available / versions released… but this feature got me excited and will certainly be a huge relief for real-life usage, especially for beginners! I had almost lost hope we might see this day – but after many years of wishing for it, […]

PostgreSQL allows end users to store BLOBs (binary large objects) in the database. Many people use these functions to handle data directly in SQL. There has long been a discussion about whether this is a good thing or not. We expect that particular discussion to go on indefinitely. However, if you are in the “pro […]

Visualizing spatial data is an important task. In this mini-tutorial, you will learn how to quickly visualize OpenStreetMap (OSM) data with PostGIS and QGIS. The idea is to get you started as fast as possible, and to make the most out of your data. Installing QGIS QGIS is freely available as Open Source software and […]