Month: April 2018
Tech preview: PostgreSQL 11 – CREATE PROCEDURE
Many people have have been asking 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 […]
ora_migrator: migration from Oracle to PostgreSQL
As some of you might know, CYBERTEC has been helping customers with 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 […]
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 deleting […]
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 […]
Window function: Why first_value and last_value are not bugs
SQL is a must, if you want to be a Data Analyst or a Data Scientist. However, every once in a while people wonder why a result is the way it is. While on the road in Berlin (Germany) the other day, I found a fairly interesting window function scenario which is pretty counter-intuitive to […]