Postgres 18 on SnoutData Cloud: what changed, and the three things that would have bitten us

What you get on 18

Every example here was run on our 18 image, exactly as printed.

uuidv7()

A UUID that sorts by the time it was made. As a primary key it keeps inserts at the end of the index instead of scattering them across it, and it still cannot be guessed.

create table orders (
  id uuid primary key default uuidv7(),
  placed_at timestamptz not null default now()
);

Virtual generated columns, now the default kind

A generated column can be computed when it is read instead of stored on disk. On 18 that is what you get when you name neither kind, so a stored column has to say stored. More on why that matters below.

create table line_items (
  id int primary key,
  qty int not null,
  unit_cents int not null,
  total_cents int generated always as (qty * unit_cents) virtual,
  total_stored int generated always as (qty * unit_cents) stored
);

OLD and NEW in RETURNING

An update, delete or merge can return the row as it was and as it is, in one statement. That is a before-and-after diff without a trigger.

insert into line_items (id, qty, unit_cents) values (1, 2, 500);

update line_items set qty = 10 where id = 1
returning old.total_cents, new.total_cents;
--  total_cents | total_cents
-- -------------+-------------
--         1000 |        5000

Temporal primary keys

A key can say that two rows may share a value as long as their time ranges do not overlap. A room can be booked many times, never twice at once. It needs the btree_gist extension for the int column, and a project's owner can create that without asking us.

create extension if not exists btree_gist;

create table bookings (
  room int,
  during tstzrange,
  primary key (room, during without overlaps)
);

insert into bookings values (1, '[2026-10-05 09:00, 2026-10-05 10:00)');
insert into bookings values (1, '[2026-10-05 09:30, 2026-10-05 11:00)');
-- ERROR:  conflicting key value violates exclusion constraint "bookings_pkey"

What 18 does without being asked

• Data checksums are on. Every page is checksummed when it is written and checked when it is read, so corruption is reported as an error instead of being returned as data. On a platform where a host is only a cache of what is in object storage, we want to know.
• Faster cold reads, but not where we expected. Postgres 18 can keep several reads in flight instead of waiting for each one, and we run it that way (io_method=worker). We measured it ourselves, on a 2-vCPU Graviton host with a gp3 volume, shaped like our fleet's, with a Plus project's limits and a cold cache. A bitmap heap scan touching 10% of a 2.6 GB table took 2.4 seconds on 18 against 10.3 on 17. But switching asynchronous I/O off did not slow it down: the gain comes from 18 reading neighbouring pages in larger requests. A full sequential scan took 21 seconds on both, because that is as fast as the disk goes, and a vacuum took 18% to 45% longer on 18, a cost consistent with its data checksums. The method, every run and the plans are in the research note.
• Protocol 3.2. A client that asks for it (libpq 18's max_protocol_version=3.2) gets longer cancel keys. More on that below too.

The extensions came along

SnoutTime 0.1.6, our time-series extension, pgvector 0.8.6 and PostGIS are built for 18 and run in the same image. The extensions page in the docs lists the rest.

How we moved without touching anyone's database

The usual way to move a platform to a new major is to upgrade everyone. We did not, because we did not need to. Each project already records the major it was made with, and the host agent now runs each project on the image of its own version. A project made on 17 keeps getting the 17 image, a new project gets 18, and both run side by side on the same host. Nothing about an existing project changed: not its version, not its data, not its uptime.

That needed one guard. A Postgres data directory opens only on the major that made it: an 18 server pointed at a 17 directory refuses to start. Without a check, a project handed the wrong image would restart, fail, restart and fail again. So the agent reads the major in the image and the major in the data directory before it starts a pod, and if they differ it refuses with a sentence on the project's status instead. It asks before a recreate stops anything, too, so a wrong image leaves the old pod serving.

The same rule holds off the cloud. npx snoutdata init starts a new local project on 18, and a local project made earlier keeps the major its data was written with. The self-hosted stack defaults to 18 for a new install, and one set up on 17 says so in its .env. The image is public at ghcr.io/snoutdata/snoutpod-postgres:18, for amd64 and arm64, beside :17, which stays.

Before calling it live we ran the whole life of a project on 18 against the live system (create, pause, a cold wake from object storage, a password rotation, delete), 25 checks of 25; our QA round, 20 of 20; and an unmodified client application through auth, storage, realtime, functions and the data API, 29 of 29. The Studio build that ships next was driven against a live 18 project too: it reads the version, runs queries, completes table names, and a project created from its panel came up on 18.

Three things that would have bitten us

1. The data directory moved, and a recreate would have lost the database

Our pod image is built on the official Postgres image, and its 18 tag moved the default data directory from /var/lib/postgresql/data to /var/lib/postgresql/18/docker. Every volume we mount is at the old path. Built unchanged, a new 18 pod would have run initdb in a directory outside its volume: it would have worked, accepted writes, and passed every check we had, and then lost every row the first time it was recreated. Nothing would have failed loudly. Our image now sets the path explicitly, and we checked that a fresh 18 pod writes inside its volume.

2. Longer cancel keys broke the front door

When you press Ctrl-C in psql, the client opens a second connection and sends the cancel key the server gave it at startup. Our front door sits between every client and every project, wakes a sleeping one on connect, and rewrites cancel keys so a cancel reaches the right project. It assumed a key was 4 bytes, because for over twenty years it was. Protocol 3.2 lets a key be up to 256 bytes, and 18 sends 32. So a client that opted in to 3.2 was refused at our door before it reached its database. The proxy now accepts any 3.x startup, carries keys of 4 to 256 bytes, and issues its substitutes at the length the server used, from a proper random source. We checked it live: psql with max_protocol_version=3.2 through the front door, a pg_sleep, Ctrl-C, and the query was cancelled.

3. Generated columns quietly changed meaning

On 17 a generated column was always stored. On 18 a generated column with neither stored nor virtual is virtual: computed on every read, and not indexable. So DDL written for 17 that leaves the word out runs without an error on 18 and makes something different. If a migration from last year makes a generated column and then indexes it, it now fails on a new project. From the next SnoutData Studio release, Studio writes stored or virtual in every DDL statement it generates, and reads the kind back when it looks at a table. Its plan advisor will also know 18's skip scan and its rewrite of OR into = ANY, so it does not warn about queries 18 already plans well. If you have migrations of your own, search them for generated always as.

And two smaller ones

• The 18 base image also declares its parent directory as a volume, so every recreate of a pod left an anonymous volume behind: twenty of them on our build host after one round of tests. The agent now removes those with the container. The named volume that holds the data is never touched by it.
• Our image registry kept the last ten images of any tag. Ten builds of 18 would have expired the 17 image that every older project still runs on. The rule now expires only untagged images.

What is next (not shipped)

Two things 18 makes possible that we are working on. Neither is available today.

• Signing in to the database with your company's single sign-on. Postgres 18 can accept an OAuth token instead of a password. We are building the module that checks it, so a member of your team connects with the identity they already use for SnoutData. Today a database login is a role and a password.
• Managed major upgrades. 18 keeps planner statistics through pg_upgrade, and its --swap mode moves a data directory instead of copying it. Together they make an in-place upgrade to the next major a matter of minutes, without the slow queries that used to follow one.

If you have a project on 17, it keeps running on 17, supported like any other, and there is nothing you need to do. The Postgres 18 page in the docs has the details, including what to check in DDL written for 17.