1.0: tier cold partitions to Parquet on object storage

Part of the 1.0 time-series set the owner chose on 2026-10-12. All seven are listed on #1283.

What exists

pgcolumnar reads Parquet in place, reads Apache Iceberg tables (including through a REST catalog), and reaches object storage (docs/features.md, "Object storage" and "Apache Iceberg"). pgcolumnar.export writes Parquet.

What is missing

Tiering: moving cold data, typically old partitions, from local storage to Parquet on object storage, while queries keep working across both.

Proposed shape, to be challenged

Export a cold partition to Parquet on object storage.
Attach the exported files as a foreign-table partition, through the existing FDW, in place of the local partition.
Drop the local partition.

Every piece exists separately. The work is making it one safe operation, so a crash between steps neither loses data nor shows it twice.

Questions

Atomicity across the export, attach and drop. Which steps can share one transaction?
What the planner and partition pruning do with a mixed parent: local columnar partitions beside foreign ones.
Whether this needs 1.0: partition lifecycle for time-partitioned columnar tables, via pg_partman #1291's partition lifecycle first.

Done when

The design is agreed here. Then: an arm moves a partition and the answers match before and after, and a fault-injection arm shows a crash at each step leaves the data exactly once.

🤖 Generated with Claude Code

@jdatcmd

Design note 1: measured. The swap can be one transaction, and the price is a brief ACCESS EXCLUSIVE on the parent

PG 17 assert build at main. A RANGE parent on timestamptz with two columnar partitions (January and February, 8,001 rows). January is tiered to Parquet with pgcolumnar.export_parquet and the pgcolumnar_parquet FDW. A local file path stands in for object storage; s3:// is the same function with another path and is not measured here.

What works today, with no new code

BEGIN;
LOCK m_jan IN SHARE MODE;                                      -- writes to the cold partition wait
SELECT pgcolumnar.export_parquet('m_jan', '.../m_jan.parquet'); -- 4464 rows
CREATE FOREIGN TABLE m_jan_pq (...) SERVER pq OPTIONS (path '.../m_jan.parquet');
ALTER TABLE m DETACH PARTITION m_jan;
ALTER TABLE m ATTACH PARTITION m_jan_pq FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
DROP TABLE m_jan;
COMMIT;

question	measured
answers before and after the swap	identical: `count
pruning, February-only query	scans `m_feb` only
pruning, January-only query	scans `m_jan_pq` only, and returns 2736, the recomputed count
an insert routed to the tiered range	`ERROR: cannot insert into foreign table "m_jan_pq"`
pgcolumnar catalog rows of the dropped partition	0 left
a write to the cold partition during the export	waits on the SHARE lock (`lock timeout` at 500 ms), so it cannot be lost
parent lock held by the swap	AccessExclusiveLock (from `DETACH`), plus ShareUpdateExclusiveLock (`ATTACH`)

Atomicity: a rollback after every step leaves the data exactly once, still in the columnar partition. That holds after export, after CREATE FOREIGN TABLE, after DETACH, after ATTACH, and after DROP. The only residue is the exported file, which no transaction can take back. So the operation needs a unique file name per attempt, plus a sweep of files no foreign table references.

(One probe of mine reported "not the same" after an export-only rollback. In isolation that case is clean. The cause was my reset script: DROP TABLE refuses a foreign table left by the previous run, so the old table survived and the reset inserted twice. Recorded so nobody reads that line in the raw output as a product finding.)

The decision this needs: atomic swap, or no exclusive lock on the parent. Not both.

One transaction (above): data exactly once, at every crash point, measured. It holds ACCESS EXCLUSIVE on the parent for the length of the DDL. The export can run before the transaction, under the partition's SHARE lock alone, so the exclusive window is only catalog work. Tiering happens about once per partition per lifetime.
No exclusive lock: DETACH CONCURRENTLY cannot run inside a transaction or a function (Native partition lifecycle for time-partitioned columnar tables: create ahead, drop past retention #1297 note 1). So the swap becomes export, then detach concurrently, then attach, then drop, as separate transactions. A crash between detach and attach leaves the data stored once but invisible through the parent until a recovery step re-attaches it. "Exactly once" holds for storage and fails for answers during that window.

I'd take the first. The exclusive window is short, bounded and rare, and the alternative gives wrong answers on a crash, which is worse than a brief wait. This conflicts with #1297's rule ("never an exclusive lock on the parent"), so it's the owner's call. I'd ask jd rather than decide it here.

The issue's other two questions

A mixed parent: pruning behaves as above, and the planner treats the foreign partition like any other. Not yet measured: whether pgcolumnar's custom scan and vectorized aggregates still apply to the local partitions in a mixed Append. That's the next probe.
Does it need 1.0: partition lifecycle for time-partitioned columnar tables, via pg_partman #1291/Native partition lifecycle for time-partitioned columnar tables: create ahead, drop past retention #1297 first? No. The swap works on any RANGE parent. It shares Native partition lifecycle for time-partitioned columnar tables: create ahead, drop past retention #1297's drop half (DETACH and the parent lock), so the two should use one helper for that step.

@jdatcmd, anything in the shape to attack before I take the lock question to jd?

🤖 Generated with Claude Code

Owner's decision, 2026-10-12 (jd, asked in session): atomic, with a brief lock. The swap runs as one transaction and holds ACCESS EXCLUSIVE on the parent for the DDL only. The export runs beforehand, under the partition's SHARE lock. That's the explicit exception to #1297's 'never an exclusive lock on the parent' rule, because a wrong answer after a crash is worse than a short wait about once per partition. The next probe is the mixed-Append question (whether the custom scan and vectorized aggregates still apply to the local partitions); then arms, test-first.

@jdatcmd

The mixed-Append probe: the local partitions keep the custom scan

Same fixture, PG 17, max_parallel_workers_per_gather = 0, plans before and after tiering January:

all local   sum(v)            Aggregate -> Append -> PgColumnarScan m_jan, PgColumnarScan m_feb
all local   GROUP BY day      HashAggregate -> Append -> PgColumnarScan m_jan, PgColumnarScan m_feb
mixed       sum(v)            Aggregate -> Append -> Foreign Scan m_jan_pq, PgColumnarScan m_feb
mixed       GROUP BY day      HashAggregate -> Append -> Foreign Scan m_jan_pq, PgColumnarScan m_feb
mixed       February only     Aggregate -> PgColumnarScan m_feb  (January pruned)
answers     sum 32004000, 56 groups, the same before and after

So tiering costs the local partitions nothing at the plan level, and the issue's question is answered.

Not about tiering, but found here: over a partitioned parent, even with every partition local, the aggregate is core's Aggregate/HashAggregate above an Append. pgcolumnar's vectorized aggregate does not apply, because it plans per relation and the parent's aggregate sits above the Append. For the time-series set that matters, since its tables are partitioned. @jdatcmd, that's #1292's territory (vectorized GROUP BY date_bin): the gate you're building would not reach a partitioned parent at all. Worth a line on #1292, or its own issue, whichever you prefer.

@jdatcmd

Proposed API, so the arms can be written first (alpha6)

Following the owner's decision above: atomic swap, brief exclusive lock on the parent.

pgcolumnar.tier_partition(partition regclass, server name, path text) RETURNS bigint  -- rows moved

One call, so one transaction, in this order:

LOCK partition IN SHARE MODE. Writes to the cold partition wait; reads go on.
Export to path with the existing Parquet writer. An existing path is refused, so a retry can never overwrite a file that another foreign table already reads.
DETACH the local partition, then DROP it. The parent's ACCESS EXCLUSIVE starts here and ends at commit, so it covers catalog work only.
CREATE FOREIGN TABLE with the partition's own name, columns and owner, SERVER server OPTIONS (path ...). Queries that name the partition keep working.
ATTACH it with the partition's own bounds, read from pg_get_expr(relpartbound).

Refused with a SQLSTATE, before anything moves: a relation that is not a partition, a partition that is not columnar, a DEFAULT partition, and a server whose FDW is not pgcolumnar_parquet.

Fault injection, so "exactly once" is tested rather than argued: pgcolumnar.tier_fault_after is a superuser-only developer GUC taking export|detach|drop|create|attach. It raises an ERROR right after that step. The arms assert that the rows sit once in the local columnar partition, attached with their bounds, and that no foreign table is left.

What a failed attempt leaves: the exported file, which no transaction can take back (design note 1). This proposal does not sweep it. The function refuses to reuse the path, and the docs say so. An orphan sweep can come later if it's wanted. For a local path, an abort callback could unlink the file; for s3:// that is a remote delete, so I'd keep it out of the first version.

Arms, written red before the code (test/tier_partition.sh and an independent pytest twin):

the answers over the parent match before and after, using a forced decode, not zone-map aggregates;
the tiered range is a foreign table with the same name and bounds, and the local partition leaves no pgcolumnar catalog rows;
pruning: a query on the tiered range scans only the foreign partition, and one on the other range scans only the local one;
an injected failure after each of the five steps leaves the data exactly once, still local;
an insert routed to the tiered range is refused, which is core's behaviour and documented;
the parent's lock inside the call is ACCESS EXCLUSIVE, asserted because the docs will say so;
each refusal above has its SQLSTATE, and an existing path is refused.

@jdatcmd, attack the shape. In particular, is reusing the partition's name right, or should it get a suffix? I'm writing the arms against this now; they change cheaply if the shape does.

🤖 Generated with Claude Code

Review of the proposed shape. The order holds up, and so does reusing the name. One finding loses data and needs a design answer before the arms freeze. Two refusals are missing, and one lock interaction needs an arm.

Gate change first: the owner decided today that alpha5 waits for all seven time-series features, so this is alpha5 work, not alpha6 (posted on #1292).

1. Blocking: the SHARE lock does not refresh the snapshot, so rows can be dropped unexported

Measured on PG18 with heap tables. Session A begins, reads, then takes LOCK t IN SHARE MODE. Session B inserts 50 rows and commits between A's first read and A's lock.

                    A before lock   A after SHARE lock   committed
READ COMMITTED          100               150               150
REPEATABLE READ         100               100               150

Under REPEATABLE READ or SERIALIZABLE, step 2 exports 100 rows and step 3 drops 150. The 50 rows a concurrent writer committed before the lock was granted are gone, and nothing errors.

READ COMMITTED is safe only if the export reads under a snapshot taken after the lock. A C function that reads through GetActiveSnapshot() reads the calling statement's snapshot, which predates the lock. That is the same loss, inside one call.

Proposal:

Refuse unless XactIsoLevel == XACT_READ_COMMITTED.
After the lock, push a fresh snapshot (GetLatestSnapshot()) for the export.
Arm: a writer commits between the caller's snapshot and the lock grant, and every row survives. Run it once at each isolation level; the arm must be red against the current shape.
Mutation: export under the statement snapshot.

Also check the rows-moved count against a count(*) taken under that same fresh snapshot, and abort if they differ. That turns any remaining gap into an error rather than a loss.

2. Missing refusal: a parent with any UNIQUE index or primary key

The PG18 ALTER TABLE reference says ATTACH of a foreign table is "currently not allowed ... if there are UNIQUE indexes on the target table". Today that surfaces at step 5, after the export, as an error that leaves an orphan file. Refuse it up front with a SQLSTATE, and add an arm.

3. Missing refusal: anything that depends on the partition's OID

The DROP in step 3 fails, or with CASCADE silently removes things, if a view, rule, publication membership, policy or foreign key names the partition. Refuse when pg_depend shows a dependent the swap cannot recreate. Recreate the ACL, owner and comment on the new foreign table. Arm: a view over the partition is refused before anything moves.

4. Arm needed: an insert routed to the cold partition, racing the swap

The function holds SHARE on the partition, then asks for ACCESS EXCLUSIVE on the parent at DETACH. An INSERT already in flight holds ROW EXCLUSIVE on the parent and waits for ROW EXCLUSIVE on the partition. That looks like a lock cycle, and the deadlock detector would abort one side. I haven't run it, so it's a hypothesis.

Separately, while the AE request waits, every new query on the parent queues behind it. The "brief" lock is only as brief as the longest transaction already open on the parent.

Proposal: take the parent's lock with a bounded wait, a lock_timeout-style argument, and treat a timeout like the injected faults: data still local, nothing attached. One arm for the in-flight insert, one for the timeout.

5. Retry after a failure versus refusing an existing path

With #4's timeout, a failed attempt after step 2 is the normal case, not a rare one. Refusing an existing path then makes every retry need a new path, and leaves an orphan per attempt. A narrower refusal still protects other readers: refuse a path that a foreign table in this database names in its options, and overwrite one that none does. A reader outside the database is the remaining risk, and the docs can name it. If you keep the blanket refusal, the docs should give the retry recipe.

Agreed as proposed

Reusing the partition's name: yes, no suffix. Queries naming the partition keep working, and Tag and publish the MIT 1.0 release #3 covers what an OID change breaks.
The five fault points and "exactly once, still local": right.
ATTACH does not scan a foreign table: right. The docs say nothing is done to verify its rows, so the bounds you read from relpartbound are trusted as-is. That makes the "same bounds" arm load-bearing.

One addition: ANALYZE the new foreign table before commit, if pgcolumnar_parquet supports it, so the planner doesn't price the tiered range from nothing.

The proposal above is built locally and passes on PG15 to PG19, with the arms written first

Nothing is pushed. It needs the alpha6 cycle opened, which is the owner's commit to make, so for now it sits on a local branch against a stand-in for that commit. The PR comes after the tag.

test/tier_partition.sh was red on main at all 21 arms that need the function. With the function in place it is 40/40 on PG15–19. The independent pytest twin is 40/40 too, and compare_to_bash finds every shell property covered.
Upgrading from every released version (1.0-alpha through 1.0-alpha4) through the new script gives a catalog byte-identical to a fresh install, per native_upgrade_converge.sh.

The arms found two real bugs while the function was being written:

The existing-path refusal never fired. pg_stat_file(path, true) IS NOT NULL is false for a file that exists: a row is NOT NULL only when every field is, and creation is NULL on Linux. A second move then exported February over the file January's foreign partition was reading, and the parent returned February twice (16128 = 2 × 8064). It now tests one field. As a side note, export_parquet itself overwrites an existing path without a word, measured on main.
The SHARE lock is load-bearing, not hygiene. I added an arm in which a second session holds an uncommitted insert into the partition when the move starts. With the lock, the move waits about 2 s and exports the row. With the lock removed, the export does not see the row, DETACH waits for the insert to commit, and DROP takes the row with the partition: silently lost.

Removal proofs, in both twins: removing the SHARE lock, the path check, the DEFAULT check, the wrapper check or the partition check each turns its arm red. So does suffixing the foreign table's name, or shifting the attached upper bound by one minute. One mutant survived: deleting the "is it columnar" check, because export_parquet already refuses a heap partition with 42809 before anything moves. So that check was redundant, and I removed it.

The two questions to you still stand: the shape in general, and whether the foreign table should take the partition's own name.

🤖 Generated with Claude Code

Thanks. All five points are taken, plus the ANALYZE. Point 1 is right, and my in-flight arm can't see it, because it only covers a writer that is still uncommitted when the move starts.

Plan, each arm red against the current function before any code changes:

Isolation. A writer commits between the caller's snapshot and the lock grant, run at READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Every row must survive. The function refuses anything but READ COMMITTED, exports under a snapshot taken after the lock, and checks the exported count against a count(*) under that snapshot, aborting on a mismatch.
A UNIQUE index or primary key on the parent is refused up front, with an arm.
Dependents of the partition, checked against an allowlist from pg_depend. A view, a policy, a foreign key and a local trigger are each refused with an arm. The ACL and comment are carried over to the foreign table, with an arm.
The in-flight insert racing the swap gets an arm, and the parent lock gets a bounded wait (lock_wait, default 10 s). A timeout is treated like a fault: the data stays local and nothing is attached.
Path refusal: your narrower rule. Refuse a path that a foreign table in this database reads, and allow a retry over an orphan, with an arm.
ANALYZE the new foreign table before commit, if pgcolumnar_parquet supports it.

This goes against alpha5, with no alpha6 scaffold. The TID range PR comes first, then #1297. I'll also review #1304 with measured numbers.

🤖 Generated with Claude Code

test: tier_partition moves a cold partition to Parquet, exactly once …

feat: pgcolumnar.tier_partition moves a cold partition to Parquet in …

test, docs: register tier_partition and describe it (commandprompt#1295)

test: tier_partition ledger, from a five-major run and sixteen mutati…

test: tier_partition ledger after the commandprompt#1317 review, from…

test: tier_partition moves a cold partition to Parquet, exactly once …

feat: pgcolumnar.tier_partition moves a cold partition to Parquet in …

test, docs: register tier_partition and describe it (commandprompt#1295)

test: tier_partition ledger, from a five-major run and sixteen mutati…

test: tier_partition ledger after the commandprompt#1317 review, from…

Merge pull request #1317 from OffgridwithJD/feat/1295-tier-partition

Done in #1317, merged at 305e15e. pgcolumnar.tier_partition(partition, server, path, lock_wait) moves a cold columnar partition to a Parquet foreign partition, exactly once.

Locking: a brief ACCESS EXCLUSIVE on the parent, per the owner's decision, bounded by lock_wait.
Isolation: READ COMMITTED only, because REPEATABLE READ or SERIALIZABLE would drop rows committed before the lock.
Refused up front: a parent with UNIQUE indexes, and dependent objects.
Carried over: grants, comment, owner, collation, and stored and virtual generated columns.
Tests: 71 checks per twin on PG15–19, including fault injection after every step.

Navigation Menu

Repository navigation

1.0: tier cold partitions to Parquet on object storage #1295

What exists

What is missing

Proposed shape, to be challenged

Questions

Done when

Design note 1: measured. The swap can be one transaction, and the price is a brief ACCESS EXCLUSIVE on the parent

What works today, with no new code

The decision this needs: atomic swap, or no exclusive lock on the parent. Not both.

The issue's other two questions

The mixed-Append probe: the local partitions keep the custom scan

Proposed API, so the arms can be written first (alpha6)

1. Blocking: the SHARE lock does not refresh the snapshot, so rows can be dropped unexported

2. Missing refusal: a parent with any UNIQUE index or primary key

3. Missing refusal: anything that depends on the partition's OID

4. Arm needed: an insert routed to the cold partition, racing the swap

5. Retry after a failure versus refusing an existing path

Agreed as proposed

The proposal above is built locally and passes on PG15 to PG19, with the arms written first

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

1.0: tier cold partitions to Parquet on object storage #1295

Description

What exists

What is missing

Proposed shape, to be challenged

Questions

Done when

Activity

OffgridwithJD commented on Oct 12, 2026

Design note 1: measured. The swap can be one transaction, and the price is a brief ACCESS EXCLUSIVE on the parent

What works today, with no new code

The decision this needs: atomic swap, or no exclusive lock on the parent. Not both.

The issue's other two questions

OffgridwithJD commented on Oct 12, 2026

OffgridwithJD commented on Oct 12, 2026

The mixed-Append probe: the local partitions keep the custom scan

OffgridwithJD commented on Oct 12, 2026

Proposed API, so the arms can be written first (alpha6)

jdatcmd commented on Oct 12, 2026

1. Blocking: the SHARE lock does not refresh the snapshot, so rows can be dropped unexported

2. Missing refusal: a parent with any UNIQUE index or primary key

3. Missing refusal: anything that depends on the partition's OID

4. Arm needed: an insert routed to the cold partition, racing the swap

5. Retry after a failure versus refusing an existing path

Agreed as proposed

OffgridwithJD commented on Oct 12, 2026

The proposal above is built locally and passes on PG15 to PG19, with the arms written first

OffgridwithJD commented on Oct 12, 2026

jdatcmd commented on Oct 13, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions