Is UPDATE the same as DELETE + INSERT in PostgreSQL?

© Laurenz Albe 2020 Introduction We know that PostgreSQL does not update a table row in place. Rather, it writes a new version of the row (the PostgreSQL term for a row version is “tuple”) and leaves the old row version in place to serve concurrent read requests. VACUUM later removes these “dead tuples”. If […]

Read more

BLOB cleanup in PostgreSQL

PostgreSQL offers a nice BLOB interface which is widely used. However, recently we came across problems faced by various customers, and it makes sense to reflect a bit and figure out how PostgreSQL handles BLOBs – and especially BLOB cleanup. Using the PostgreSQL BLOB interface In PostgreSQL, you can use various means to store binary […]

Read more

Dealing with streaming replication conflicts in PostgreSQL

© Laurenz Albe 2020 Streaming replication in PostgreSQL is a well-established master-slave replication technique. It is simple to set up, stable and performs well. So many people are surprised when they learn about replication conflicts — after all, the standby server is read-only. This article describes replication conflicts and tells you how to deal with […]

Read more

HOT updates in PostgreSQL for better performance

© Laurenz Albe 2020 HOT updates are not a new feature. They were introduced by commit 282d2a03dd in 2007 and first appeared in PostgreSQL 8.3. But since HOT is not covered by the PostgreSQL documentation (although there is a README.HOT in the source tree), it is not as widely known as it should be: Hence […]

Read more

Tuning PostgreSQL autovacuum

© Laurenz Albe 2020 In many PostgreSQL databases, you never have to think or worry about tuning autovacuum. It runs automatically in the background and cleans up without getting in your way. But sometimes the default configuration is not good enough, and you have to tune autovacuum to make it work properly. This article presents […]

Read more

Understanding recursive queries in PostgreSQL

© Laurenz Albe 2020 Many people consider recursive queries a difficult topic. Still, they enable you to do things that would otherwise be impossible in SQL. This articles gives a simple introduction with examples and shows the differences to Oracle’s implementation of recursive queries. Common table expressions (WITH clauses) A common table expression (CTE) can […]

Read more

Join strategies and performance in PostgreSQL

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

Read more

Deduplication in PostgreSQL v13 B-tree indexes

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

Read more

Binary data performance in PostgreSQL

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

Read more