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

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