--- title: "A deep dive into the perils of subtransactions" publication: "Postgres Weekly" issue: 420 published_at: "2021-09-01" canonical_url: "https://postgresweekly.com/issues/420" --- Plus Aurora gets PG 13 compatability, and how to generate lots of time series data on the fly. | #​420 — September 1, 2021 [Web Version](https://postgresweekly.com/issues/420) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/jnxtjwrgsljajf5mvegc.jpg)](https://aiven.io/blog/solving-the-knapsack-problem-in-postgresql) [Solving the 'Knapsack Problem' with Postgres](https://aiven.io/blog/solving-the-knapsack-problem-in-postgresql) — The [‘knapsack problem’](https://en.wikipedia.org/wiki/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](https://postgres.ai/blog/20210831-postgresql-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 [![\](https://copm.s3.amazonaws.com/4e63892b.png)](https://hasura.io/?utm_campaign=postgres-weekly-sep2021&utm_medium=email&utm_source=newsletter) [Instant Realtime GraphQL & REST APIs on Postgres in 30s](https://hasura.io/?utm_campaign=postgres-weekly-sep2021&utm_medium=email&utm_source=newsletter) — 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](https://aws.amazon.com/about-aws/whats-new/2021/08/amazon-aurora-postgresql-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](https://www.commandprompt.com/blog/mark-porter-cto-mongodb-on-postgresql-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()`](https://blog.timescale.com/blog/how-to-create-lots-of-sample-time-series-data-with-postgresql-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](https://www.cybertec-postgresql.com/en/postgresql-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](https://www.crunchydata.com/products/crunchy-bridge/?utm_source=Postgres+Weekly&utm_medium=newsletter&utm_campaign=Bridge&utm_id=crunchy+bridge&utm_content=9-01-21) Crunchy Bridge sponsor [UUID or Cached Sequences?](https://dev.to/yugabyte/uuid-or-cached-sequences-42fi) — 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?](https://www.depesz.com/2021/08/26/how-to-get-advisory-lock-in-shell/) — Certainly a curious use for Postgres’s [advisory locks](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS). Hubert depesz Lubaczewski [Insert-Only Tables and Autovacuum Issues Prior to Postgres 13](https://blog.crunchydata.com/blog/insert-only-tables-and-autovacuum-issues-prior-to-postgresql-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 **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)