Better SQL Functions in Postgres v14 — Postgres 14 introduced a new way to define SQL functions and it’s caught the eye of at least two developers this week as we have two good posts about it. In essence, “the body of SQL functions and procedures need no longer be a string constant” and this provides some advantages as we see in Laurenz’s demo.

Laurenz Albe

BEGIN ATOMIC: A Better Way to Create Functions in Postgres 14 — Yes, it’s the same story as above, but focused less around explanation and more around a larger code example. It’s well worth skimming both if this area interests you at all.

Jonathan Katz

Free eBook: Effective Indexing in Postgres — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance by 10x or even 100x.

pganalyze sponsor

Shipping Multi-Tenant SaaS using Row-Level Security — Explorations and thoughts on database-level (rather than app level) ‘secure by default’ security in single-schema multi-tenant databases. The extensive discussion about this topic on Hacker News proved to be quite valuable too.

Miki Pokryvailo (Nile)

Partitioning with Native Postgres and pg_partman — Sometimes premature optimization makes no sense, but if you know your database is likely to rapidly enter the world of terabytes (maybe you’re storing a lot of time series data, say) then being proactive about partitioning is a wise idea and this is a particularly gentle introduction.

Elizabeth Christensen

Using BUFFERS for Query Optimization — You know EXPLAIN ANALYZE but what about BUFFERS? It provides info about how Postgres’s buffers are used in queries and can help you narrow in on I/O related issues in particular.

Michael Christofides

13 Postgres-Related Tools That Aren't psql — Timescale continues to analyze the results of its recent State of PostgreSQL 2022 survey, this time looking at tools. We cover Postgres tools every week ourselves, but this post has the numbers for what people are actually using.

Ryan Booz (Timescale)

Migrating Away from Heroku: A Customer Study on Crunchy Bridge

Crunchy Bridge sponsor

AlloyDB Versus Postgres: A Performance Review — As is always the case, go into benchmarks with a cynical but open mind; benchmarking is hard! Nonetheless, it’s cool to see someone putting Google’s Aurora-a-like AlloyDB for PostgreSQL through its paces.

Michael Aboagye

pg_jsonschema: JSON Schema Validation Extension — Postgres’s support for JSON / JSONB columns is now pretty much second to none, but what if you want to validate the structure of your JSON data? JSON Schema provides a way to define JSON-based formats and this extension lets you verify JSON documents using such schemas. (A fun aside is just how simple the code is since it leans on a different Rust library for the hard work.)

Supabase