Posts

  Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance. Through a recent support case I learned that there are even worse effects of explicit table locks. Table locks Before an SQL statement […]

  The “exclusive backup” method of calling pg_start_backup(‘label’) before backup and pg_stop_backup() afterwards is scheduled for removal in the future. This article describes the problems with the old method and discusses the options for those who still use this backup method. The “exclusive” backup method Before pg_basebackup was invented, there was only one online file-system […]

pgbouncer is the most widely used connection pooler for PostgreSQL. This blog will provide a simple cookbook how to configure user authentication with pgbouncer. I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site. But it should work pretty similarly anywhere.   What is […]

Prepared transactions are disabled in PostgreSQL by default, since the parameter max_prepared_transactions has the default value 0. You don’t need prepared transactions in most cases. However, they can cause nasty problems, so I think that everybody who runs a PostgreSQL database should understand them. To illustrate these problems, I’ll show you how to use prepared […]

  Since I only recently learned about the concept of “killed index tuples”, I thought there might be some others who are not yet familiar with this interesting PostgreSQL concept. This may give you an explanation the next time you encounter wildly varying execution times for the same execution plan of the same PostgreSQL query. […]

  Foreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables. A fact that is often ignored is that foreign keys need proper indexing to perform well. This article will explain that and show you how to search for missing indexes. Index at the target of a […]

After you ANALYZE a PostgreSQL table to collect value distribution statistics, you will find the gathered statistics for each column in the pg_stats system view. This article will explain the meaning of the correlation column and its impact on index scans. Physical vs. logical ordering Most common PostgreSQL data types have an ordering: they support […]

  Inspired by my co-worker’s recent blog post, I decided to revisit the old question of rules vs. triggers and run a little benchmark to see which one does better. About rules While triggers are well known to most application developers and database administrators, rules are less well known. The full name “query rewrite rule” […]

  We all know that you have to pay a price for a new index you create — data modifying operations will become slower, and indexes use disk space. That’s why you try to have no more indexes than you actually need. But most people think that SELECT performance will never suffer from a new […]

  In a recent wrestling match with the Linux “out-of-memory killer” for a Cybertec customer I got acquainted with Linux control groups (“cgroups”), and I want to give you a short introduction how they can be used with PostgreSQL and discuss their usefulness. Warning: This was done on my RedHat Fedora 27 system running Linux […]

  PostgreSQL query tuning is our daily bread at Cybertec, and once you have done some of that, you’ll start bristling whenever you see an OR in a query, because they are usually the cause for bad query performance. Of course there is a reason why there is an OR in SQL, and if you […]

  xmax is a PostgreSQL system column that is used to implement Multiversion Concurrency Control (MVCC). The documentation is somewhat terse: The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the […]