Index bloat reduced in PostgreSQL v14

© Laurenz Albe 2021 PostgreSQL v12 brought more efficient storage for indexes, and v13 improved that even more by adding deduplication of index entries. But Peter Geoghegan is not done yet! PostgreSQL v14 will bring “bottom-up” index entry deletion, which is targeted at reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes. […]

Read more

Understanding LATERAL joins in PostgreSQL

LATERAL joins are one of the lesser-known features of PostgreSQL and other relational databases such as Oracle, DB2 and MS SQL. However, LATERAL joins are a really useful feature, and it makes sense to take a look at what you can accomplish with them. Inspecting FROM more closely Before we dive into LATERAL, it makes […]

Read more

From MD5 to scram-sha-256 in PostgreSQL

© 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. […]

Read more

About cross join in PostgreSQL

© Laurenz Albe 2021 For many people, “cross join” is something to be afraid of. They remember the time when they forgot the join condition and the DBA was angry, because the query hogged the CPU and filled the disk. However, there are valid use cases for cross joins which I want to explore in […]

Read more

How to interpret PostgreSQL EXPLAIN ANALYZE output

© 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 […]

Read more

What to return from a PostgreSQL row level trigger?

© 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 […]

Read more

When to use tablespaces in PostgreSQL

© Laurenz Albe 2021 Users with an Oracle background consider tablespaces very important and are surprised that you can find so little information about them in PostgreSQL. This article will explain what they are, when they are useful and whether or not you should use them. What is a tablespace Essentially, a tablespace in PostgreSQL […]

Read more

New target_session_attrs settings for high availability and scaling in PostgreSQL v14

© Laurenz Albe 2021 PostgreSQL commit ee28cacf61 has added new options for the target_session_attrs connection parameter. This article explores how the new options can be used to simplify high availability and horizontal scaling solutions. What is target_session_attrs? The PostgreSQL client shared library libpq has support for connection strings to more than one database server: In […]

Read more

Estimating connection pool size with PostgreSQL database statistics

© Laurenz Albe 2021 PostgreSQL v14 has new connection statistics in pg_stat_database. In this article, I want to explore one application for them: estimating the correct size for a connection pool. New connection statistics in v14 Commit 960869da080 introduced some new statistics to pg_stat_database: session_time: total time spent by sessions in the database active_time: time […]

Read more

The shibboleth of PostgreSQL

© Laurenz Albe 2020 After all the technical articles I have written, I thought it would be nice to write about PostgreSQL sociology for a change. Language and community A community like PostgreSQL has no clearly defined borders. There is no membership certificate; you belong to it if you feel that you belong. That said, […]

Read more