~postgresweekly | Bookmarks (165)

pgslice: Postgres Partitioning 'As Easy As Pie' — A tool that can either generate or generate and execute the required SQL statements to partition either new or existing tables with nothing to install server-side. Written in Ruby.

pg-lock-tracer: A BPF-Based Lock Tracer — An interesting way to dig into lock-related bottlenecks in Postgres when running on Linux. It observes the locking activity of a Postgres process by using eBPF instrumentation.

Patroni 3.0: Template for Postgres High Availability — A template to create your own customized, high-availability solution using Python and ZooKeeper, etcd, Consul or Kubernetes. v3.0 notably adds Citus support.

pg-mem: An Experimental In-Memory Postgres Instance for Tests — Aimed at Node.js and browser-based use cases due to being written in TypeScript. There’s a playground if you want to experiment with what it can handle.

Transaction ID Wraparound: A Walk on the Wild Side — "For most people it is an abstract concept, a bogeyman lurking in the dark around the corner. Many people know horror stories about anti-wraparound autovacuum tanking performance or databases that stop working, but who has ever seen actual data loss? I decided to face the beast.." Take a journey with Laurenz and you could...

Ask HN: How Do You Test SQL? — Extensive discussion about the myriad of ways that people either do or don’t(!) test their SQL queries. “Try and write any complex SQL as a series of semantically meaningful CTEs” resonated with folks.

Coming to v16: Surviving Without a Superuser — A superuser in Postgres gets the highest privileges possible and can access anything, including, to some extent, the operating system. If you want a superuser in Postgres but not the OS, that’s tricky but becoming a lot easier in Postgres 16 thanks to this update. Robert goes into detail in this post.

Postgres's 'Supply Chain' — Every significant project relies on many others. Peter notes that a few of Postgres’s dependencies are quite fragile, although it seems none of the problems are unique to Postgres.

tbls: A CI-Friendly Tool for Documenting Databases — Document a database automatically in Markdown format with schemas rendered via DOT, PlantUML, Mermaid, or direct to images. By default you get documentation covering columns, indexes, relations, and other schema details (here’s a sample document). Written in Go.

pg_show_plans: Show Query Plans of All Currently Running Statements — Lets you get live access via SELECT * FROM pg_show_plans or you can join it against pg_stat_activity as demonstrated in the README.

pg_qualstats: Extension for Collecting Statistics About Predicates — Analyze the most often used predicates in queries against your database, perhaps as a way to create the most effective indexes possible. Part of the POWA (PostgreSQL Workload Analyzer) project.

▶  30+ Talks from PGConf NYC 2022 — We forgot to include this in December, but it’s still a good set of talks now whether it’s learning how to secure Postgres with Bruce Momjian, figuring out isolation with Lætitia Avrot, or learning more about the MERGE command with Simon Riggs (as in the image above).

A Unique Constraint Where NULL Conflicts with Everything — Laurenz showcases range data types and exclusion constraints in creating what appears, on the surface, to be an unusual uniqueness constraint. Some good left field thinking here.

Join Us for Season 2 of Our “5mins of Postgres” Video Series — After over 50 episodes in 2022, we are happy to get started with season 2 of 5mins of Postgres. Join pganalyze CEO Lukas Fittl for weekly videos on the most noteworthy Postgres content. Subscribe to our YouTube channel and enjoy tomorrow's episode!

Puzzling Postgres: A Story of Solving An Unreproducible Performance Issue — Going down the rabbit hole of query plans and prepared statements. “We had a query that was particularly slow in production, but it was very hard to reproduce as we didn’t know our ORM was using prepared statements under the hood and nothing was looking problematic with a simple EXPLAIN ANALYZE.”

▶  Walkthrough of Implementing a Simple Postgres Patch — If you’ve ever been curious about what’s involved in hacking directly on Postgres, this is an interesting walkthrough of tweaking how b-tree search works and turning that into a usable patch. (15 minutes.)

A Foreign Key Pathology to Avoid — If you have a many-to-many join table with defined foreign key relationships, a large number of inserts can have unexpected effects when locking parent records.

A Look into WAL Compression and Recent Improvements in Postgres 15 — Taking another look at the built-in WAL compression feature because Postgres 15 has even more to offer than in previous versions.

PostgreSQL 16's random_normal() Function — This will give us a way to get normally-distributed random numbers, which could have key benefits in creating certain types of random/dummy data. Hubert gives us a tour.

Bun 1.1.10: Simple, Performant ORM for Go — Supporting Postgres, MySQL, SQL Server and SQLite, Bun is a modern SQL-first database client library for Go developers with built-in migration and fixtures support. GitHub repo.

Building a Postgres Wire Protocol Server — A Java-oriented tutorial that has two purposes, both to show to implement Postgres’s wire protocol and to show off a variety of new Java 17 features.

usql: A 'Universal psql'? — A comparison of the Go-powered usql ‘universal CLI’ for various database systems (including Postgres) and Postgres’s own psql client. Syntax highlighting is.. a highlight, says Pavlo, but being able to query across multiple data sources is useful, and usql supports many of the same backslash commands too.