Any long-time user of Postgres is likely familiar with VACUUM, the process that ensures old data tuples are identified and reused to prevent unchecked database bloat. This critical element of maintenance has slowly, but surely, undergone incremental enhancements with each subsequent release. Postgres 12 is no different in this regard. In fact, there are two […]

Window functions are a very useful tool; and since their introduction 10 years ago, they have been gradually adopted for various use cases where a simple aggregation is not flexible enough: for example incremental totals, moving averages, etc. The latest features introduced in PostgreSQL complete the implementation according to the SQL:2011 standard, adding capabilities such […]

In a developer’s ideal world there’s only one stable and one development version of the product. Everyone updates immediately to the next stable version and nobody cares about anything older. As far as I can tell this has never actually happened, ever, in the history of anything. Working on many versions PostgreSQL is a mature […]

When I wrote about Reason #3: No Vendor Lock-in, I leaned on the liberal PostgreSQL License and the fact that there are many vendors that can Support PostgreSQL in production. Another aspect that deserves its own mention is the fact that PostgreSQL can not be bought. Let me explain the significance of that statement. MySQL. […]

A regular expression is a special text string used to describe a search pattern. PostgreSQL’s regular expressions supports three separate approaches to pattern matching: POSIX-style regular expressions (BREs and EREs) SIMILAR TO operator added in SQL:1999 SQL LIKE operator There are some more advanced techniques for advanced pattern matching requirements but those will very likely […]

Vacuuming is a key feature of PostgreSQL databases to keep databases healthy and optimized. For this, Autovacuum is configured to conserve space by removing obsolete disk usage, and to optimize database performance by speeding up sequential scans, just as one example. To discuss this topic in-depth, 2ndQuadrant hosted the “Postgres Vacuuming Through Pictures” webinar. The […]

PostgreSQL was presented with the 2019 O’Reilly Open Source Award for Lifetime Achievement in Portland, Oregon, Thursday July 18th during OSCON. I had the honor of accepting that award alongside Bruce Momjian and Christophe Pettus. Starting last year in 2018, these awards were presented to projects and the first Lifetime Achievement award was given to […]

Declarative partitioning got some attention in the PostgreSQL 12 release, with some very handy features. There has been some pretty dramatic improvement in partition selection (especially when selecting from a few partitions out of a large set), referential integrity improvements, and introspection. In this article, we’re going to tackle the referential integrity improvement first. This […]

Does anyone really know what time it is? First we have the bare bones of the PostgreSQL Interval, Date and Timestamp Data types. Here are the questions: What types are they? And what options do they have? What postgresql.conf variables affect date and time i/o? Available Date and Time Data types Date day of year […]