December 31, 2022   •   PostgreSQL
We continue to follow the news of the upcoming PostgreSQL 16. The third CommitFest concluded in early December. Let's look at the results. If you missed the previous CommitFests, check out our reviews: 2022-07 , 2022-09 . Here are the patches I want to talk about: meson: a new source code build system Documentation: a new chapter on transaction processing psql: \d+ indicates foreign partitions in a partitioned table psql: extended query protocol support Predicate locks on materialized views Tracking last scan time of indexes and tables pg_buffercache: a new function pg_buffercache_summary walsender displays the database name in the process status Reducing the WAL overhead of freezing tuples Reduced power consumption when idle postgres_fdw: batch mode for COPY Modernizing the GUC infrastructure Hash index build optimization MAINTAIN ― a new privilege for table maintenance SET ROLE: better role change management Support for file inclusion directives in pg_hba.conf and pg_ident.conf Regular expressions support in pg_hba.conf
November 14, 2022   •   PostgreSQL
It’s official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release. Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code. If you missed the July CommitFest, our previous article will get you up to speed in no time. Here are the patches I want to talk about: SYSTEM_USER function Frozen pages/tuples information in autovacuum's server log pg_stat_get_backend_idset returns the actual backend ID Improved performance of ORDER BY / DISTINCT aggregates Faster bulk-loading into partitioned tables Optimized lookups in snapshots Bidirectional logical replication pg_auth_members: pg_auth_members: role membership granting management pg_auth_members: role membership and privilege inheritance pg_receivewal and pg_recvlogical can now handle SIGTERM
October 6, 2022   •   PostgreSQL
Queries in PostgreSQL: 7. Sort and merge
In the previous articles, covered query execution stages , statistics , sequential and index scan , and two of the three join methods: nested loop and hash join . This last article of the series will cover the merge algorithm and sorting . I will also demonstrate how the three join methods compare against each other.
August 17, 2022   •   PostgreSQL
August is a special month in PostgreSQL release cycle. PostgreSQL 15 isn't even officially out yet, but the first CommitFest for the 16th release has already been held. Let's compile the server and check out the cool new stuff!
August 11, 2022   •   PostgreSQL
So far we have covered query execution stages , statistics , sequential and index scan , and have moved on to joins. The previous article focused on the nested loop join , and in this one I will explain the hash join . I will also briefly mention group-bys and distincs.
July 5, 2022   •   PostgreSQL
So far we've discussed query execution stages , statistics , and the two basic data access methods: Sequential scan and Index scan . The next item on the list is join methods. This article will remind you what logical join types are out there, and then discuss one of three physical join methods, the Nested loop join. Additionally, we will check out the row memoization feature introduced in PostgreSQL 14. Joins Joins are the primary feature of SQL, the foundation that enables its power and agility. Sets of rows (whether pulled directly from a table or formed as a result of an operation) are always joined together in pairs. There are several types of joins. ...