Latest Blog Posts

Greetings!

Fault tolerance is a very important aspect of every non-startup application.
It can be described as a definition:

Fault tolerance is the ability of a system to maintain proper operation despite failures or faults in one or more of its components.

But this gives only slight overview - fault tolerance concerns many areas especially when we are talking about software engineering:

• Network failures (i.e. connection halt due to power outage on intermediate router)
• Dependent service unavailability (i.e. another microservice)
• Hardware bugs (i.e. Pentium FDIV bug)
• Storage layer corruptions (i.e. bit rot)

As a database developer I'm interested in latter - in the end all data stored in disks.

But you should know that not only disks can lead to such faults. There are other pieces that can misbehave or it's developer's mistake that he/she didn't work with these parts correctly.

I'm going to explain how this 'file write stack' (named in opposite to 'network stack') works.
Of course, main concern will be about fault tolerance.

Application

Everything starts in application's code. Usually, there is separate interface to work with files.

Each PL (programming language) has own interface. Some examples:

• fwrite - C
• std::fstream.write - C++
• FileStream.Write - C#
• FileOutputStream.Write - Java
• open().write - Python
• os.WriteFile - go

These all means given by PL itself: read, write, etc...
Their main advantage - platform independence: runtime (i.e. C#) or compiler (i.e. C) implements this.
But this have drawbacks. Buffering in this case - all calls to this virtual "write" function store going to be written data in special buffer to later write it all at once (make syscall to write).

Due to documentation, each of all PL above support buffering:

• C - setvbuf
• C++ - filebuf
• C# - BufferedFileStrategy
• Java - Files.newBufferedReader
• Python - io.BufferedIOBase
• go

I have just completed the first draft of the Postgres 18 release notes. It includes a little developer community feedback but still needs more XML markup and links.

The release note feature count is 206. There is a strong list of optimizer, monitoring, and constraint improvements. Postgres 18 Beta 1 should be released soon. The final Postgres 18 release is planned for September/October of this year.

Slides and transcript from my talk, "Anatomy of a Database Operation", at DjangoCon Europe in Dublin on 25 April 2025.

I'll share the recording as soon as it's available.

Introduction

This talk seemed like a great idea back in December 2024 when I asked the Django community for suggestions of database topics people wanted to hear about:

I'd love to be a DjangoCon Europe speaker again, because it's one of my favourite events. To that end, I'm looking for help to put together an irresistible talk submission! If you have suggestions for database/Postgres related topics that you'd really like to hear about, or burning database questions that you want to know the answer to, please let me know. I'm counting on you!

One of the suggestions was:

"how databases work and store data under the hood [so I can] build a complete mental model of what happens once you call SELECT … or INSERT."

Great idea, I thought. And then I realised just how much there was behind that one simple request!

So here goes...

I even learnt Django for the purpooses of this talk!

For demonstration purposes, I created this highly sophisticated web site that displays beer information that’s stored in a Postgres database. You can list beers, types of beer and breweries, and you can search for your favourite beer or add a new one.

But what’s happening behind the scenes when you do those things?

• How is the request passed to the database?
• How are the results retrieved and returned to you?
• How’s the data stored, retrieved and modified?
• And why should you even care?

One reason you might care is that users probably aren't happy when they see this ("LOADING, please wait...").

Or this ("DATABASE ERROR!").

This is me!
As you can see, from the diagram representing my career so far, (and as you already know if you've read my posts or watched my talks before), I have a database background.

I was a DBA for 20 years before I moved into database consultan

 I am (slowly) adding handy PostgreSQL queries to my GitHub, and Vacuum is the newest category.  The end goal is to have a compilation of queries for those of us who need to keep an instance healthy.

Over the years, I have collected hundreds of various queries and hate hunting them down in my code snippet library. Finally, they will be in one place and easy to search. 

Please contribute if you have similar or better queries (hint, hint!).

Introduction

Recently, we had a long discussion in our internal chat about the concept of Trusted Extensions in PostgreSQL. It became clear that while the feature is very useful, it’s often misunderstood — especially by beginners. Let's fix that!

This post explains what trusted extensions are, why they exist, how they work, and provides some important hints and warnings for everyday use.

What Are PostgreSQL Extensions?

An extension is a package of SQL scripts, types, functions, and sometimes even compiled C code that extends PostgreSQL's capabilities.
Extensions are installed on the server and then enabled inside a database using:

CREATE EXTENSION my_extension;

Normally, installing or enabling an extension requires superuser privileges, because extensions can modify how the database server behaves.

What Does "Trusted" Mean?

Trusted extensions allow non-superusers (regular database users) to enable certain extensions themselves using CREATE EXTENSION, without needing superuser rights.

In simple words:

• If an extension is trusted, a user with CREATE privilege on a database can activate it.
• If an extension is not trusted, only a superuser can activate it.

Important:
"Trusted" does not mean:

• The extension is bug-free.
• The code has been officially audited by PostgreSQL core developers.
• It is completely safe in every possible scenario.

It simply means:

"We believe that enabling this extension should not allow users to bypass security or harm the database server."

How Does PostgreSQL Know if an Extension is Trusted?

Each extension has a control file (.control) where its properties are described.
Inside that file, the line:

trusted = true

tells PostgreSQL that the extension can be enabled by non-superusers.

If the line is missing, the extension is considered untrusted by default.

Example of a simple control file:

comment = 'Extension for text search'
default_version = '1.0'
relocatable = false
superuser = fal

In the previous blog post we have seen how plenty of ram doesn’t necessary results in a faster instance.

This time we’ll quickly see how PostgreSQL and Linux relate to each other in particular if we want to run our RDBMS at scale.

Then we’ll have a look to the virtual memory in Linux and how to make it more friendly to PostgreSQL.

Let’s dig in.

We did it!

Thank you so much to everyone who made this event a success! Starting with the great talks selection (thank you, CfP committee!), to our amazing sponsors (thank you, Pat Wright!), to volunteers, to attendees!

My highlights were having multiple new speakers, new topics, and seeing a lot of new attendees for many of whom this was their first Postgres conference! I hope you all enjoyed it, and I hope to see you again!

And please don’t forget to leave us feedback here!

© Laurenz Albe 2025

PostgreSQL is famous for its stability, and rightly so. But that does not mean that it can never crash. And while PostgreSQL usually cleans up after itself, it has no good way to do so after a crash (after all, it has lost its memory). As a consequence, you can end up with orphaned files in your data directory. If these files are small, they probably won't worry you. But sometimes you can end up with a lot of junk in your data directory. It is notoriously difficult to deal with that problem, so I decided to write about it.

Why would PostgreSQL crash?

In case the title of this article gives you bad feelings about PostgreSQL, I'll try to dispel them. PostgreSQL is not in the habit of crashing. But sometimes a crash is not PostgreSQL's fault. Let me enumerate a few possible causes for a crash:

• you didn't disable memory overcommit, ran out of memory, and the Linux kernel's out-of-memory killer sent a SIGKILL to one of the PostgreSQL processes
• The file system containing pg_wal ran full, and PostgreSQL couldn't create a new WAL segment
• a PostgreSQL process encounters an error that it cannot cope with during a “critical section” (where it modifies data and has not yet reached consistency) — PostgreSQL escalates such errors to PANIC
• faulty memory, disk or other hardware component led to data corruption
• the administrator inadvertently corrupted the database
• a PostgreSQL software bug that causes a crash or data corruption (are you running an unsupported release or didn't update the the latest minor release?)

In fact, the most frequent cause of a crash that leaves large orphaned files in its wake is people running VACUUM (FULL). This command creates a copy of the table and all its indexes. If you run out of disk space and PostgreSQL cannot create a new WAL segment, the database server will crash. Such a crash will leave the partly created copy of the table behind. To avoid that kind of problem, I recommend that you keep pg_wal and the data directory o

Back in 2022, we covered how to log database query generation information from a web app using pg_stat_statements for Postgres. https://andyatkinson.com/blog/2022/10/07/pgsqlphriday-2-truths-lie

The application context annotations can look like this. They’ve been re-formatted for printing:

application=Rideshare
controller=trip_requests
source_location=app/services/trip_creator.rb:26:in `best_available_driver'
action=create

I use pg_stat_statements to identify costly queries generated in the web application, often ORM queries (the ORM is Active Record in Ruby on Rails), with the goal of working on efficiency and performance improvements.

The annotations above are included in the query field and formatted as SQL-compatible comments.

Application context usually includes the app name and app concepts like MVC controller names, action names, or even more precise info which we’ll cover next.

How can we make these even more useful?

What’s the mechanism to generate these annotations?

For Ruby on Rails, we’ve used the Marginalia Ruby gem to create these annotations.

Besides the context above, a super useful option is the :line option which captures the source code file and line number.

Given how dynamic Ruby code can be, including changes that can happen at runtime, the :line level logging takes these annotations from “nice to have” to “critical” to find opportunities for improvements.

What’s more, is that besides Marginalia, we now have a second option that’s built-in to Ruby on Rails.

What’s been added since then?

In Rails 7.1, Ruby on Rails gained similar functionality to Marginalia directly in the framework.

While nice to have directly in the framework, the initial version didn’t have the source code line-level capability.

That changed in the last year! Starting from PR 50969 to Rails linked below, for Rails 7.2.0 and 8.0.2, the source_location option was added to Active Record Query Logs, equivalent to the :line option in Marginalia.

PR: Support :sou

We continue to follow the news about PostgreSQL 18. The January CommitFest brings in some notable improvements to monitoring, as well as other new features.

You can find previous reviews of PostgreSQL 18 CommitFests here: 2024-07, 2024-09, 2024-11.

• EXPLAIN (analyze): buffers on by default
• pg_stat_io: input/output statistics in bytes instead of pages
• pg_stat_io: WAL statistics
• pg_stat_get_backend_io: I/O statistics for a specific process
• VACUUM(verbose): visibility map information
• Total vacuum and analysis time per table
• autovacuum: change the number of workers without restarting the server
• psql: connection service information
• psql: expanded display for \d* commands
• psql: leakproof flag in \df* output
• jsonb: null conversion to other types
• MD5 encryption algorithm: end-of-support preparations
• New function uuidv7
• postgres_fdw: SCRAM authentication without storing the password
• passwordcheck: minimum password length
• New function casefold and pg_unicode_fast collation
• DML commands: RETURNING with OLD and NEW
• to_number: convert a string of roman numberals to numeric

...

PostgreSQL databases tend to grow silently. One day, you notice queries slowing down, backups taking forever, and no one remembering what is in that 500 GB log table. If this sounds familiar, you’re not alone.

Partitioning makes things manageable

If your database has one massive table that keeps growing, partitioning can be a lifesaver. PostgreSQL has supported native declarative partitioning since version 10, and recent versions (13 and up) have made it much easier to work with.

• You don’t need any extensions — you can define a parent table and split it by range, list, or hash. For example, for time-based data:

What makes partitioning useful is how it improves query planning and makes deleting old data trivial (dropping a partition is faster and cleaner than a DELETE).

To migrate an existing table, you can:

• Create a partitioned version of the table

• Move data in batches using scripts

• Add triggers to route new inserts into the partitioned version

• Swap the old and new tables once the migration is complete

Note: Define indexes on the partitioned table itself — PostgreSQL will automatically create corresponding indexes on all partitions, including ones added in the future. You can’t use concurrently, though.

Compression: saving space on cold data

PostgreSQL uses a mechanism called TOAST (The Oversized-Attribute Storage Technique) to handle large field values such as text, jsonb, and bytea. By default, it compresses these using the pglz algorithm.
Since PostgreSQL 14, you can explicitly choose a compression algorithm per column — if your build includes LZ4 support (–with-lz4), you can use the faster and often more efficient lz4 algorithm:

Bonus: compressing backups

Last week I visited the Malmö PUG to talk about performance cliffs. It’s a really nice meetup - cozy environment, curious audience asking insightful questions. I highly recommend attending or even giving a talk there.

After the meetup I realized it’s been a while since I posted about some patch idea, and the performance cliff talk has a couple good candidates. Some might be a bit too hard for the first patch, for example improving the JIT costing. But improving the very first example about queries with IN clauses seems feasible. It’s quite well defined and isolated.

A very minimal edition this week, due to lack of time, and it's been relatively quiet anyway.

more...

Postgres is a powerful and feature-rich database, but like any system, it has certain limits that are good to be aware of. In this post, we'll take a look at a few interesting limits in Postgres.

63 Character Limit on Identifiers

When I have set returning functions and even non-set returning functions, I love putting them in the FROM clause instead of the SELECT part. I often take it for granted that the results are usually the same, but in some important situations, they are different. It's not guaranteed to be the same when your function is not immutable.

Check TRAKTOR 2 - less complicated, more stable, cloud ready! Compatible with PostgreSQL 16 and 17.

Stacey Haysler created a document which lists all the available volunteer options for the PostgreSQL project.

Luigi Nardi organized the Malmö PostgreSQL Meetup. Tomas Vondra presented.

April 23 the Postgres Extensions Ecosystem Mini-Summit #4 hosted a panel of users - Celeste Horgan, Sonia Valeja, Alexey Palazhchenko - to gain their POV (recording).

At the April 22 Barcelona PostgreSQL User Group meetup run by Andrea Cucciniello, Dave Pitts, and Nacho Alonso Portillo, Gabriele Bartolini presented "CloudNativePG: The Past, The Present, The Future".

On April 24th Gerard Zuidweg hosted the PostgreSQL User Group NL Spring 2025 Meetup. The following talks were held: Frank Sidi about “Analytics for PostgreSQL: From OLTP to Lakehouse to Petabyte-Scale AI” Hans-Jürgen Schönig about “Running PostgreSQL in Kubernetes made easy” Lightning Talk held by Ellert van Koperen about “Defaults and logical replication do not get along”

On April 23rd Karren Jex gave a lightning talk at DjangoCon Europe about the Diversity Task Force and encouraging folks to get involved in the PostgreSQL community and come along to PostgreSQL events.

Tom Kincaid organized the Boston PostgreSQL Meetup, Adam Wright spoke at the event.

Alastair Turner and Chris Ellis organized the PostgreSQL London Meetup. Tushar Ghotikar, Mauro Parada Crespo and Alastair Turner presented at the event.

Tobias Bussman, Andreas Geppert and Daniel Westermann have been part of the organization team for Swiss Database Synergy Day 2025. [Dirk Krautschick] (https://www.linkedin.com/in/dirk-krautschick), Laurenz Albe, Stephan Keller, Maurizio De Giorgi and Daniel Westermann presented talks.

Working on TRAKTOR again. The good news is, it still works with PostgreSQL 17.x and I have completely replaced psycopg2 with pg8000 to remove all native dependencies. The bad news is, if you don't want to run the arbiter as superuser it's a complicated dance of permissions, some of which can only be GRANTed by a superuser, and I have to find a way to automate this as far as possible.

Today, I would like to discuss additional techniques to speed up query execution. Specifically, I will focus on rearranging conditions in filter expressions, JOINs, HAVING clauses, and similar constructs. The main idea is that if you encounter a negative result in one condition within a series of expressions connected by the AND operator, or a positive result in one of the conditions linked by the OR operator, you can avoid evaluating the remaining conditions. This can save computing resources. Below, I will explain how much execution efforts this approach saves and how to implement it effectively.

Occasionally, you may come across queries featuring complex filters similar to the following:

SELECT * FROM table
WHERE
  date > min_date AND
  date < now() - interval '1 day' AND
  value IN Subplan AND
  id = 42';

And in practice, it happens that a simple rearrangement of the order of conditions in such an expression allows for speeding up (sometimes quite notably) the query execution time. Why? Each individual operation costs little. However, if it is performed repeatedly on each of millions of the table's rows, then the price of the operation becomes palpable. Especially if other problems, like the table blocks getting into shared buffers, are successfully solved.

This effect is particularly evident on wide tables that contain many variable-length columns. For instance, I often encounter slow IndexScans that become slow when the field used for additional filtering is located somewhere around the 20th (!) position in the table, containing many variable-width columns. Accessing this field requires calculating its offset from the beginning of the row, which takes up processor time and slows down the execution.

The PostgreSQL community has already addressed this issue, as observed in the code. In 2002, commit 3779f7f, which was added by T. Lane, reorganised the clauses by positioning all clauses containing subplans at the end of the clause list (see order_qual_clauses). This change was logical because

When disaster strikes, whether a natural disaster or a technical event, its impact on your network, database, and end-users can cause both costly downtime and data corruption. Data corruption, whether sparked by hardware failures like dying disks or faulty RAM, software glitches such as operating system bugs, or human missteps like accidental overwrites, is a terrifying prospect for any administrator. Yet, it’s not a death sentence.Your PostgreSQL database is typically a dependable cornerstone of your operations. Still, when disaster strikes, it can swiftly morph into an inaccessible liability, bringing applications to a grinding halt and leaving critical data at risk. PostgreSQL 17 arms you with an enhanced arsenal to tackle this challenge head-on, offering built-in tools like for pinpointing corruption, improved failover slot synchronization to keep replication intact during recovery, and finer-grained Write-Ahead Logging (WAL) control for precise restoration. In this blog, we'll dive deep into the disaster management process, equipping you with real-world commands and expected outputs to diagnose corruption accurately and recover effectively, whether you’re restoring from a robust backup or salvaging scraps from a crippled cluster with no safety net. With the right approach, you can turn panic into a plan and restore order to your database.

Step 1: Detecting Corruption in PostgreSQL

© Laurenz Albe 2025

Saving storage space should not be your first objective in a PostgreSQL database. Very often, the desire to conserve space aggressively comes at the expense of performance. But there is also no reason to needlessly waste space. Therefore, it is a good idea to be familiar with the concepts of data type alignment and padding bytes in PostgreSQL.

What is data type alignment?

When the CPU reads or writes a value from memory, the performance is best if the address of the value is a multiple of the size of the value. For example, a 4-byte integer should start at an address that is a multiple of four. PostgreSQL tries to optimize for performance. Therefore, it makes sure that all values are correctly aligned in memory. Note that alignment is only relevant for data types with a fixed length: PostgreSQL stores variable length data types like text, varchar and numeric without respecting the type alignment.

Whenever PostgreSQL persists data on disk, it organizes these data in pages (also known as buffers or, when on disk, as blocks) of 8kB. To keep things simple and efficient, the layout of a block on disk is exactly the same as the page in memory. As a consequence, PostgreSQL respects the type alignment on disk as well.

PostgreSQL data types can have an alignment of 1, 2, 4 or 8 bytes. You can see the alignment of a data type in the typalign column of the system catalog pg_type:

SELECT typalign,
       string_agg(typname, ', ' ORDER BY length(typname)) AS types
FROM pg_type
WHERE typtype = 'b'            -- base type
  AND typelem = 0              -- no array
  AND typlen <> -1             -- fixed length
  AND typnamespace = 'pg_catalog'::regnamespace  -- system type
  AND typname NOT LIKE 'reg%'  -- no object identifier type
GROUP BY typalign;

 typalign │                                               types
══════════╪════════════════════════════════════════════════════════════════════════════════════════════════════
 c        │ bool, char, uuid
 d        │ xid8, time, int8,

With all five CommitFests over, we're now into the testing and bugfix phase, and no new features are likely to be added. Having said that, there have been a few tweaks to SQL function performance and handling this week.

more...