Having recently witnessed quite an ingenious hack to implement some good old “println” style development debugging from stored procedures into a file, it prompted me to post knowledge to the Interwebs on two other ways how such a goal can be implemented more transparently. Also with help of some other good old legacy technology in […]

I’ve written already a blog post “Building PostgreSQL with MSYS2 and MinGW under Windows” a year ago. But this time I want to show you two important things: cross compiling (x86, x64) using the same run-time; compiling with the latest OpenSSL support. That’s true nowadays you won’t find binaries or installations of PostgreSQL without OpenSSL […]

  It is a frequent complaint that count(*) is so slow on PostgreSQL. In this article I want to explore the options you have get your result as fast as possible. Why is count(*) so slow? Most people have no trouble understanding that the following is slow: After all, it is a complicated query, and […]

Pavel Stehule recently wrote the post “Don’t use SQL keywords as PLpgSQL variable names” describing the situation when internal stored routine variable names match PostgreSQL keywords. But the problem is not only in keywords. Consider: Output: OK, at least we have no hidden error like in Pavel’s case. Let’s try to fix it specifying an […]

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

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

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

Preface There are several ways of building PostgreSQL under Windows. Official manual stands using Visual Studio is the simplest one, wiki describes how to use mingw and mingw-w64. As for me, using new MSYS2 building platform for Windows is a bless. With its help not only PostgreSQL sources are built smoothly, but even extensions are […]

Why should I get rid of unused indexes? Everybody knows that a database index is a good thing because it can speed up SQL queries. But this does not come for free. The disadvantages of indexes are: Indexes use up space. It is not unusual for database indexes to use as much storage space as […]