What’s in an xmax?
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 […]
Get rid of your unused indexes!
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 […]
idle_in_transaction_session_timeout: Terminating idle transactions in PostgreSQL
When running PostgreSQL on a production system, it might happen that you are facing table bloat. As you might know PostgreSQL has to copy a row on UPDATE to ensure that concurrent transactions can still see the data. At some point VACUUM can clean out dead rows but if transactions are too long, this cleanup […]
Advanced SQL: Why first_value and last_value are no bugs
SQL is a must, if you want to be a Data Analyst or a Data Scientist. However, one in a while people are wondering, why a result is the way it is. While on the road in Berlin (Germany) the other day I have found a fairly interesting scenario, which is pretty counterintuitive to most […]
Improving transaction latency by moving indexes to faster media
Transaction performance topic being as actual as ever in spite of constant hardware improvements, we’re quite often asked how to improve in that area. But the truth is that when the most important PostgreSQL configuration paramaters are already more or less tuned, it is mostly really hard to magically squeeze that extra something out of […]
PostgreSQL: Speeding up analytics and windowing functions
As a professional PostgreSQL support company we see a lot of SQL performance stuff, which is often worth sharing. One of those noteworthy things, which might be interesting to you, the reader, happened this week when I was out on a business trip to Berlin, Germany. This (excellent) customer was making extensive use of windowing […]