#​517 — August 2, 2023

Web Version

Together with  pgAnalyze

Postgres Weekly

The State of PostgreSQL 2023 Survey is Now Open — Starting in 2019, Timescale’s annual Postgres survey has become a popular way to monitor the feelings of the broader community. Here’s last year’s results, if you’re curious. As well as sharing the results, Timescale makes the raw (anonymized) data available for third party analysis too.


🔓  A Way to Look at Postgres Lock Conflicts — While Postgres has two broad types of lock – shared and exclusive – there are various levels of granularity and a birds’ nest of ways in which different commands affect or conflict with each other through their locking requirements. This site/tool shows all lock types and how they interact with different commands.

Hussein Nasser

Introducing the New pganalyze VACUUM Advisor for Postgres — pganalyze VACUUM Advisor provides per-table recommendations for autovacuum settings to optimize table bloat, freezing, VACUUM performance, and more. Implement its recommendations to optimize space usage, prevent bloat, and improve query performance.

pganalyze sponsor

An Introduction to Table Partitioning — Postgres has support for three forms of table partitioning where tables can be divided into multiple partitions for a variety of performance, maintenance, or logistical benefits. This post gives some basic examples of the three types of partitioning.

Matteo Crosta


Comparing the Performance of Bulk Loading Techniques — A quick comparison of the basic approaches for rapidly bulk inserting data into a table, whether through a single or multiple transactions, whether using INSERTs or COPY. Unsurprisingly, COPY is fastest, being designed for the task, but you can use INSERT in a way that means it’s not too far behind.

Laurenz Albe

Setting the Record Straight: More Updates on a Trademark Dispute — The PostgreSQL Community Association (PGCA) and Fundación PostgreSQL are currently locked in a dispute over EU trademarks relating to Postgres. This is the PGCA’s take, but in the interests of balance, you can also see what Álvaro Hernández of Fundación PostgreSQL thinks.

PostgreSQL Community Association

A Possible Way to Implement a Shift Function in PL/PgSQL — 'Shift' meaning a function that takes an array and both removes and returns the first/left-most element.

Luca Ferrari

A PL/PgSQL Simple Roman Number Translator — More PL/PgSQL shenanigans.

Luca Ferrari

Code and Tools

PLJS: JavaScript Language Plugin for PostgresPLV8 is arguably the ‘go to’ way to use JavaScript as a procedural language within Postgres, but this QuickJS-based variant, from the same creator, is more compact, easier to maintain, and may be enough for your needs.

Jerry Sievert

Multi-Cloud Made Easy. Deploy the Same Postgres on Any Cloud. Start with $300 in Credits

EDB BigAnimal sponsor

'Rusting' a New Postgres C Extension to Count Subtransactions — A subtransaction, or nested transaction, is a transaction that begins within another transaction, and while useful, performance implications exist. pg_subxact_counters is a new extension written in both C and Rust to count such subtransactions, and it's also useful as a way to compare the implementation of extensions in both languages.

Bertrand Drouvot