What is cold-row archiving to Parquet on S3, and who should use it?

Cold-row archiving moves rows your application no longer reads out of Postgres and into Parquet files on S3, partitioned by date (for example, one prefix per month of created_at). Once the export is verified, the rows are deleted from the database. The data stays queryable and restorable, but it no longer sits on provisioned database storage.

Teams adopt this pattern because rarely read data still costs money in several places. On RDS, every gigabyte counts toward allocated storage, snapshot size and backup retention. Large tables also make vacuum, index maintenance and pg_dump slower, and full scans and index bloat compete for IOPS with live traffic. Parquet is columnar and compresses well, so the same rows usually take far less space on S3, where storage is cheaper per gigabyte.

The pattern suits backend and platform engineers who own large, append-heavy tables such as orders, events or audit logs. In these tables, rows reach a final state and are then rarely touched again.

Define cold data with a cutoff predicate that combines age with terminal status. An example is orders where status IN ('delivered', 'cancelled') and updated_at < now() - interval '18 months'. Age alone can catch rows that are still open, such as disputes or pending refunds. Check how much you would reclaim with pg_total_relation_size('orders'), which includes indexes and TOAST, and run a count(*) against the predicate.

Before you export anything, confirm with the application owners that no hot-path query, background job or report reads rows that match the predicate. Check slow-query logs and pg_stat_statements if you have them, but treat a conversation with the code owners as the final check.

-- 1. Total footprint of the table, including indexes and TOAST.
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
       pg_size_pretty(pg_relation_size('orders'))       AS heap_only;

-- 2. How many rows match the cold-row predicate (age + terminal status).
SELECT count(*) AS cold_rows
FROM orders
WHERE status IN ('delivered', 'cancelled')
  AND updated_at < now() - interval '18 months';

-- 3. Cold rows per month of created_at: this is the shape of the
--    date-partitioned Parquet layout (one S3 prefix per month).
SELECT date_trunc('month', created_at)::date AS month,
       count(*)                              AS rows
FROM orders
WHERE status IN ('delivered', 'cancelled')
  AND updated_at < now() - interval '18 months'
GROUP BY 1
ORDER BY 1;

-- 4. Rows that are old but NOT terminal: an age-only cutoff would
--    have archived these (open disputes, pending refunds, etc.).
SELECT status, count(*)
FROM orders
WHERE updated_at < now() - interval '18 months'
  AND status NOT IN ('delivered', 'cancelled')
GROUP BY status;

-- 5. If pg_stat_statements is installed, list frequent queries on orders
--    to review with code owners for reads that could touch cold rows.
SELECT calls, left(query, 120) AS query
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
ORDER BY calls DESC
LIMIT 20;

Queries that measure the orders table's size, count and bucket cold rows by month using an age-plus-terminal-status predicate, surface old non-terminal rows an age-only cutoff would wrongly catch, and list frequent queries on the table to review with application owners.

Why doesn't deleting rows reduce RDS storage costs?

A DELETE does not return space to the operating system. Postgres marks each deleted row version as dead, and VACUUM later records that space as reusable for future inserts and updates in the same table. The data files keep their size. The one exception is when VACUUM finds completely empty pages at the end of a file and truncates them. Archiving old rows scattered through a busy table rarely produces that pattern.

RDS adds a second layer to the problem. You are billed for allocated storage, not for the bytes Postgres uses, and allocated storage generally cannot be reduced in place. Even when you shrink the files, the volume and the bill stay the same size.

Option Shrinks table files? Shrinks RDS allocation? Main cost
DELETE + VACUUM No, except trailing empty pages No Dead-tuple churn, WAL volume
Detach and drop old partitions Yes, immediately No Requires a partitioned table, ideally designed in advance
pg_repack Yes No Free space for a full copy of the table and its indexes, plus short locks at start and end
VACUUM FULL Yes No ACCESS EXCLUSIVE lock that blocks reads and writes for the whole rewrite
Migrate to a smaller instance volume Yes Yes Logical replication or dump/restore, then a cutover

Expect the savings to arrive in two stages. First, freed space stops storage growth, so you defer or avoid autoscaling increases. A lower bill comes only after you migrate to a smaller volume. If you track the project only against this month's invoice, it will look like it failed even when it worked.

How does an export-then-delete archive pipeline work?

The pipeline is a fixed sequence, and each step must finish completely before the next one starts:

  1. Select by cutoff. Freeze one cutoff timestamp per run, such as created_at < '2024-04-01'. If the boundary moves during the run, export and delete will disagree about which rows are in scope.
  2. Read in keyset-paginated batches. Use WHERE id > last_id ORDER BY id LIMIT n. OFFSET gets slower on every page, and concurrent writes can make it skip rows.
  3. Write Parquet, then upload to a staging key. S3 has no atomic rename. "Promoting" a file means copying it to the final key and then removing the staging object.
  4. Write a manifest. Record file keys, row counts, the min and max primary key, and checksums. Readers treat a partition as complete only when its manifest exists.
  5. Verify against the source. Compare the manifest's counts and key ranges with a fresh query on the same cutoff.
  6. Delete. Remove rows in bounded batches, using the verified key ranges.

Lay out the data with Hive-style keys, such as table=orders/year=2024/month=03/, so query engines can prune partitions. Aim for files in the hundreds of megabytes. Thousands of tiny files raise request costs and slow down scans. Compress with Snappy for speed or Zstd for a better ratio. Store a schema file next to the data so a future restore doesn't have to guess how types were mapped.

Failure windows open between the steps:

  • Crash after upload, before the manifest: orphaned staging files are left behind and need cleanup.
  • Crash after promotion, before verification: final files exist but nothing has checked them yet.
  • Crash mid-delete: the source has been partially pruned.

Each window is harmless only if a rerun can detect where the previous run stopped and resume from that point.

 +---------------------------------+     S3 final layout (Hive-style)
 | 1. Select: created_at < cutoff  |     table=orders/
 +----------------+----------------+       year=2024/month=03/
                  v                           part-0000.parquet
 +---------------------------------+           part-0001.parquet
 | 2. Keyset: WHERE id > last_id   |           _schema.json
 |    ORDER BY id LIMIT n          |           _manifest.json
 +----------------+----------------+     (100s of MB, Snappy/Zstd)
                  v
 +---------------------------------+
 | 3. Write Parquet -> _staging/key|
 +----------------+----------------+
                  v  <-- W1: crash leaves orphaned staging files
 +---------------------------------+
 | 3b. Promote: copy to final key, |
 |     then remove staging object  |
 +----------------+----------------+
                  v
 +---------------------------------+
 | 4. Manifest: keys, row counts,  |
 |    min/max id, checksums        |
 +----------------+----------------+
                  v  <-- W2: final files exist, not yet verified
 +---------------------------------+
 | 5. Verify vs fresh source query |
 +----------------+----------------+
                  v
 +---------------------------------+
 | 6. Delete by verified id ranges |  <-- W3: source partially pruned
 +---------------------------------+

The export-then-delete pipeline runs in a fixed order under one frozen cutoff, writes Parquet to a Hive-style S3 layout, and has failure windows W1 to W3 between steps.

How do you map Postgres types to Parquet without losing data?

Type mapping is the other place where an archive silently loses data. If you let pyarrow infer types from the first batch, a batch where every discount is null becomes a null-typed column, a numeric becomes a float, and the next batch fails or the files end up with different schemas. Declare the schema explicitly with pa.schema([...]) from the table's catalog definition, and fail the export if a batch doesn't conform.

Postgres type Parquet / Arrow type Pitfall
numeric(p,s) decimal128(p, s) Floats lose cents. Unconstrained numeric has no fixed scale, so pick one and reject values that don't fit.
timestamptz timestamp('us', tz='UTC') Postgres stores UTC. Keep the timezone flag so readers don't shift values.
timestamp timestamp('us'), no tz Don't label it UTC unless it really is. Use microsecond precision to match Postgres.
jsonb string (UTF-8 JSON text) Inferred structs break when keys vary between rows.
int[], text[] list<...> Multidimensional arrays don't map cleanly. Flatten them or serialise them to JSON.
enum string (dictionary-encoded) Enum labels can be renamed later. Store the label, not the internal ordinal.
uuid string or fixed_size_binary(16) Strings are the most portable. Binary is smaller but harder to read with other tools.

Schema drift is unavoidable over years of archives. Files written last year keep last year's columns, so readers need to cope with three cases:

  • Missing columns: fill them with null.
  • Renamed columns: apply a mapping.
  • Widened types: cast to the current schema at read time.

Write a schema version into each file's key-value metadata so readers know which mapping to apply.

import pyarrow as pa
import pyarrow.parquet as pq

SCHEMA_VERSION = b"3"
# Built from the orders table's catalog definition, never inferred from a batch.
SCHEMA = pa.schema([
    pa.field("id", pa.string(), nullable=False),           # uuid as text
    pa.field("total", pa.decimal128(12, 2)),                # numeric(12,2)
    pa.field("discount", pa.decimal128(12, 2)),             # stays decimal even if a batch is all null
    pa.field("created_at", pa.timestamp("us", tz="UTC")),  # timestamptz
    pa.field("ship_by", pa.timestamp("us")),                # timestamp without time zone
    pa.field("attrs", pa.string()),                         # jsonb as JSON text
    pa.field("tags", pa.list_(pa.string())),                # text[]
    pa.field("status", pa.dictionary(pa.int32(), pa.string())),  # enum label
])

def write_batch(rows, path):
    # Raises if a value doesn't fit the declared type, so the export fails loudly.
    table = pa.Table.from_pylist(rows, schema=SCHEMA)
    table = table.replace_schema_metadata({b"schema_version": SCHEMA_VERSION})
    pq.write_table(table, path)

# Per-version renames, old name -> current name.
RENAMES = {"2": {"amount": "total"}}

def read_conformed(path):
    table = pq.read_table(path)
    version = (table.schema.metadata or {}).get(b"schema_version", b"1").decode()
    mapping = RENAMES.get(version, {})
    table = table.rename_columns([mapping.get(n, n) for n in table.column_names])
    columns = []
    for field in SCHEMA:
        if field.name in table.column_names:
            # Widen older types, e.g. decimal128(10, 2) -> decimal128(12, 2).
            columns.append(table[field.name].cast(field.type))
        else:
            # Column added after this file was written: fill with nulls.
            columns.append(pa.chunked_array([pa.nulls(table.num_rows, field.type)]))
    return pa.Table.from_arrays(columns, schema=SCHEMA)

An explicit pyarrow schema for a Postgres orders table, a writer that rejects non-conforming batches and stamps a schema version into the file metadata, and a reader that renames, casts and null-fills older files to match the current schema.

How do you implement an idempotent, verifiable export and safe delete?

Export in keyset batches

Page through the table with WHERE id > :last_id ORDER BY id LIMIT :n, not OFFSET. Each batch is an index range scan with predictable cost, and concurrent inserts can't shift page boundaries. Write each batch with pyarrow or DuckDB's COPY ... TO. Name files deterministically, for example orders/month=2024-03/batch-000042.parquet, so a rerun overwrites the same key instead of creating duplicates.

Upload to a _staging/ prefix first. S3 has no atomic rename, so promote to the final prefix with a copy-then-delete only after verification passes. Readers never see half-written batches.

Verify before deleting

Record each file in a manifest with its row count, min and max primary key, and a checksum, such as a hash of the ordered primary keys. Re-read the promoted file from S3 and compare all of these values to the source over the same key range. Any mismatch blocks the delete for that range.

Delete gently

Delete by primary-key range in batches of a few thousand rows, one short transaction per batch. Short transactions keep lock duration low and let autovacuum keep up. Every deleted row generates WAL and leaves a dead tuple. Pause between batches when replica lag or IO wait rises. If the table is partitioned by time, DETACH PARTITION followed by DROP TABLE avoids per-row WAL and dead tuples entirely.

Exporting from a read replica spares primary IO, but replay lag can hide recent rows. Long-running queries on the replica may be cancelled by recovery conflicts, or, with hot_standby_feedback on, may bloat the primary. Always run verification counts against the primary.

import hashlib, time
import psycopg2
import pyarrow.parquet as pq
from pyarrow import fs

s3 = fs.S3FileSystem(region="us-east-1")

def summarize(ids):
    # Count, min/max PK and a SHA-256 over the ordered PKs: cheap to compare on both sides
    h = hashlib.sha256()
    for i in ids:
        h.update(f"{i}\n".encode())
    return (len(ids), ids[0] if ids else None, ids[-1] if ids else None, h.hexdigest())

def verify(conn, s3_key, manifest, lo, hi):
    # Re-read the promoted file from S3, not the local copy we uploaded
    ids = sorted(pq.read_table(s3_key, filesystem=s3, columns=["id"]).column("id").to_pylist())
    with conn, conn.cursor() as cur:  # conn must point at the PRIMARY, not a replica
        cur.execute("SELECT id FROM orders WHERE id BETWEEN %s AND %s ORDER BY id", (lo, hi))
        src = [r[0] for r in cur]
    expected = (manifest["rows"], manifest["min_id"], manifest["max_id"], manifest["sha256"])
    return summarize(ids) == summarize(src) == expected

def replica_lag_seconds(conn):
    with conn, conn.cursor() as cur:
        cur.execute("SELECT COALESCE(EXTRACT(EPOCH FROM MAX(replay_lag)), 0) FROM pg_stat_replication")
        return float(cur.fetchone()[0])

def delete_range(conn, lo, hi, step=5000, max_lag_s=5.0):
    start = lo
    while start <= hi:
        end = min(start + step - 1, hi)
        with conn, conn.cursor() as cur:  # one short transaction per batch; commits on exit
            cur.execute("DELETE FROM orders WHERE id BETWEEN %s AND %s", (start, end))
        start = end + 1
        while replica_lag_seconds(conn) > max_lag_s:  # let replicas and autovacuum catch up
            time.sleep(2)

# Usage: delete only if the gate passes for this exact key range
# if verify(conn, "archive-bucket/orders/month=2024-03/batch-000042.parquet", m, m["min_id"], m["max_id"]):
#     delete_range(conn, m["min_id"], m["max_id"])

A verification gate compares row count, min/max primary key and an ordered-PK checksum across the S3 Parquet file, the manifest and the primary. If they all match, the script deletes that primary-key range in short batched transactions and pauses whenever replica lag rises.

How do you query and restore archived Parquet data?

For ad hoc questions, DuckDB can read the archive in place. With the httpfs extension loaded and S3 credentials configured, read_parquet accepts a glob over the archive prefix. Enabling hive_partitioning exposes path segments such as year=2024/month=03 as columns. A filter on those columns is resolved against the object keys before any file is opened, so a query for one month lists and reads only that month's objects. Filters on ordinary columns still benefit from Parquet row-group statistics, but they cannot skip files by path. This makes the partition key you chose at export time the main lever for query cost and latency.

Restores should follow a written, rehearsed path:

  1. Load one partition into a staging table that has the same schema as the source.
  2. Check the row count and checksum against the manifest written during export.
  3. Promote only the rows you need with INSERT ... SELECT.

DuckDB's postgres extension can write directly into an attached database. Exporting to CSV and loading it with COPY works as well. Never load directly into the live table, because primary keys may have been reused and triggers would fire on historical rows.

Archived rows lose a lot. They have no foreign keys, no constraints, and no transactional consistency with live data. A join across hot and cold rows becomes a two-system query that you reconcile by hand.

Plan for the rows you assumed were cold, such as refunds, disputes and support lookups. Any of these can turn a slow restore into an incident with a customer waiting. A runbook that has already been tested turns that incident into a routine task instead of an afternoon of improvisation.

What does archiving cost and save, and when should you avoid it?

Start with arithmetic before you build anything.

Illustrative estimate (assumed numbers, not a benchmark). Assume 2,000 GB of cold rows, a 5:1 Parquet compression ratio, $0.115/GB-month for RDS storage, $0.02/GB-month for backup beyond the free allowance, and $0.023/GB-month for S3 Standard.

Monthly saving = cold_GB × (rds_price + backup_price) − (cold_GB ÷ ratio) × s3_price − request_costs

That gives 2,000 × 0.135 − 400 × 0.023 ≈ $270 − $9 = $261/month, before request costs. The saving only appears if you actually reclaim or stop growing allocated storage. If building and running the pipeline costs two engineer-weeks, payback takes many months. Run your own numbers.

Lifecycle rules can move objects into infrequent-access or archive classes, which lowers storage cost further. Those classes add per-GB retrieval fees, minimum storage durations, and minimum billable object sizes. Archive tiers can also take hours to restore. Thousands of small files multiply request charges, so write fewer, larger files before you transition anything.

Avoid this pattern in three cases:

  • Erasure or retention rules. Deleting one customer's data means rewriting whole Parquet files.
  • Small tables. The saving does not cover the build effort.
  • Data read back often. Retrieval and query costs cancel the saving.
Alternative Fits when Gap
Drop partitions, no archive Nobody needs the old data Data is gone permanently
pg_repack alone Bloat is the problem, not volume Live data keeps growing
Keep in Postgres, cheaper storage Cold data still gets queried Still pays database prices
One-off dump You archive once Every repeat is manual work

A reusable pipeline pays off when archiving recurs, because it replaces that repeated manual work.

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, 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.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

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.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • 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.