Postgres 18 features

Every CapyDB cell runs Postgres 16, 17, or 18, chosen when you create the project. This guide covers the 18 features worth reaching for, and - more importantly - how to use them without pinning your schema to one major.

Check what a cell runs from the dashboard, or:

SHOW server_version;

Time-sortable UUIDs

A random UUID primary key scatters inserts across the whole index. A time-sortable one (UUIDv7) puts consecutive inserts on the same page, which means less index churn and a smaller working set - a real difference on a cell sized for a working set that fits in cache.

Postgres 18 has uuidv7() built in. Postgres 16 and 17 do not; they get the same thing from the pg_uuidv7 extension, which spells it uuid_generate_v7().

Use capydb.uuidv7() and neither of those names.

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT capydb.uuidv7(),
  customer_id uuid NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

capydb.uuidv7() resolves to the built-in on 18 and to an equivalent implementation on 16 and 17. It exists on every cell, needs no extension enabled, and produces identical RFC 9562 values on all three - including the same ordering behaviour for rows created inside the same millisecond.

capydb doctor flags raw uuidv7() / uuid_generate_v7() calls in your migrations as uuidv7_not_portable.

Temporal constraints

Booking, tenancy, pricing, and scheduling schemas all need the same rule: two rows for the same thing must not overlap in time. Before 18 that meant a hand-written exclusion constraint with a GiST index and a btree_gist extension. Postgres 18 makes it part of the key.

CREATE TABLE tenancies (
  unit_id  uuid,
  period   tstzrange NOT NULL,
  tenant   text NOT NULL,
  PRIMARY KEY (unit_id, period WITHOUT OVERLAPS)
);

Two tenancies for the same unit whose periods overlap are now rejected by the primary key. Foreign keys understand periods too:

CREATE TABLE rent_charges (
  tenancy_unit uuid,
  period       tstzrange NOT NULL,
  amount       numeric NOT NULL,
  FOREIGN KEY (tenancy_unit, PERIOD period)
    REFERENCES tenancies (unit_id, PERIOD period)
);

This is 18-only. On 16 and 17 you still write the exclusion constraint by hand.

Returning the row you just overwrote

UPDATE and DELETE on 18 can return both the pre- and post-image of every row they touch:

UPDATE contacts
   SET email = lower(email)
 WHERE email <> lower(email)
RETURNING old.email AS was, new.email AS now;

Use this on any bulk write. The values you are about to overwrite come back in the result - so if the WHERE clause turns out to have been wider than you intended, you have the exact per-column before-state in hand rather than having to reconstruct it.

It complements a restore point rather than replacing one. A restore point is how you recover; RETURNING old.* is how you know what changed. See Changing data safely for the full loop.

old and new are a syntax error on 16 and 17.

Virtual generated columns

Postgres 18 adds generated columns that are computed when you read them instead of being stored:

CREATE TABLE invoices (
  net   numeric NOT NULL,
  vat   numeric NOT NULL,
  gross numeric GENERATED ALWAYS AS (net + vat)          -- virtual on 18
);

On 18, GENERATED ALWAYS AS is virtual unless you write STORED. That is a change from 16 and 17, where stored was the only option. A virtual column occupies no storage and never goes stale, but it cannot be indexed and is recomputed on every read.

If you want the old behaviour, say so:

gross numeric GENERATED ALWAYS AS (net + vat) STORED

Faster reads on larger cells

Two 18 changes need nothing from you:

Skip scan. A multi-column index on (tenant_id, status) can now serve a query that filters on status alone. Before 18 that query needed its own index. CapyDB's index advisor knows this and stops suggesting narrow indexes that a wider one already covers on an 18 cell.

Asynchronous I/O. Postgres 18 can issue reads concurrently rather than one at a time, which speeds up sequential scans, bitmap scans, and vacuum. CapyDB sizes this per plan: the smallest plan runs without I/O worker processes at all, because its working set is already in cache and the processes would only cost wake time; larger plans get workers proportional to their allotment.

Choosing a version

New projects default to Postgres 17. Pick 18 explicitly if you want the features above:

capydb projects create my-app --postgres-version 18

Every extension CapyDB offers is available on all three majors, so the choice does not restrict you. Moving an existing project between majors is a major upgrade - a copy, a verification window, and a cutover you trigger.