The November commitfest is ripe with new interesting features! Without further ado, let’s proceed with the review. If you missed our July and September commitfest reviews, you can check them out here: 2023-07, 2023-09. ON LOGIN trigger Event triggers for REINDEX ALTER OPERATOR: commutator, negator, hashes, merges pg_dump --filter=dump.txt psql: displaying default privileges pg_stat_statements: track statement entry timestamps and reset min/max statistics pg_stat_checkpointer: checkpointer process statistics pg_stats: statistics for range type columns Planner: exclusion of unnecessary table se ..read more

We continue to follow the news of the PostgreSQL 17 development. Let’s find out what the September commitfest brings to the table. If you missed our July commitfest review, you can check it out here: 2023-07. Removed the parameter old_snapshot_threshold New parameter event_triggers New functions to_bin and to_oct New system view pg_wait_events EXPLAIN: a JIT compilation time counter for tuple deforming Planner: better estimate of the initial cost of the WindowAgg node pg_constraint: NOT NULL constraints Normalization of CALL, DEALLOCATE and two-phase commit control commands unaccent: the targ ..read more

I’m excited to announce that the translation of the “PostgreSQL 14 Internals” book is finally complete thanks to the amazing work of Liudmila Mantrova. The final part of the book considers each of the index types in great detail. It explains and demonstrates how access methods, operator classes, and data types work together to serve a variety of distinct needs. You can download a PDF version of this book for free. We are also working on making it available on a print-on-demand service. Your comments are very welcome. Contact us at edu@postgrespro.ru ..read more

I’m excited to announce that the translation of Part IV of the “PostgreSQL 14 Internals” book is published. This part delves into the inner workings of the planner and the executor, and it took me a couple of hundred pages to get through all the magic that covers this advanced technology. You can download the book freely in PDF. The last part is yet to come, stay tuned! I’d like to thank Alejandro García Montoro, Goran Pulevic, and Japin Li for their feedback and suggestions. Your comments are much appreciated. Contact us at edu@postgrespro.ru ..read more

We continue to follow the news in the world of PostgreSQL. The PostgreSQL 16 Release Candidate 1 was rolled out on August 31. If all is well, PostgreSQL 16 will officially release on September 14. What has changed in the upcoming release after the April code freeze? What’s getting into PostgreSQL 17 after the first commitfest? Read our latest review to find out! PostgreSQL 16 For reference, here are our previous reviews of PostgreSQL 16 commitfests: 2022-07, 2022-09, 2022-11, 2023-01, 2023-03. Since April, there have been some notable changes. Let’s start with the losses. The following up ..read more

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. Merge join The merge join algorithm takes two data sets that are sorted by the merge key and returns an ordered output. The input data sets may be pre-sorted by an index scan or be sorted explicitly. Merging sorted sets Below is an example of a merge join. Note the Merge Join node in t ..read more

A quick note to let you know that the PostgreSQL 14 Internals book is now available for orders on a worldwide print-on-demand service. You can buy hardcover or paperback version. PDF is still freely available on our website ..read more

The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let’s take a look at some exciting new updates it introduced. I hope that this review together with the previous articles in the series (2022-07, 2022-09, 2022-11, 2023-01) will give you a coherent idea of the new features of PostgreSQL 16. As usual, the March Commitfest introduces a ton of new changes. I’ve split them into several sections for convenience. Monitoring pg_stat_io: input/output statistics Counter for new row versions moved to another page when performing an UPDATE pg_buffercache: new pg_buffe ..read more

There are several ways to build a sharded database on top of distributed postgres instances. One of the most interesting and general approach is a built-in support for sharding. Historically postgres has fdw and partitioning features that can be used together to build a sharded database. There were concerns in the past about adoption them for a complete solution. We will review the current state of postgres fdw along with patches, that fix some significant bottlenecks in the planner and demonstrate the latest results in TPC-C with comparison to existing sharding solutions. Despite the promisin ..read more

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 walse ..read more