Blog

  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 my last post I described what to expect from simple PL/pgSQL triggers in performance degradation sense, when doing some inspection/changing on the incoming row data. Conclusion for the most common “audit fields” type of use case was that we should not worry about it too much and just create those triggers. But in which […]

I have already written about timeseries and PostgreSQL in the past. However, recently I stumbled across an interesting problem, which caught my attention: Sometimes you might want to find “periods” of activity in a timseries. For example: When was a user active? Or when did we receive data? This blog post tries to give you […]

In PostgreSQL, a view is a virtual table based on an SQL statement. It is an abstraction layer, which allows to access the result of a more complex SQL fast an easily. The fields in a view are fields from one or more real tables in the database. The question many people now ask if: […]

First of course the big question – should we be using good old triggers at all? Well, actually I’m not going to recommend anything here as it’s an opinionated topic:) People well-versed in databases would probably see good use cases for them whereas modern application developers would mostly say it’s an awful practice – doing […]

For those of you out there working with PostgreSQL in a professional way, migrating from Oracle to PostgreSQL might be one of the most beloved tasks available. One of the first things most people will notice, however, is that those data types available in Oracle might not be quite the same in PostgreSQL. This blog […]

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

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

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

Recently I did some PostgreSQL consulting in the Berlin area (Germany) when I stumbled over an interesting request: How can data be shared across function calls in PostgreSQL? I recalled some one of the other features of PostgreSQL (15+ years old or so) to solve the issue. Here is how it works. Stored procedures in […]

Many people have asked for this feature for years and PostgreSQL 11 will finally have it. I am of course talking about CREATE PROCEDURE. Traditionally PostgreSQL has provided all the means to write functions (which were often simply called “stored procedures”). However, in a function you cannot really run transactions – all you can do […]

As some of you might know Cybertec has been doing PostgreSQL consulting, tuning and 24×7 support for many years now. However, one should not only see what is going on in the PostgreSQL market. It also makes sense to look left and right to figure out what the rest of the world is up to […]