Postgres 19 is planning to change the default TOAST compression from pglz to LZ4, so let's look at how Postgres compresses data in table storage and indexes. Postgres uses a single, unified compression framework for table (heap), TOAST, and indexes. In heap and TOAST, compression is automatic and on by default for variable-length types like TEXT, VARCHAR, BYTEA, and JSONB. In indexes, it is opportunistic: compression fires only when an individual key exceeds the size threshold, not for every variable-length value stored in an index.

click to expand

History of Postgres Compression

Compression was first added in Postgres 7.0, released in 2000, but not in the form it takes today. At the time, Postgres had a strict 8kB maximum row size, and trying to insert more than 8kB would throw an error. Fixing this row size limit was a priority for the Postgres core team.

The first attempt to work around the limit was an explicitly compressed field. Postgres 7.0 shipped an lztext data type that used the pglz compression algorithm. This implementation had tradeoffs: the 8kB row limit still existed, and users had to explicitly choose a compressed data type.

After the 7.0 release, the next logical step might have been to add more compressed data types like LONG or BLOB (as many closed source databases were doing at the time). Instead, the Postgres core team rejected additional data types and rallied around TOAST. Discussions on the pgsql-hackers mailing list show the group split the 8kB problem into two distinct problems: a data type problem and a physical storage problem. Compression and TOAST were the answer to the physical storage problem.

TOAST with pglz

With Postgres 7.1, TOAST was implemented using pglz.

The pglz algorithm lives in the pg_lzcompress.c file. Because Postgres is open source, we can read the author's reasoning for home-rolling a compression algorithm right in the comments:

• Trade ratio for speed: pglz is fast to compress and decompress, and willingly gives up compress

Six months ago we started writing the PostgreSQL backup tool we kept wishing existed. Now pg_hardstorage has shipped, and you can read every line of it on GitHub before you trust it with your WAL stream.

Why another backup tool?

At CYBERTEC we have spent more than two decades helping organisations run PostgreSQL in production. The single most consistent thread through all of those engagements is the same one: backups are the load-bearing wall of a database. When they hold, you forget about them; when they don't, nothing else matters.

Which is why we believe the PostgreSQL ecosystem is healthier when there is more than one serious open-source backup option, and when none of those options depends on the goodwill of a single maintainer or a single company. Choice is a feature. A migration path between tools is a feature. Knowing you can switch without rewriting your recovery plan is a feature.

pg_hardstorage exists to add another credible option to that pool. It has been in real customer deployments for over a year, we wanted to be sure the wire format and the operational shape were right before asking anyone else to depend on it. Today it goes open-source under Apache 2.0, with no enterprise edition and no CLA.

The existing tools: pgBackRest, Barman, WAL-G are excellent pieces of engineering, and the work behind them is a large part of why the PostgreSQL backup story is as mature as it is today. We have enormous respect for the people who build and maintain them. pg_hardstorage is not trying to displace any of them; it is trying to give operators a credible alternative when their requirements pull in a different direction, and to give them a smooth way across if they ever need to take it.

That direction, for us, is the next generation of how PostgreSQL is actually deployed:

• Cloud-native by default. The data plane is the PostgreSQL replication protocol over a normal libpq connection — the same one a streaming replica uses. That single architectural choice is the entire reason pg_h

Slides and transcript from How much do you really need to know about databases? at EuroPython 2026 in Kraków, Poland on 15 July 2026.

I will add a link to the recording once it's available.

I've included just very brief alt-text for each slide image, but I've also added any links or other essential information into the body of the blog post.

This talk is aimed at application developers, whether or not you already have some database knowledge. And if that's not your profile, you're welcome in any case, because I'm always happy to talk about databases to anyone at all! So, let’s find out how much you really need to know about databases.

Just to reassure you (because you may start to wonder as the talk goes on) that I really do have a life outside databases: this is a photo of me in my happy place, splattered in mud, out on my mountain bike in the French Alps, where I live.

I’m part of the Postgres extensions team at Snowflake, I’m actively involved in PostgreSQL Europe, especially all things diversity and inclusion, and I write and present talks (about databases) at Postgres and Developer conferences.

When asked “What do you want to be when you grow up?” I doubt any kid ever said “a database administrator”.

"Shockingly", not a single person in the room raised their hand when I asked the audience Who wanted to be a DBA when they grew up.

I’ve spent my whole career working with databases, as illustrated by this diagram of my roles over the last 25+ years - Junior database administrator, senior database administrator, (so-called) database expert, senior database consultant…

My last 2 job titles - senior solutions architect and now senior software engineer are the only ones that don’t contain the word “database”, but I still work exclusively with them.

And even I didn’t want to be a DBA when I grew up!

But, since I’m a database person (did I mention that?) and I live and breathe data, I thought I’d better do some actual research. Maybe I was wrong, and kids do in

POSETTE: An Event for Postgres 2026 is an online event for PostgreSQL, brought to us by the Postgres team at Microsoft, which took place on June 16-18, 2026. I'll always have a soft spot for in-person conferences, but POSETTE is probably the best-run online event in our community, and I was delighted to be invited back to speak.

As it happens, I had already given this same talk in person earlier the same month, at PG DATA 2026 in Chicago on June 4-5. My sincere thanks go to the organisers of both events for having me. The recording of my talk is now up.

If you use PostgreSQL's LISTEN and NOTIFY for asynchronous inter-process communication, they may be hiding a serious performance bottleneck in a high-throughput database. I walk through a real production incident where NOTIFY quietly brought a busy database to a grinding halt: how the internal serialisation of notifications triggers AccessExclusive lock cascades on pg_database, and how to architect a fix using unlogged queue tables, transaction-level advisory locks (pg_try_advisory_xact_lock), and batching to move NOTIFY out of your transaction hot path.

🎞️ Video on YouTube: youtube.com/watch?v=2-5WYY2bFjs

📊 View the slides: LISTEN Carefully: How NOTIFY Can Trip Up Your Database (PDF)

Have you been bitten by NOTIFY in production? I'd like to hear about it, on Mastodon at @vyruss@fosstodon.org or on Bluesky at @vyruss.org.

As stated in Part 1, pgAgent is going away, so I'm focussing on setting up pg_timetable as similar to pgAgent that I can. For this second part, I'm going to go over how to configure pg_timetable as a service on Linux.

A heads up, i really loved the pgAgent UI in pgAdmin so I'm working on one for pg_timetable which is mostly patterned after the pgAgent one. You can see the pull request work in progress pg_timetable UI for pgAdmin. I'm in the middle of cleaning up some loose ends before it is ready for commit.

Back on Friday, April 3rd, I ran a live, hands-on LinkedIn Live session on fixing bad SQL in PostgreSQL. My apologies for the delay in sharing the recording: for various reasons I couldn't post it earlier, but here it is.

The session was based on Chapter 2 of my book, PostgreSQL Mistakes and How to Avoid Them (Manning), and it zeroes in on the common SQL anti-patterns that quietly lead to incorrect results and hidden performance issues.

I kept it hybrid: short, concise explanations paired with live terminal demos on real-world examples. For each mistake, I show you the problematic query in action, explain why it leads to incorrect results or slow execution, and then walk through a practical refactor that fixes it, often unlocking proper index usage and more efficient execution along the way.

It's a mix of everyday correctness pitfalls and the subtler performance traps, the kind that slip straight through code review and then quietly hurt you in production. If you write SQL or review database code, I hope you'll come away with concrete techniques you can apply immediately to write safer (and faster!) Postgres queries.

Without further ado, here's the recording:

Video on YouTube: youtube.com/watch?v=SxIgD1OfU_A

I'd love to hear what you think. You can always reach me at @vyruss@fosstodon.org with your questions, your own SQL horror stories, or the refactors that saved your bacon.

If you enjoyed this and want the whole collection of mistakes (and how to avoid them), the book is right here 👇

This post is about what PostgreSQL actually does when you write GRAPH_TABLE syntax. It turns out the database rewrites your graph query into ordinary joins against the underlying tables, then plans them with the regular optimizer. This has three practical consequences you'll notice right away.

First, performance is predictable. The query plan shape follows directly from your pattern shape, just like join-heavy SQL. Second, the indexes that matter are the obvious ones — the same you'd add for equivalent joins. Third, EXPLAINshows you the join tree directly. There's no graph-specific plan node hiding anything from you.

The setup below is self-contained. Run it once and you're ready to follow along with everything that comes next.

Setup

We'll create a synthetic graph with 10,000 vertices and about 60,000 random directed edges. It loads in a few seconds and uses the prefix big_ so it won't collide with anything else you might have.

Drop existing objects first if you've run this before:

DROP PROPERTY GRAPH IF EXISTS big_social;
DROP TABLE IF EXISTS big_knows, big_person CASCADE;

Now create everything:

CREATE TABLE big_person (id int PRIMARY KEY, name text);
CREATE TABLE big_knows  (a int NOT NULL, b int NOT NULL, PRIMARY KEY (a,b));

INSERT INTO big_person SELECT g, 'P'||g FROM generate_series(1, 10000) g;

INSERT INTO big_knows
SELECT DISTINCT a, b FROM (
    SELECT (random()*9999)::int + 1 AS a,
           (random()*9999)::int + 1 AS b
    FROM generate_series(1, 60000)
) s
WHERE a <> b ON CONFLICT DO NOTHING;

ALTER TABLE big_knows ADD FOREIGN KEY (a) REFERENCES big_person(id);
ALTER TABLE big_knows ADD FOREIGN KEY (b) REFERENCES big_person(id);

CREATE PROPERTY GRAPH big_social
    VERTEX TABLES (big_person KEY (id) LABEL person PROPERTIES (id, name))
    EDGE TABLES (
        big_knows
            SOURCE KEY (a) REFERENCES big_person (id)
            DESTINATION KEY (b) REFERENCES big_person (id)
            LABEL knows
    );

ANALYZE big_person;
ANALYZE big_knows;

The R

On 2 July, 2026, the PostgreSQL Istanbul Meetup met for the first time, organized by Devrim Gündüz, Gülçin Yıldırım Jelínek & Bilge Korkmaz Erdim.

Speakers:

• Viktoriia Hrechukha
• Pavlo Golub

On 2 July 2026, the PostgreSQL User Group Estonia met, organized by Ervin Weber

Speakers:

• Henrietta (Hettie) Dombrovskaya
• Martin Vool

On 2 July, the Program Committee of PGConf Brazil met to finalize the schedule:

• Rodrigo (Bill) Bernardi
• Taís Medeiros
• Marcelo Altmann
• Ronaldo Andrade Silva
• Rafael Thofehrn Castro

On 9 July, the Program Committee of PGDay Lowlands met to finalize the schedule:

• Teresa Lopes (Chair)
• Chelsea Dole
• Stefan Fercot
• Boriss Mejias
• Ellert van Koperen

On 9 July, the PostgreSQL Edinburgh Meetup July 2026 met, organized by Jimmy Angelakos

Speakers:

• Pat Wright
• Martins Otun

Multi-media contributions:

• Pavlo Golub posted a video about Swiss PGDay 2026

Actual sunshine ☀️ in Edinburgh 😲 and a room full of Postgres people catching up over pizza: July was good to us. 🐘

Thursday, July 9th brought us to Paterson's Land at the University of Edinburgh, and to a wonderfully diverse pair of talks: one on finding your voice in the community, and one on building auditable AI systems on Postgres. Thanks to everyone who came along, to our two excellent speakers, to my co-organisers Jim Gardner and Denys Rybalchenko, and to pgEdge for kindly sponsoring the pizza and refreshments.

For those who couldn't make it, or for those who want to revisit the details, here is a recap of the talks with slides.

The Talks

Speaking and Community Involvement for the Introvert

Pat Wright (Redgate)

Pat Wright, ready to talk about speaking

Pat, a PostgreSQL Advocate at Redgate, kicked off the evening by making the case that technical skills alone aren't enough: speaking and community involvement can set you apart in your career, even if you're an introvert. All it takes is passion for your topic and "20 seconds of courage".

Pat sharing his tips for conference abstract submissions

He walked us through the whole journey with refreshingly practical advice: writing an abstract that gets accepted, rehearsing to an empty room before presenting to others, testing demos relentlessly (and having a backup), and learning to "pause the introvert" on the day. His parting challenge for everyone: meet three new people at your next event.

📊 View the slides: Speaking and Community Involvement for the Introvert (PDF)

One Engine, One Audit Trail: Traceable, SQL-First Retrieval for Compliance-Critical Systems

Martins Otun (Algonix AI)

Martins Otun introducing SQL-first retrieval for compliance-critical AI

After the break, Martins, Founder & Principal AI Engineer at Algonix AI, showed us how to build AI retrieval systems for regulated industries using PostgreSQL and pgvector, where auditability and compliance aren't an afterthought but a core part

We at CYBERTEC usually spend a lot of time producing polished community documentaries, but sometimes you just want to push the raw data straight to the output. Welcome to the UNLOGGED experiment for Swiss PgDay 2026!

In Postgres, an UNLOGGED table skips the write-ahead log for pure speed and zero crash recovery. That’s exactly what this first video is: a fast, uncompressed cut to capture the immediate, boots-on-the-ground vibe from Swiss PgDay 2026 while the team compiles the official documentary.

I’m dropping this as a test to see if you enjoy this faster, unedited format. Check it out and let me know if you want to see more of these!

Watch the video on YouTube.

TL;DR - RegreSQL 1.0 tested that your queries return the right rows. 2.0 tests that they return them the right way, and it does the checking against production's real statistics instead of your empty dev database, which lies.

A migration cleanup dropped an index nobody thought was load-bearing. Every test passed: same rows, same order, green. Three days later the API started timing out on a query that hadn't changed a character, because the planner had quietly switched it from an index scan to a sequential scan over a table that had kept growing.

The first version of RegreSQL would have passed that change too. It tests what your queries return: run them, diff the rows against a committed expected file, go red when the output changes. That catches the query that now returns the wrong rows. It says nothing about the query that returns the right rows the wrong way, which is most of what takes a database down.

Version 2.0 tests that.

Test the plan, not just the rows

Here is that failure on a laptop. An orders table, an index on customer_id, and a query that reads one customer's orders:

-- orders-by-customer.sql
select id, total
  from orders
 where customer_id = :cid
order by id;

Baseline it and run the tests. Green:

$ regresql test
  ✓ 2 passing

Now drop the index the way that migration did, and run the same tests again:

$ regresql test
  ✓ 1 passing
  ✗ 1 failing

FAILING:
  orders-by-customer.1.buffers (3898 > 109 * 102%, +3476.1%)
  Expected buffers: 109
  Actual buffers:   3898 (+3476.1%)

  Cost (info):      7962.41 (baseline: 421.03)

  ⚠️ Table 'orders': Bitmap Heap Scan → Seq Scan

Here is the whole loop, start to failure, as it actually runs:

The output check still passes; the rows didn't change. The plan check catches what the output check can't: the same query, returning the same result, now runs a sequential scan instead of the bitmap index scan it used before, reading thirty-five times the buffers. That failure blocks the merge. And the diff names the table and

I've been going to conferences and meetups of all kinds since 2004. And today — much like in the era when a Nokia brick was giving people their first, still-primitive taste of mobility — these events follow the same format: you give a talk, you answer questions from the room, and the slides get posted somewhere. These days a video lands on YouTube too. Sometimes a chat survives the event, filled mostly with logistics. And that's about it.

I get it: the way humans interact and consume information doesn't really change, our analog bandwidth is limited, and the format — refined over centuries — is probably close to optimal. But why do we limit ourselves artificially and use almost none of what information technology has to offer?

Concretely, here's what I want to discuss.

Delays

For some mysterious reason, we still wait days, weeks, sometimes months for the slides and the video of a talk that matters to us. What stops organisers from publishing the slides before the talk starts, and the audio/video right after it ends? Editing isn't what makes a technical talk valuable — I can rewind to the right moment myself.

Access

Sure, people pay for tickets — but mostly for the chance to meet in person, argue, and soak up the atmosphere. The commercial upside of events like PGConf.dev or PGConf.EU is hardly the main point: they lean heavily on sponsors. And sponsors care about reach, not box office, don't they? So what stops us from streaming at least an audio feed from the nearest smartphone in real time and attaching the recording to the talk page the moment it ends? The community is international, and travel costs and visa formalities keep plenty of professionals away.

Interactivity

An on-site attendee can ask a question after the talk — or in the hallway track, over coffee, at an afterparty jogging. Spontaneous voice conversation is familiar, convenient, and important. But there are also people who don't think that fast on their feet, aren't that strong in face-to-face conversation, don't

 Ever have a query 'tossed over the fence' that you find incomprehensible but still have to support it? A few years ago, you would have needed to triage the query. Obfuscated queries can be tough to decipher.  Sometimes, the query is due to someone or an ORM being clever. Many times the query is touch to read because the 

DBeaver recently added its AI Chat to the free, open-source DBeaver Community Edition. And you will find it very at determining what a query does. Let's start with a simple query.

Open AI Assistant

Where to find the AI Assistant

Input Prompt

Prompt with a query

Explanation

The Query Explained

If you've heard one thing about pg_hardstorage, it's probably that "it works against managed PostgreSQL". This post is about the one architectural choice that makes that true, and the consequences that fall out of it.

The choice: data plane = libpq + replication protocol

pg_hardstorage's agent connects to PostgreSQL the same way a streaming replica does. A normal libpq connection, with the replication attribute set. The agent reads WAL using START_REPLICATION SLOT on a persistent physical slot, and pulls base backup data using BASE_BACKUP.

That's it. No archive_command. No archive_library. No SSH-into-the-host. No shared filesystem. The connection string is the entire interface.

pg_hardstorage agent libpq client (no host access) PostgreSQL replication endpoint [ CONNECTION ] libpq connect (replication=true, user=hs_backup) streaming-replication privilege ok [ BASE BACKUP ] BASE_BACKUP data dir streaming · stop_lsn = 0/3CFFEE40 [ WAL STREAMING (continuous) ] START_REPLICATION SLOT 'hs_prod' 0/3CFFEE40 WAL records streaming… flush LSN ACK · 0/3D000028 (every ~10s) … more WAL records … (loop forever, with periodic ACKs draining the slot.

The wire conversation between the agent and PostgreSQL, three phases over a single libpq connection. BASE_BACKUP for the data directory, START_REPLICATION
SLOT for WAL, and a periodic flush LSN ACK that drains the slot on the PG side.

Consequence 1: managed PostgreSQL works automatically

RDS, Aurora, Cloud SQL, Azure Database, Aiven, Supabase, Neon — they all expose a PostgreSQL replication endpoint. Most of them give you a SUPERUSER or rds_replication role on demand. The agent needs nothing else.

Compare to pgBackRest, which assumes:

• archive_command can be set on the source PG - managed: nope, you don't own postgresql.conf
• SSH access to the host - managed: nope
• An archive_library can be loaded - managed: nope

None of those work against managed PG. The data plane decision excludes most of the modern PostgreSQL deployment

A few months ago, I spent time with multiple teams inside the same large financial services organization.

I spoke with the data engineering team. The AI and model team. The platform team. The governance and compliance team.

Each conversation sounded right.

That was the problem.

Each team understood its domain. Each had a clear mandate. Each had a reasonable explanation for the choices it was making. Nothing sounded careless. Nothing sounded irresponsible.

But the real issue did not appear inside any one conversation. It became visible only when I looked across all four of them together.

The data engineering team had pulled customer data into a separate environment to support AI use cases. Faster to build. Safer to experiment. They did not want to touch core systems.

The AI team was building agents on top of that data — assuming it had already been governed, validated, and approved before it reached them.

The platform team was focused on infrastructure stability. AI-specific data governance was outside its current scope.

The compliance team was building a model governance framework. Access controls. Audit requirements. Approval workflows. Human review points.

Solid work. Built on one assumption nobody had verified: that the data underneath the models was already governed correctly.

Nobody had lied. Nobody had been careless. Each team was doing exactly what it believed it was supposed to do. But when I looked across all four conversations, the real risk became obvious.

The customer data grounding the AI agents existed in at least three separate copies across the organization. Each copy had been created independently, with slightly different field definitions, access controls, lineage, and freshness. None of them was the governed system of record.

The control said: “Only show this customer’s data to an authorized user.” The data layer replied: “Which copy? Governed by which access model? Created by which team? Refreshed when?”

That is where AI governanc

One of the most valuable things about partitioned tables is pruning - the database's ability to eliminate entire partitions based on a query predicate. Under conventional wisdom, pruning can only be achieved when querying by the partition key - this makes choosing the right key extremely difficult. However, if your data follows certain patterns, using some clever tricks you can achieve pruning even when filtering by non-partition key columns.

In this article, I demonstrate how to achieve partition pruning when filtering by non-partition key columns.

Table Partition

Imagine you run a popular website with many users. Your product team wants to gain some insight into how the system is used, so you start logging events. To give events context, you group them into sessions and keep the time, the type, and some data in a database table:

db=# CREATE TABLE event (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  timestamp  TIMESTAMPTZ NOT NULL,
  session_id BIGINT NOT NULL,
  type       TEXT NOT NULL,
  data       JSONB
) PARTITION BY RANGE (timestamp);

CREATE TABLE;

You have many users so you expect many events. Most queries use only a subset of the data, usually a specific date range, so you create a partition for each year based on the timestamp:

db=