#​420 — September 1, 2021

Web Version

Postgres Weekly

Solving the 'Knapsack Problem' with Postgres — The ‘knapsack problem’ is the mathematical challenge of combining a set of items to get the largest value within a specified limit (a bit like packing things into a bag). Can Postgres help with combinatorial optimization? There’s a way.

Francesco Tisiot

Postgres Subtransactions Considered Harmful — The title is a bit clickbaity but nonetheless this is a detailed look at some of the downsides you can run into when using nested transactions (which numerous ORMs and frameworks might be using for you) and ways they could be improved.

Nikolay Samokhvalov

Instant Realtime GraphQL & REST APIs on Postgres in 30s — Connect Hasura to your new or existing PG databases to instantly get REST & GraphQL APIs with authorization baked in. Works on all flavours of Postgres including Citus, Timescale, Yugabyte, postgis and vanilla PG. Open source, loved by devs, and downloaded over 300M times.

Hasura sponsor

Amazon Aurora Now Supports Postgres 13 — Amazon’s performance-oriented “it looks like Postgres, smells like Postgres, but isn’t exactly Postgres” database now has Postgres 13 compatibility.

Amazon Web Services

▶  MongoDB's CTO, Mark Porter, on Postgres and MongoDB — Two podcast episodes (of an hour each) digging deep with MongoDB’s CTO who has an extensive and broad history in the database world, including leading efforts on the AWS RDS and Aurora projects.

Joshua D. Drake podcast

How to Create Lots of Sample Time-Series Data with generate_series() — The generate_series() function provides a handy way to create data en masse such as for testing, playing around with SQL queries, or benchmarking.

Ryan Booz (Timescale)

The Power of a Single Missing Index — I think this is the first time we’ve linked to an item written in the form of a poem(!) Hans-Jürgen turns his literary hand to describing a situation where losing a single index can impede performance.

Hans-Jürgen Schönig

Crunchy Bridge: Fully Managed Multi-Cloud Postgres

Crunchy Bridge sponsor

UUID or Cached Sequences? — UUIDs might not be in sequence but they’re quick to generate and work well in highly scaled systems. Franck reflects on this property but also considers how tweaking the sequence cache size can make sequences scalable too.

Franck Pachot

How to Get an Advisory Lock in the Shell? — Certainly a curious use for Postgres’s advisory locks.

Hubert depesz Lubaczewski

Insert-Only Tables and Autovacuum Issues Prior to Postgres 13 — If you have insert-only tables in a database on earlier than Postgres 13, this post examines how you could benefit from running a regularly scheduled VACUUM.

David Christensen