© Laurenz Albe 2020 There are three join strategies in PostgreSQL that work quite differently. If PostgreSQL chooses the wrong strategy, query performance can suffer a lot. This article explains the join strategies, how you can support them with indexes, what can go wrong with them and how you can tune your joins for better […]

© Laurenz Albe 2020 A while ago, I wrote about B-tree improvements in v12. PostgreSQL v13, which will come out later this year, will feature index entry deduplication as an even more impressive improvement. So I thought it was time for a follow-up. Deduplication for B-tree indexes If the indexed keys for different table rows […]

© Laurenz Albe 2020 A frequently asked question in this big data world is whether it is better to store binary data inside or outside of a PostgreSQL database. Also, since PostgreSQL has two ways of storing binary data, which one is better? I decided to benchmark the available options to have some data points […]

© Laurenz Albe 2020 Most people know that autovacuum is necessary to get rid of dead tuples. These dead tuples are a side effect of PostgreSQL’s MVCC implementation. So many people will be confused when they read that from PostgreSQL v13 on, commit b07642dbc adds support for autovacuuming insert-only tables (also known as “append-only tables”). […]

© Laurenz Albe 2020 In my daily work, I see many databases with a bad setting for max_connections. There is little advice out there for setting this parameter correctly, even though it is vital for the health of a database. So I decided to write up something. What is max_connections? According to the documentation, max_connections […]

© Laurenz Albe 2020   Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post. What are subtransactions? Everybody knows database transactions. In PostgreSQL, which is operating in autocommit mode, you have to start a transaction that spans multiple statements […]

The PostgreSQL caching system has always been a bit of a miracle to many people and many have asked me during consulting or training sessions: How can I figure out what the PostgreSQL I/O cache really contains? What is in shared buffers and how can one figure out? This post will answer this kind of […]

© Laurenz Albe 2019 The biggest change in PostgreSQL v12 from the viewpoint of backward compatibility is that recovery.conf has been absorbed into postgresql.conf. This article describes the changes and how you can adapt to them. Getting rid of recovery.conf Up to now, the presence of the file recovery.conf was the trigger for PostgreSQL to […]

  With the latest major version freshly released, it’s again time to gauge its performance. I’ve already been doing this for years, and I have my scripts ready, so it’s not too much work for me – but rather, for the machines. The v12 release, as always, adds quite a lot of nice stuff. Some […]

© Laurenz Albe 2019   “Durability”, the D of ACID, demands that a committed database transaction remains committed, no matter what. For normal outages like a power failure, this is guaranteed by the transaction log (WAL). However, if we want to guarantee durability even in the face of more catastrophic outages that destroy the WAL, […]

  In another blog post, I described how Linux control groups work and how they are useful for PostgreSQL. Here I will present my little PostgreSQL plugin pg_cgroups which makes this simple to handle for the DBA. What is pg_cgroups good for? If you have a machine dedicated to a single PostgreSQL cluster, you normally […]

  We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it: Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is. In this article I want to explore the mechanics […]