#​471 — September 14, 2022

Web Version

Postgres Weekly

Choosing a Primary Key Identifier Format: The Options — Figuring out what data, or even composite of data, to use for a primary key can be tricker than you’d expect, and if you go with a common unique ID approach, which should you use? Victor tours some of Postgres’s many options along with their pros and cons before showing off an extension he’s built to help you generate IDs too.

Victor Vados

pg_netstat: Monitor Your Database's Network Traffic — Runs a background worker that uses libpcap to capture packets and aggregates at an interval of your choice. You can then query your database’s realtime network stats through a pg_netstat view to see packets in and out, bytes in and out, etc.


PDF Download: Understanding Kubernetes — Updated for 2022! This resource covers everything from fundamental concepts, to cluster components and network model implementation. The new edition includes the Kubernetes Dashboard, high-availability control plane, and autoscaling. Get it instantly.

Linode sponsor

Working with Transaction Snapshots — This is another handy feature to have in one’s back pocket. Snapshots allow multiple transactions to share identical views of the same database state. This can come in handy when allowing multiple pg_dump instances to more quickly build a single dump using the -j option, or in more complex data syncing scenarios (there’s a neat example provided in the post).

Robert Bernier

PostgreSQL 15 Beta 4 Released — One downside to being weekly is when a release drops the day after you publish, and so it went with last Thursday’s release ;-) Luckily it’s a relatively minor step on the road to Postgres 15, mostly fixing up bugs and crashes.

PostgreSQL Global Development Group

'6 Useful Postgres Features I Wish I Knew When I Started' — Identity syntax (vs SERIAL), COALESCE + NULLIF, grouping sets, CTEs, and domains all make an appearance.

Marat Badykov

What's an INNER JOIN or OUTER JOIN in SQL? — Steer clear if you’re already a join expert, but otherwise this is a nice introduction / refresher that also breezes through full joins and ‘semi-joins.’

Hans-Jürgen Schönig

What pgcrypto Does — It made us laugh, but sadly we had to change Paul’s actual ‘crypto’ related headline to avoid hitting the spam filters ;-) Nonetheless, he takes a practical, high level look at the options pgcrypto presents for encrypting and decrypting data inside a database.

Paul Ramsey

How and Why Citus Took Distributed Postgres Fully Open Source — The inside story on why Citus / Microsoft open sourced their few remaining enterprise features in Citus 11, what exactly was open sourced, and what it took to make it happen.

Jelte Fennema (Citus Data)

🛠 Code and Tools

pg_idkit: An Extension for Generating IDs — Covered in the Choosing a Postgres Primary Key article featured above, this Rust-built extension offers up a myriad of different unique ID schemes from UUIDs to NanoIDs and ksuids.


Move Mission Critical Applications to the Cloud…Faster

EDB BigAnimal sponsor

Apache AGE 1.1.0: Graph Extensions for Postgres — AGE (A Graph Extension) was inspired by AgensGraph, a multi-model database Bitnine built from PostgreSQL 10, and brings graph processing and analytics features to Postgres via both OpenCypher and SQL. Homepage.

Apache Foundation