Articles on Postgres

Top deep-dives on Postgres

Improve Performance Using SQL Functions in Postgres

SQL is a powerful language that can go much beyond simple querying. Although personally I like to keep anything too complex out of the DB, there are some functions that it does very well (like aggregates and filtering). Jason Porritt has written an extensive article with plenty of examples on CTE functions in Postgres and how they can help optimize performance over views.

Hot Swapping Production Tables for Safe Database Backfills

Database management is one of the most frustrating things to do on the back end. Mostly because any screw up or mistake might have serious consequences if you're working with production data. In this informative article, Justin Lee describes some excellent techniques for managing a Postgres database between migrations, including using shadow tables, copy functions, and triggers. Although it's very SQL specific, the general ideas for keeping the data in sync are priceless.

How Postgres Stores Rows

Ketan Singh explains how rows are organized in files and what exactly is in a page.

Comparing Load Balancers for PostgreSQL

A comparison of different PostgreSQL load balancers with pros and cons.

UK COVID-19 dashboard built using Postgres and Citus for millions of users

Claire Giordano covers the work and tuning required to process over 800M rows of data on a daily basis.

The mirage of memory

Frits Hoogland's series of articles looks at memory allocation in Linux and how it impacts Postgres performance.

Tricking Postgres into using an insane – but 200x faster – query plan

Jacob Martin demonstrates how you can analyze a postgres query plan to optimize parts of it. Is this recommended in most cases? Definitely not.

Lesser Known PostgreSQL Features

I know it has a click-baity title, but I promise it's not another crappy listicle (NACL). As someone who has been using Postgresql daily for the past year, while also aggressively searching for new features that might make my life easier, even I picked up a couple of things from this article. Haki Benita covers (and includes examples for) getting the number of updated/inserted rows in an upsert, granting permissions on specific columns, matching against multiple patterns, and many more features.

The Surprising Impact of Medium-Size Texts on PostgreSQL Performance

When you're talking about Postgres databases, TOAST is an acronym for the system that handles when "large field values are compressed and/or broken up into multiple physical rows", so that they can be stored across Postgres' 8 kB pages. In this in depth article, Haki Benita first explains how TOAST works, then demonstrates it in action, and finally ties it back to the titular problem, with possible solutions thrown in at the end.

What is the new LZ4 TOAST compression in PostgreSQL 14, and how fast is it?

I wish I had read this earlier last week, before I spent the whole week optimizing PostGIS queries. Would it have solved all of my issues? Not really, but it would've given me a better idea of what was going on. In this article, Haiying Tang discusses the new compression algos available in Postgresql, explains their potential impact, and demonstrates the performance results from testing.

Postgres Query Optimization: LEFT JOIN vs UNION ALL

David Christensen demonstrates neat optimization of a postgres query by replacing LEFT JOIN with UNION ALL.

PostgreSQL 14 Internals

Egor Rogov has written a book on the internals of Postgresql. The link is to the website where all parts will be published, but currently only part 1 is available and it focuses on data consistency and isolation.

Speeding up sort performance in Postgres 15

David Rowley explains the optimizations made in postgres 15 to improve sorting speed.

Horizontally Scaling PostgreSQL

The author of this article covers the different approaches to using postgres as a distributed database.
Some highlights:

• Horizontal scaling is the art and science of distributing data across multiple servers without compromising data integrity, transaction safety and query performance
• Physical replication aims to replicate the “physical”, on-disk layout of the primary server’s data files by relaying WAL records that essentially encode the deltas of the file contents
• Logical replication works by recording and replaying the SQL-level transactions, rather than copying the final data directly

Transaction anomalies with SELECT FOR UPDATE

Laurenz Albe explains "how adding FOR UPDATE to a query can introduce transaction anomalies".

Postgres HA: roles are dynamic

If you're managing a system that depends on a database, at some point you're going to want to think about the what if case for when your database crashes. Dimitri Fontaine's article explores dynamic roles for when you have a number of Postgres nodes that are meant to act as primary or failovers.