PostgreSQL 18 Upgrade: Why Your Columns Still Use the Old uuidv7 Polyfill
By Michael Cooper · Founder
We moved a populated production database from PostgreSQL 17.9 to 18.3 in place, on Amazon Aurora Serverless v2, and wrote down what bit us and what did not. The headline finding is a trap that has nothing to do with us and everything to do with how Postgres binds column defaults: after the upgrade, every one of our uuidv7() columns was still calling the old polyfill, and you cannot just drop it.
What changed, in numbers
| Metric | Value |
|---|---|
| Write-outage during the in-place upgrade | ~10.3 min, self-recovered (no restart) |
| Columns still bound to the polyfill after upgrade | 115 |
| Native vs polyfill uuidv7() - function (1M) | ~396 vs ~1042 ms (2.6×) |
| Native vs polyfill - PK index size (500k) | 15 vs 20 MB (33% bigger) |
| In-place-upgraded DB vs fresh DB - PK insert (500k) | ~2870 vs ~3081 ms (equal) |
| Signed records re-verified after upgrade | 42,007 records, 0 broken |
| PG 17 polyfill vs PG 18 native - uuid-keyed insert (500k) | ~741 vs ~406 ms (45% faster) |
Isolated SQL benchmarks are median of 5 (500k-row inserts, PG 18.3). Upgrade and verification figures are from the live run, Amazon EKS 1.36 + Aurora Serverless v2, 2026-06-29. The cross-version row is a separate local-box pass, median of 3, added 2026-08-03 and detailed below; it is an UNLOGGED insert, so it is not directly comparable to the logged primary-key rows above it.
How long is the write outage on an in-place 17 to 18 upgrade?
We set the cluster’s engine version to 18.3 with allow_major_version_upgrade against a populated database and watched the application from the outside. The write path returned 503 for a ~10.3-minute window; total upgrade wall time was about 11.5 minutes. Then the API recovered on its own, with no pod restart: the connection pool reconnected once Aurora came back, and the first post-recovery write succeeded.
Read that number as a floor, not an estimate. The database was small, tens of thousands of rows, so 10.3 minutes is close to Aurora's fixed overhead for a major version bump rather than anything driven by our data. A multi-terabyte cluster will take longer, and how much longer is not something this run can tell you. What it does tell you is that the floor is minutes, not seconds, and that the application came back without help. Aurora Blue/Green Deployments are the route if you need a switchover measured in seconds; we did not use them here.
Why is my column still using the old uuidv7 function after upgrading?
This is a general PostgreSQL gotcha, not a uuidv7 one. State it without uuidv7 and it sounds obvious in hindsight:
A stored column DEFAULT binds to a function’s OID at parse time. Adding a same-named native function in a later major version does not re-point existing defaults. They keep calling the old one.
Native uuidv7()is new in PostgreSQL 18. Anyone who adopted UUIDv7 on 15 through 17 installed a SQL polyfill (there are a handful of widely-copied gists). We had one too, in the public schema. A fresh 18 install never creates it and resolves uuidv7() straight to pg_catalog.uuidv7. An in-place upgrade does not: the polyfill persists, and every one of our 115 DEFAULT uuidv7() columns stayed bound to it. Inserts kept calling the polyfill, and row counts and “it still works” would never tell you.
The cleanup is where the OID pinning bites a second time. A naive DROP FUNCTION public.uuidv7() fails, because 115 column defaults depend on it. CASCADEwould “succeed” by stripping those defaults, which silently breaks every insert that relied on them. The correct order is to re-point each default first, which rebinds it to the native function, and only then drop the polyfill:
-- Re-point, then drop. The ALTER rebinds the default to pg_catalog.uuidv7;
-- the polyfill has no dependents left, so the DROP is safe.
ALTER TABLE public.records ALTER COLUMN id SET DEFAULT uuidv7();
ALTER TABLE public.audit_log ALTER COLUMN id SET DEFAULT uuidv7();
-- ... every other DEFAULT uuidv7() column ...
DROP FUNCTION public.uuidv7();You do not have to maintain that list by hand. Because the dependency is recorded in pg_depend, you can ask the catalog which defaults are bound to the polyfill’s OID, which is the same fact that caused the problem:
SELECT n.nspname || '.' || c.relname AS tbl, a.attname AS col
FROM pg_depend dep
JOIN pg_attrdef d ON d.oid = dep.objid
JOIN pg_class c ON c.oid = d.adrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE dep.classid = 'pg_attrdef'::regclass
AND dep.refclassid = 'pg_proc'::regclass
AND dep.refobjid = 'public.uuidv7()'::regprocedure;Here is the whole thing as one idempotent block. It acts only when both the native function and the polyfill exist, so it is a no-op on a database created fresh on 18, and safe to leave in a migration that runs everywhere. This is what we ran.
DO $$
DECLARE r record;
BEGIN
-- only act if BOTH native and the public polyfill exist
IF EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'uuidv7' AND n.nspname = 'pg_catalog')
AND EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'uuidv7' AND n.nspname = 'public') THEN
FOR r IN
SELECT n.nspname AS sch, c.relname AS tbl, a.attname AS col
FROM pg_attrdef ad
JOIN pg_class c ON c.oid = ad.adrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
WHERE pg_get_expr(ad.adbin, ad.adrelid) ILIKE '%uuidv7%'
LOOP
EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I SET DEFAULT uuidv7()', r.sch, r.tbl, r.col);
END LOOP;
DROP FUNCTION public.uuidv7();
END IF;
END $$;One assumption worth checking before you run it. The re-point works because the unqualified uuidv7() in SET DEFAULT resolves to pg_catalog, which Postgres searches implicitly before the rest of search_path. If your database sets search_path explicitly with public ahead of pg_catalog, the new default rebinds to the polyfill and the migration silently does nothing, which is the same failure this whole post is about, hiding inside its own fix. Check with SHOW search_path; first, or write SET DEFAULT pg_catalog.uuidv7() and remove the doubt.
To confirm it worked, re-run the pg_depend query above. Zero rows means nothing is bound to the polyfill any more. Note it raises an error rather than returning zero rows once the polyfill is dropped, since ::regprocedure cannot resolve a function that no longer exists, so it is a check to run before and immediately after, not a fleet-wide sweep.
Rows written through the polyfill in the meantime are fine and need no backfill. Both functions produce RFC 9562 UUIDv7 values; the polyfill is slower and orders slightly worse inside a millisecond, but what is already stored is valid and stays put.
What skipping the cleanup costs you
Isolated SQL benchmarks, 500k-row inserts, median of 5, on PostgreSQL 18.3:
| Operation | Native | Polyfill | Penalty |
|---|---|---|---|
| Function alone (1M calls) | ~396 ms | ~1042 ms | 2.6× slower |
| Heap insert, no index | ~1452 ms | ~1875 ms | ~29% slower |
| Insert with UUID primary key | ~3081 ms | ~3463 ms | ~12% slower |
| Primary-key index size | 15 MB | 20 MB | 33% bigger |
The function is 2.6× slower in isolation, but end to end the gap shrinks, because the insert (and, in our case, signing the record) dominates the call. Through our full signed-insert path the difference was: single-record throughput was flat within noise, bulk inserts ran roughly 6 to 9% faster on native (single run, rate limiter off to isolate the engine).
The cost that does not show up as latency is the 33% larger index in this run. The polyfill’s weaker intra-millisecond ordering packs the B-tree worse, so the primary-key index stays bloated until you reindex (which native ordering avoids needing): more storage and more buffer-cache pressure in the meantime. The gap scales with how many rows you write per millisecond, so treat 33% as our number, not a constant. Do not panic over the function’s microseconds, but if you are write-heavy, clean it up.
Update, 2026-08-03: the cross-version number, and why the win may be invisible to you
Everything above compares native against polyfill on the same PostgreSQL 18, which is the question you face after an in-place upgrade: is the cleanup worth doing? It does not answer the question you face before one. So we ran the other half: PostgreSQL 17 with the polyfill against PostgreSQL 18 with the native function, which is the actual before-and-after of the upgrade.
Same application image against postgres:17-alpine (17.10) and postgres:18-alpine (18.4), migrations applied fresh on each. 500k-row insert into an UNLOGGED table whose id column defaults to the generator under test, EXPLAIN (ANALYZE, TIMING OFF) execution time, median of 3, with a gen_random_uuid() (UUIDv4) control to net out the base insert machinery.
| Generator | PG 17 | PG 18 |
|---|---|---|
| uuidv7 (polyfill on 17, native on 18) | 740.6 ms | 406.3 ms |
| gen_random_uuid (v4 control) | 419.6 ms | 398.0 ms |
| uuidv7 overhead over v4 | ~321 ms | ~8 ms |
The control lands at roughly 400 to 420 ms on both versions, so the base insert path is comparable and the entire remaining difference is the uuid generator. On 17 the polyfill adds about 321 ms per 500k rows over v4, which is roughly 0.64 microseconds per row of plpgsql call overhead. On 18 native uuidv7 adds about 8 ms, which is effectively free. For a pure uuid-keyed insert, that is a ~45% reduction in execution time moving 17 to 18.
Through the application’s own bulk-insert path on that same box, POST /v1/records/bulkin batches of 100, the difference was about 1 to 2%: roughly 211 records/s on 17 against 215.6 on 18, batch p50 467 against 461 ms. That is inside the noise. Every record we write carries an Ed25519 signature and hash-chain work, about 4.6 ms per record on this hardware, and that dwarfs a sub-microsecond uuid call.
That is consistent with the 6 to 9% measured on Aurora in June rather than contradicting it: how much of the uuidv7 win you can see depends on how fast your signing is. The faster the rest of your write path, the larger a share the uuid generation represents, and the more of that DB-layer 45% surfaces end to end. On a slow box it falls inside the noise, as it did here. The single-record path is signing-bound on any hardware, so it stays flat either way (not re-measured this pass; the mechanism is unchanged).
Caveat on this pass: a single local box, modest N, three runs. The DB-layer figures were tight across repeats; treat the app-level figures as directional.
Does an in-place upgrade perform worse than a fresh install?
The obvious next worry: once cleaned up, does an in-place-upgraded database carry hidden baggage that a fresh install would not? We ran the same native primary-key insert benchmark (500k rows, 5 runs) in the upgraded database and in a brand-new database on the same cluster: ~2870 ms versus ~3081 ms. The upgraded database was marginally faster. There is no in-place penalty.
For new data, an in-place major upgrade performs like a fresh install. The only legacy lives in pre-existing objects: the bound defaults above, and the optimizer statistics below. Not in the engine.
Do you have to re-analyze statistics after the upgrade?
After the upgrade our largest table had last_analyze = NULL, and autovacuum re-analyzed it about nine minutes later (upgrade completed ~20:32, autoanalyze fired ~20:41). Be careful what you infer from that: a major upgrade resets the last_analyze timestamp regardless of whether the actual histograms carried over, so a NULL on its own tells you no ANALYZE has run since the upgrade, not that the planner is blind.
PostgreSQL 18 improved pg_upgrade to carry statistics across a major upgrade, and Aurora’s mechanism may differ; we did not confirm whether the carried histograms survived for that table. Run ANALYZE yourself right after a major upgrade instead of waiting for autovacuum, especially on a busy database.
How do you check whether the upgrade corrupted anything?
pg_upgrade does not rewrite heap data, so silent heap corruption is not the realistic risk. Index corruption is, and it usually comes from a collation change rather than from Postgres itself: if the upgrade also moved you to a new OS image, a different glibc can reorder text comparisons and quietly invalidate every text-keyed B-tree. Check with amcheck, or across a whole database with pg_amcheck --heapallindexed. Reindex what it flags. Aurora 18 kept the same collation version across our upgrade, so this was not a factor for us, but it is the first thing to rule out.
We could also check the question directly rather than infer it from the application still working, because our records are hash-chained and Ed25519-signed. After the upgrade we re-verified every record, all 42,007 records, every chain link and signature, with zero breakage, and re-checked a sample offline against an out-of-band key (refusing the server’s own embedded key). That is a property of how we store these records, not a general upgrade check you can run on your own database.
FAQ
Why does my PostgreSQL major-version upgrade still use the old uuidv7 polyfill instead of the native function?
A column DEFAULT binds to a function OID at parse time. An in-place major upgrade does not re-point existing defaults, so they keep calling the polyfill. Re-point them with ALTER TABLE ... ALTER COLUMN ... SET DEFAULT uuidv7().
Why can I not DROP the uuidv7 polyfill after upgrading to PostgreSQL 18?
The column defaults still resolve to it, so they are dependent objects and a bare DROP FUNCTION fails. CASCADE would strip the defaults and break inserts. Re-point the defaults to the native function first, then drop.
How much downtime does an in-place Aurora 17 to 18 major upgrade take?
In our run, about a 10.3-minute write-outage and ~11.5 minutes wall time. The application self-recovered with no restart once the database returned.
Is the native PostgreSQL 18 uuidv7 faster than a SQL polyfill?
Yes: about 2.6× as a function in isolation, smaller end to end, and the polyfill also leaves a primary-key index about 33% larger.
How much faster is PostgreSQL 18 than 17 for uuid-keyed inserts?
About 45% on a pure uuid-keyed insert: 740.6 ms on 17 with the polyfill against 406.3 ms on 18 with native uuidv7 (500k-row UNLOGGED insert, median of 3). A UUIDv4 control ran 419.6 against 398.0 ms, so the base insert path is comparable and the whole difference is the generator.
Why did my application not get faster after upgrading to PostgreSQL 18?
Because uuid generation is probably not your bottleneck. The DB-layer gain is real but sub-microsecond per row, so heavier per-row work hides it. Our Ed25519 signing at ~4.6 ms per record cut an end-to-end bulk gain to 1 to 2% on a slow box against 6 to 9% on Aurora. How much of the uuidv7 win you see depends on how fast the rest of your write path is.
Sources & further reading
- PostgreSQL 18 release notes (native
uuidv7(), pg_upgrade statistics): postgresql.org - PostgreSQL documentation - UUID functions: postgresql.org
- PostgreSQL documentation - ALTER TABLE, column defaults and dependencies: postgresql.org
- RFC 9562 - UUID Version 7 (time-ordered): rfc-editor.org