What does it mean to archive Postgres data to S3 Parquet, and which data is cold?
To archive Postgres data to S3 Parquet means running a recurring lifecycle process. Rows move out of the primary database once they stop being useful there. They are kept in cheap object storage for a defined period and then deleted. A one-off export script does not do this. It leaves no record of what was moved and no rule for when the next batch moves.
This approach suits teams with large append-mostly tables: events, application logs, order history, audit trails. These tables grow steadily, their old rows are almost never updated, and reads concentrate on recent data. Old rows still take up disk, backup time, and replica storage, so moving them out is where the storage savings come from.
Finding the cold boundary
Measure how far back queries actually reach before picking a cutoff. pg_stat_statements shows which normalized queries hit a table and how often. It does not record parameter values, so it cannot tell you which date ranges are requested. For that, enable statement logging for the relevant tables for a while and look at the timestamp predicates in the logged queries. You might find, for example, that almost every read touches the last 90 days, with occasional support lookups reaching back a year.
Writing the policy down
Turn that finding into a written data retention policy, agreed with product and compliance: hot in Postgres for N months, archived in S3 for M years, then deleted.
Settle right-to-erasure obligations at this stage. Parquet files are immutable. Deleting one user's rows means rewriting every file that contains them, and that becomes expensive once years of archives exist.
-- 1. Which normalized queries touch the events table, and how often?
-- pg_stat_statements stores query text with parameters replaced ($1, $2),
-- so this shows query shapes, not the date ranges requested.
SELECT calls,
round(total_exec_time::numeric, 1) AS total_ms,
rows,
left(query, 120) AS query_shape
FROM pg_stat_statements
WHERE query ILIKE '%events%'
ORDER BY calls DESC
LIMIT 20;
-- 2. Capture actual parameter values for a sampling window.
-- Scope logging to the application role so the log stays manageable.
-- New sessions for this role pick up the setting; revert it afterwards.
ALTER ROLE app_api SET log_min_duration_statement = 0;
-- ...after the sampling window ends:
-- ALTER ROLE app_api RESET log_min_duration_statement;
-- 3. See how rows are spread by age. Each month older than the
-- agreed cutoff is a candidate batch for archiving.
SELECT date_trunc('month', created_at) AS month,
count(*) AS row_count
FROM events
GROUP BY 1
ORDER BY 1;
Queries for finding the cold boundary: pg_stat_statements shows which query shapes hit the table, temporary per-role statement logging captures the date predicates actually used, and a monthly row count shows how much data falls beyond a candidate cutoff.
Why does time-based table partitioning make archiving cheap?
With declarative range partitioning on a timestamp column, each month (or week) of data lives in its own physical table. Archiving becomes a file operation instead of a row operation. You export one closed partition, verify the export, then DETACH it from the parent and DROP it. Dropping a table unlinks its files. It does not scan rows, it writes almost no WAL, and it leaves nothing behind for VACUUM to clean up.
A large DELETE ... WHERE created_at < ... behaves very differently. Every removed row is marked dead in place and logged to WAL, which also inflates replication traffic and backups. The table and its indexes keep their size until autovacuum works through them, and that work competes with production I/O. Space freed this way is usually reused by the same table, not returned to the OS.
Bloat left by earlier DELETE-based cleanup stays in place after you switch to partitioning. Reclaiming it takes one of two tools:
VACUUM FULLholds an exclusive lock for the entire rewrite.pg_repackneeds only brief locks, but it requires free disk space roughly equal to the table plus its indexes, and it generates sustained I/O.
Retrofitting partitioning onto a large live table is the riskiest step, so do it incrementally:
- Create a new partitioned parent with matching columns.
- Route new writes to it through dual writes or a trigger.
- Backfill history in small, throttled batches.
- Swap names in a short transaction once the data is verified.
Before you start, check your constraints. Primary keys and unique constraints on a partitioned table must include the partition key. A primary key on id alone therefore becomes (id, created_at). Audit any foreign keys and application code that assume id is unique by itself.
-- New partitioned parent. The primary key must include the partition key,
-- so a key on id alone becomes (id, created_at).
CREATE TABLE events_new (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- One partition per month. The upper bound is exclusive.
CREATE TABLE events_2026_09 PARTITION OF events_new
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events_new
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events_new
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
-- Backfill one small, bounded slice per batch. Repeat with the next range
-- and pause between batches to limit WAL and I/O pressure.
-- OVERRIDING SYSTEM VALUE keeps the original ids. After the backfill, set the
-- identity sequence above the highest copied id.
INSERT INTO events_new (id, created_at, payload)
OVERRIDING SYSTEM VALUE
SELECT id, created_at, payload
FROM events
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';
-- Archive a closed month. First export events_2026_09 to Parquet and verify
-- the export. Then detach the partition so queries on the parent no longer see it.
-- On PostgreSQL 14+, DETACH PARTITION ... CONCURRENTLY avoids a long lock on the
-- parent, but it cannot run inside a transaction block.
ALTER TABLE events_new DETACH PARTITION events_2026_09;
-- Dropping the table unlinks its files. There is no row-by-row DELETE,
-- very little WAL is written, and no dead tuples are left for VACUUM.
DROP TABLE events_2026_09;
Creates a monthly range-partitioned table whose primary key includes the partition key, backfills history in a small bounded batch, and retires a closed month with DETACH PARTITION and DROP TABLE instead of a large DELETE.
How does the archive pipeline work from partition to S3?
Each partition moves through a fixed sequence. Every step can be retried, and nothing is destroyed until the archive has been proven readable.
- Export. The exporter reads one closed partition, such as
events_2024_01, and writes Parquet to a temporary key like_tmp/<run_id>/. A crashed run then leaves only debris under_tmp/, never a half-written file in the live prefix. - Verify. Re-read the uploaded object and compare its row count and a column checksum against the source partition.
- Promote and record. Copy the object to its final key and write a manifest listing file names, sizes, row counts and checksums. Insert a row into an archive catalog table in Postgres. That row is the record that the partition is safely archived.
- Detach and wait. Detach the partition, but keep it as a standalone table for a grace period. Late readers or a bad export can still be recovered without touching S3.
- Drop. After the grace period, drop the table.
The final layout uses Hive-style prefixes, for example table=events/year=2024/month=01/part-0000.parquet. Query engines that understand this convention prune by path, so a query for one month never lists or opens other months' objects.
| Choice | Effect on scan cost | Effect on S3 requests |
|---|---|---|
| Files of tens to a few hundred MB | Efficient sequential reads | Few GETs and LISTs per query |
| Many small files | Per-file overhead dominates | Request count grows with file count |
| Row groups sized for pruning | Min/max statistics let readers skip row groups | Fewer range GETs per file |
| Snappy | Fast decompression, larger files | More bytes transferred |
| Zstd | Smaller files, slightly more CPU per read | Fewer bytes stored and scanned |
For cold data that is rarely read, Zstd's smaller footprint usually matters more than Snappy's faster decompression.
POSTGRES S3 BUCKET
+---------------------------+ +-------------------------------+
| events (partitioned) | | _tmp/<run_id>/ |
| events_2024_01 (closed) |--1 export--->| part-0000.parquet |
+---------------------------+ +---------------+---------------+
| 2 verify: re-read
| row count+checksum
v 3 promote: copy
+-------------------------------+
| table=events/year=2024/ |
| month=01/part-0000.parquet |
| manifest: file names, sizes,|
| row counts, checksums |
+---------------+---------------+
|
+---------------------------+ |
| archive_catalog (Postgres)|<-- 3 insert catalog row -----+
| partition safely archived|
+-------------+-------------+
| 4 DETACH PARTITION
v
+---------------------------+
| events_2024_01 standalone |
| grace period: recoverable |
+-------------+-------------+
| 5 DROP TABLE after grace period
v
(partition gone; S3 copy + catalog row remain)
Lifecycle of one closed partition: export to a temporary S3 key, verify, promote to a Hive-style final prefix with a manifest, record it in the Postgres archive catalog, then detach, wait out the grace period, and drop.
How do you export a partition to Parquet with explicit types?
Stream rows rather than loading a partition into memory. Two options work well. COPY (SELECT ...) TO STDOUT is the fastest path out of Postgres. A server-side (named) cursor fetching batches of, say, 50,000 rows gives you typed Python values directly. Either way, convert each batch to a pyarrow RecordBatch and append it with a ParquetWriter. Memory stays flat no matter how large the partition is.
Declare the Arrow schema up front instead of letting pyarrow infer it. Inference only sees the first batch. It can guess float64 for money, or pick null type for a column that happens to be empty early on, and it gives no warning.
| Postgres type | Arrow type | Why |
|---|---|---|
timestamptz |
timestamp('us', tz='UTC') |
Matches Postgres microsecond precision and removes ambiguity about session time zones |
numeric(p,s) |
decimal128(p, s) |
Keeps exact values. Unconstrained numeric forces you to pick a scale and reject rows that don't fit |
jsonb |
string |
Stores the canonical text form, which is portable and re-parseable |
int[], text[] |
list_(int64), list_(string) |
Preserves element types and NULL elements |
| enum | dictionary(int32, string) |
Stores labels, not internal OIDs, so files survive enum changes |
Silent degradation hurts most with numeric cast to float, because cents drift without any error. It also happens with timestamp (no time zone) read as local time, and with CSV COPY collapsing NULL and empty string into the same value. For financial or audit records, a restore that is almost right can be worse than no restore, because it looks trustworthy.
Write a schema_version key into each file's Parquet key-value metadata. Keep a mapping from each version to its column list. Readers can then use it to handle columns that were added, renamed, or retyped years after the earliest files were written.
import psycopg
import pyarrow as pa
import pyarrow.parquet as pq
SCHEMA_VERSION = "3" # bump whenever columns are added, renamed or retyped
# Explicit schema: nothing is inferred from the first batch.
SCHEMA = pa.schema([
pa.field("id", pa.int64(), nullable=False),
pa.field("created_at", pa.timestamp("us", tz="UTC")), # timestamptz
pa.field("amount", pa.decimal128(18, 2)), # numeric(18,2), exact
pa.field("payload", pa.string()), # jsonb as canonical text
pa.field("tags", pa.list_(pa.string())), # text[]
pa.field("status", pa.dictionary(pa.int32(), pa.string())), # enum label
], metadata={b"schema_version": SCHEMA_VERSION.encode()})
# Cast jsonb and the enum to text in SQL so the driver hands back strings, not parsed objects.
QUERY = """
SELECT id, created_at, amount, payload::text, tags, status::text
FROM ledger_entries_2023_01 ORDER BY id
"""
with psycopg.connect("postgresql://archiver@db.internal:5432/app") as conn:
# A named cursor is server-side: rows are fetched in batches, never all at once.
with conn.cursor(name="export_2023_01") as cur, \
pq.ParquetWriter("ledger_entries_2023_01.parquet", SCHEMA) as writer:
cur.execute(QUERY)
while rows := cur.fetchmany(50_000):
columns = list(zip(*rows))
# pa.array raises instead of silently rounding if a Decimal
# does not fit decimal128(18, 2). That is the failure you want.
arrays = [pa.array(col, type=f.type) for col, f in zip(columns, SCHEMA)]
writer.write_batch(pa.RecordBatch.from_arrays(arrays, schema=SCHEMA))
# Readers check the version before mapping columns.
meta = pq.read_schema("ledger_entries_2023_01.parquet").metadata
print(meta[b"schema_version"].decode())
Streams one partition through a psycopg server-side cursor into a ParquetWriter using an explicit pyarrow schema, with the schema version stored in the file's key-value metadata.
How can you make the archive verifiable, idempotent, and safe to drop?
Each export writes to a staging prefix such as _staging/orders/2023-04/. S3 has no atomic rename, so finalizing means copying the object to its final key and then deleting the staged copy. Readers only list the final prefix, so a crashed run leaves an orphan in staging but never a half-written file where readers look. Alongside each file, write a manifest containing:
- row count
- minimum and maximum primary key and timestamp
- the schema snapshot
- a SHA-256 of the file
Before anything is dropped, re-read the Parquet file and compare its row count and a few aggregates against the source partition. Good aggregates are a sum of a numeric column and a count of distinct keys. If anything differs, stop.
Record each run in an archive_catalog table keyed by table and month, with a status column that moves through exported, verified, detached and dropped. A rerun reads this row first. If the month is already verified, the rerun skips the export. If the month is incomplete, the rerun overwrites the same deterministic keys. Either way, rerunning a month never duplicates or loses data.
The cutover runs in this order:
- Export the partition.
- Verify it against the source.
- Record the result in the catalog.
- Detach the partition, using
CONCURRENTLYwhere your version supports it. - Keep the detached table for a grace period, for example two weeks.
- Drop the table.
The drop step must refuse to run unless the catalog row says verified. That makes it impossible to skip verification.
Dropping the table does not free storage everywhere at once. Base backups and the WAL retained for PITR still hold the data, and you keep paying for it, until those backups age out.
Alert on these conditions:
- runs stuck in a non-terminal status
- orphaned staging objects
- verification mismatches
- S3 credentials or role sessions nearing expiry
This replaces quarterly manual cleanups, where someone hand-checks counts and hopes nothing was missed, with a job that pages you only when something goes wrong.
How do you query or restore archived Parquet data without Postgres?
Parquet stays queryable after the partition is dropped. Any engine that reads Parquet natively can scan the archived prefix in S3. Embedded analytical engines suit one-off investigations from a laptop or a bastion host. Distributed SQL engines suit recurring reports. Because each file stores its schema and column statistics, these engines can skip row groups and columns they don't need. That pruning is what keeps scans over years of history affordable.
You can also expose the archive inside Postgres through an external table or foreign data wrapper. Use only stable, widely known options. Check the extension's maintenance history and its compatibility with your Postgres major version before depending on it. An abandoned wrapper becomes a blocker at your next upgrade.
Be honest with your team about the cost. Archived rows can no longer be joined with ordinary SQL against live tables. A lookup that took milliseconds on an indexed partition now takes seconds or more, because it lists objects and reads files over the network.
Restoring a partition
When an incident or legal request needs the data back in Postgres, follow a rehearsed path:
- Create a staging table from the schema recorded in the archive manifest, not from today's table definition.
- Load the Parquet files into the staging table.
- Reconcile schema drift:
- Add columns introduced since the archive, using their defaults or NULL.
- Drop columns that were removed.
- Apply explicit casts for columns whose types changed.
- Check the row counts and checksums against the manifest.
- Add a CHECK constraint matching the partition bounds, then attach the table as a partition. With that constraint in place, Postgres can skip the validation scan during the attach.
Run this restore as a drill on a schedule. A restore path that has never been exercised has not been tested.
How do S3 lifecycle rules and security settings enforce data retention?
Lifecycle rules turn the written retention policy into something S3 enforces. A typical rule moves objects under the archive prefix to Standard-IA after 30 days, to Glacier Deep Archive later, and expires them at the retention limit. Use the same number the policy document states, so an auditor can compare the two directly.
| Storage class | Minimum billed duration | Retrieval |
|---|---|---|
| Standard-IA | 30 days | Immediate, per-GB fee |
| Glacier Instant Retrieval | 90 days | Immediate, higher per-GB fee |
| Glacier Flexible Retrieval | 90 days | Restore takes minutes to hours |
| Glacier Deep Archive | 180 days | Restore takes up to about 12 hours, or longer for bulk |
Lifecycle pitfalls
- Early deletion. Deleting an object before its minimum duration is billed as if it stayed the full period.
- Small files. Transitions are charged per object, and IA classes bill a minimum object size. Write one large Parquet file per partition, not thousands of small ones.
- Restore delays. Deep Archive is a poor fit for data you query ad hoc. A restore drill tells you how long a real restore will take.
Versioning and Object Lock
When versioning is on, a delete adds a delete marker and leaves the older versions in place. Add noncurrent-version expiration, or the expiration rule will not actually remove the data. Object Lock in compliance mode blocks every deletion until the retention date, including deletions requested under right-to-erasure law. Decide this with legal before you enable it.
Encryption and IAM
Use SSE-KMS so access to the archive also requires access to the key. Give the exporter role s3:PutObject on the archive prefix only. Do not grant s3:DeleteObject or permission to change bucket policy or lifecycle configuration. Then a compromised exporter role can add archive files but cannot delete existing data or shorten retention.
How much storage savings can you expect, and what are the alternatives?
Illustrative estimate (assumed numbers, not a benchmark)
Assumed inputs: cold heap H = 1,000 GB, index overhead I = 500 GB, database copies N = 3 (primary plus two replicas), retained physical backups B = 4, Parquet compression ratio C = 5. Placeholder prices per GB-month: database storage P_db = 0.10, backup storage P_bk = 0.05, object storage P_s3 = 0.02.
- Before = (H + I) × N × P_db + (H + I) × B × P_bk = 450 + 300 = 750/month
- After = (H / C) × P_s3 = 200 × 0.02 = 4/month
Only 100 of the 750 is heap on the primary. The rest comes from indexes, replicas, and backups, and Parquet carries none of those. Use your provider's prices and a compression ratio you have measured on a real exported partition. Ratios depend heavily on column types and cardinality.
Compare the result with the total cost of the pipeline: building it, testing restores, maintaining it as the schema changes, and staying on call for it. Avoid archiving when tables are small, when cold rows are still joined or updated regularly, or when you must erase individual rows on request. Rewriting immutable Parquet files to satisfy erasure requests is expensive and easy to get wrong.
| Alternative | Good fit when | Main drawback |
|---|---|---|
| Delete under retention policy | No one needs the old data | The data is gone permanently |
| Cheaper Postgres storage or tablespace | Data must stay queryable with SQL | You still pay for indexes, replicas, and backups |
pg_dump of detached partitions |
Restores are rare and done whole | Data cannot be queried without a restore |
| Logical replication to an analytics store | Analysts need fresh, joinable data | You run and pay for a second system |
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to archive postgres data to s3 parquet, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.