Latest Blog Posts

My recommended methodology for performance improvement of PostgreSQL starts with query optimization. The second step is architectural improvements, part of which is the partitioning of large tables.

Partitioning in PostgreSQL is one of those advanced features that can be a powerful performance booster. If your PostgreSQL tables are becoming very large and sluggish, partitioning might be the cure.

The Big Table Problem

Large tables tend to grow uncontrollably, especially in OLTP or time-series workloads. As millions or billions of rows accumulate, you begin to notice:

Slow queries due to full table scans or massive indexes.

Heavy I/O usage, especially when indexes cannot fit in memory.

Bloated memory during operations like sorting or joining.

Increased maintenance cost, with longer VACUUM, ANALYZE, and REINDEX times.

Hard-to-manage retention policies, as purging old rows becomes expensive.

These problems are amplified in cloud-hosted databases, where every IOPS, GB, or CPU upgrade increases cost.

The post Improving PostgreSQL Performance with Partitioning appeared first on Stormatics.

Why Bridge Java with C in the First Place?

Bridging Java and C combines the strengths of both languages. A C application may rely on Java for modern libraries, cloud APIs, or UI and web capabilities, while a Java app might need C for low-level system access or performance-critical tasks. Sometimes, there’s simply no alternative—certain features only exist in one language. While modern languages like C++ and Go offer both high- and low-level control, many systems aren’t written in them. For existing C or Java codebases, bridging is often the most practical way to extend functionality without a full rewrite.

In my case, the goal was to build a C-based PostgreSQL extension called SynchDB that integrates with the Java-based Debezium Embedded library to enable heterogeneous database replication into PostgreSQL. Debezium already provides mature connectors for databases like MySQL, SQL Server, and Oracle, so rather than reinventing the wheel in C, I chose to bridge the two runtimes using JNI. This approach allows PostgreSQL to consume change data from other systems in real time. However, maintaining both C and Java components within the PostgreSQL extension framework introduces unique challenges—such as cross-language memory management, threading compatibility, signal handling, and debugging across runtime boundaries. Let’s explore some of those next.

SynchDB – A Heterogeneous Database Replication Tool for PostgreSQL

This project serves as a practical case study for the complexities of bridging Java and C, highlighting the technical challenges and design decisions involved in maintaining two runtimes under PostgreSQL’s extension framework. The basic architecture diagram is shown as below where the yellow box represents the Java space (also known as a Java Virtual Machine (JVM)), the blue box represents PostgreSQL extension (C space) and the orange box represents the PostgreSQL core.

The working principles of SynchDB extension starts by instantiating a JVM and running a p

Before we delve into the main subject of this blog, it is essential to understand the benefits of PostgreSQL replication, and the difference between single-master replication (SMR) and multi-master replication (MMR). In every modern business application, the database is becoming a critical part of the architecture and the demand for making the database performant and highly available is growing tremendously.

Planning Ahead for Better Performance

• Reliability means that the database is able to respond to user/application requests at all times with consistency and without any server interruption.

• High-availability is also a critical consideration that ensures that database operations are not interrupted and the database downtime is minimized.

Howto guide for upgrading PostgreSQL from version 16 to 17 on Ubuntu, after its upgrade from version 24.10 to 25.04 (Plucky Puffin).

In the PostgreSQL world, as well as in order database systems, data types play a crucial role in ensuring optimal performance, efficiency, as well as semantics. Moreover, some data types are inherently easier and faster to index than others. Many people are not aware of the fact that indexes indeed make a difference, so let us take a look and see how long it takes to index the very same data using different
types.

Creating some sample data

To show which differences a data type makes, we first have to create some sample table. In this case, it contains 5 data types that we want to inspect to understand how they behave:

blog=# CREATE TABLE t_demo (
        v1      int, 
        v2      int8, 
        v3      float, 
        v4      numeric, 
        v5      text
);
CREATE TABLE

Finally, we use our good old friend (the generate_series function), which has served me very well over the years:

blog=# INSERT INTO t_demo
        SELECT x, x, x, x, x FROM (
                SELECT  (random()*10000000)::int4 AS x 
                FROM    generate_series(1, 50000000)
        ) AS y;
INSERT 0 50000000

What this does is generate 50 million random rows and put them into my table. Note that all column entries are identical to ensure fairness for our index creation.

The data might look as follows:

blog=# SELECT * FROM t_demo LIMIT 10;
   v1    |   v2    |   v3    |   v4    |   v5    
---------+---------+---------+---------+---------
 9443332 | 9443332 | 9443332 | 9443332 | 9443332
 2220480 | 2220480 | 2220480 | 2220480 | 2220480
 1328189 | 1328189 | 1328189 | 1328189 | 1328189
 4506728 | 4506728 | 4506728 | 4506728 | 4506728
 8148249 | 8148249 | 8148249 | 8148249 | 8148249
   74086 |   74086 |   74086 |   74086 | 74086
 4160715 | 4160715 | 4160715 | 4160715 | 4160715
 9193039 | 9193039 | 9193039 | 9193039 | 9193039
 4062983 | 4062983 | 4062983 | 4062983 | 4062983
 7609357 | 7609357 | 7609357 | 7609357 | 7609357
(10 rows)

Note that PostgreSQL does not necessarily write data to disk immediat

The PostgreSQL release is slowly taking shape, with the first draft of release notes now available. Corrections and additions can be sent via the pgsql-hackers thread " PG 18 release notes draft committed ".

more...

You know I had to do it as soon as I found it was possible. Yes, I installed and enabled AI in the DBeaver Query Editor so I can use AI with my PostgreSQL database work. Let’s face it. It was inevitable. However, the setup isn’t intuitive. Setting Up in DBeaver I’m going to assume […]

The post AI in the DBeaver Query Editor appeared first on Grant Fritchey.

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

...