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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
PostgreSQL v13 new feature: tuning autovacuum on insert-only tables
© 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”). […]