Before the Table Hits 100M Rows: Data Archiving & Hot-Cold Separation for Vibe Projects

Before we start, let's draw the boundaries — three guides on this site each own their territory, so you don't walk into the wrong room:

• vs Database Backup & Disaster Recovery: that guide is about "how to recover when data is lost" — RPO/RTO, backup strategy, disaster drills. This guide is about day-to-day governance — how to split tables that get too big, how to separate hot and cold data. Backups save your life; archiving puts your tables on a diet.
• vs Slow Queries & Index Optimization: that guide is about "making queries fast" — indexes, execution plans. This guide is about "keeping tables from exploding" — archiving strategy. After archiving, indexes naturally shrink and queries naturally speed up, but that's a side effect, not the goal. Don't expect another index to fix "this table has 200 million log rows."
• vs Schema Migration: that guide is about "structural change" — adding columns, changing types, zero-downtime migration. This guide is about "data lifecycle" — where old data goes to retire. Migration governs the shape of tables; archiving governs where the data inside them retires to.

In short: backups handle "lost", indexes handle "slow", migrations handle "change", archiving handles "fat". This guide is only about "fat".

How vibe projects' tables explode

There's a classic way for vibe coding projects to die: three months after launch, everything looks fine. Then one morning you get three surprises — your Supabase/Neon bill doubled, pg_dump has been running for 4 hours, and a simple "orders from the last 7 days" endpoint took the database down. You open the database: request_logs has 80 million rows, events has 200 million, audit_logs hasn't deleted a single row since day one.

It's not your fault — it's structural to vibe coding: when AI writes your code, it never asks "how big will this table be in a year?" Log tables, event tables, audit tables, message tables — append-only tables are standard equipment in every vibe project. And archiving is exactly the thing AI never reminds you to do: it belongs to no feature, produces no user-visible value, and only punishes you when you skip it.

One correction first: archiving is not deleting. Deleting is "throwing away"; archiving is "moving to cheaper storage where it's still queryable." A healthy archiving system answers three questions: what counts as "old" (signal metrics), where old data goes (strategy choice), and how the move happens without incidents (job design). This guide walks through all three using PostgreSQL native partitioning + pg_partman, with code you can actually run.

1. When to archive: four signals, not gut feelings

"The table feels big" is not a reason to act. "Metrics are over the line" is. If two of these four signals fire, schedule the work.

Signal 1: Table size — a single table exceeds instance memory, or 60%+ of the database

The official PostgreSQL partitioning docs give a rule of thumb: partitioning pays off when a table grows larger than the database server's RAM. For vibe projects you can be more aggressive: a single table over 50GB, or one table holding 60%+ of the whole database, is a red light. Run this to find your biggest tables:

SELECT tablename,
       pg_size_pretty(pg_total_relation_size('public.' || tablename)) AS total_size,
       pg_size_pretty(pg_relation_size('public.' || tablename))       AS heap_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size('public.' || tablename) DESC
LIMIT 10;

Watch the gap between total_size and heap_size — the difference is indexes. An 80-million-row log table often carries indexes 1.5x the heap size. That's why "add an index" can't cure "fat table": the index itself is part of the fat.

Signal 2: Query decay — the same query's p95 keeps climbing month over month

The first clinical symptom of table obesity isn't errors, it's "getting slower." Use pg_stat_statements to track mean_exec_time for the same query class. Three straight months of increase, plus a dropping shared_blks_hit ratio, means hot data no longer fits in memory:

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round((100.0 * shared_blks_hit
              / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS hit_pct
FROM pg_stat_statements
WHERE query LIKE '%request_logs%'
ORDER BY mean_exec_time DESC
LIMIT 5;

You'll need CREATE EXTENSION pg_stat_statements (available on Supabase/RDS/Neon). The difference from the slow-query guide: that guide fixes slowness caused by bad query shapes or missing indexes; this fixes slowness caused by sheer data volume — no matter how pretty the plan, scanning 200 million rows is scanning 200 million rows.

Signal 3: Backup duration — longer than your maintenance window

Time one full pg_dump. If it exceeds your maintenance window (say, over an hour), or your restore-drill RTO is already blown, archiving is the fastest diet pill: move 2 years of cold data out and backup size drops 70%, with backup and restore times dropping proportionally. For backup strategy itself, see the backup & disaster recovery guide. The point here: archiving is the fastest way to make backups cheap again.

Signal 4: Business signal — the hot/cold line is measured, not guessed

"Data older than 90 days is cold" — where does the 90 come from? Measure it. Look at the data distribution, then look at the time filters in your query logs:

-- Monthly data distribution: how much is actually cold
SELECT date_trunc('month', created_at) AS m, count(*)
FROM request_logs
GROUP BY 1 ORDER BY 1 DESC
LIMIT 12;

If data older than 90 days is 85% of the table, while 99% of your queries carry created_at > now() - interval '90 days', then the line is 90 days — backed by data, no arguing. Two of four signals firing means act; waiting for the fifth signal (disk full) turns archiving into firefighting.

2. Three strategies compared: no silver bullet, only trade-offs

Three mainstream approaches, ordered light to heavy for vibe-project scale:

• Strategy A: PostgreSQL native partitioning + pg_partman — RANGE partition by time; pg_partman auto-creates future partitions and detaches old ones per retention policy. Nearly transparent to the app: logically still one table, SELECT * FROM request_logs WHERE created_at > ... works as before, partition pruning skips cold partitions automatically.
• Strategy B: Archive tables + UNION view — periodically INSERT INTO archive.request_logs_2025_01 SELECT ... then DELETE FROM request_logs; full-range queries go through a UNION ALL view. No extensions needed. The most old-school approach — every step is SQL you wrote yourself, easy to debug.
• Strategy C: Cold export to Parquet — COPY to Parquet files on S3/R2; only hot data stays in the database. Cheapest storage (object storage is an order of magnitude cheaper than managed databases), but lookups go through DuckDB/Athena or re-ingestion — the longest pipeline.

Figure 1: Qualitative comparison of the three archiving strategies (1–10, higher is better; cost dimensions inverted). Scores are for discussion, not benchmarks.

Dimension	A Native partitioning + pg_partman	B Archive tables + view	C Parquet cold export
Query pattern	One logical table, automatic partition pruning	App routes by time, or UNION ALL view	Hot queries hit the DB, cold queries hit object storage/DuckDB
Cold lookup	ATTACH back, minutes	Already in the view, seconds	Download & re-ingest, hours
Storage cost	Still in Postgres, no savings	Still in Postgres, no savings	S3/R2, an order of magnitude cheaper
Implementation cost	Medium: extension + table migration	Low: plain SQL cron job	Medium-high: export + lookup pipelines
Detach efficiency	DETACH in milliseconds, zero app impact	INSERT+DELETE with locks and VACUUM cost	Fast COPY export, but long pipeline
Best for	The vast majority of vibe projects	Transition on hosts where you can't install extensions	GBs of logs per day, compliance allows off-DB storage

My recommendation is blunt: 90% of vibe projects should pick A. B is a transition for minimal managed hosts where extensions aren't available; C is for heavy hitters producing GBs of logs daily, or when the database bill already hurts enough to move data off. Note A and C aren't mutually exclusive — the mature setup is A+C: partitioned tables for online hot/cold separation (warm data), then export detached partitions to Parquet in object storage (cold data). The rest of this guide implements exactly that combination.

3. Partition key selection & migration in practice

Step one of strategy A isn't installing the extension — it's picking the partition key. Get it wrong and everything after is wasted.

3.1 Three rules for partition keys

A good archiving partition key must satisfy all three:

1. Every query filters on it — partition pruning only kicks in for queries whose WHERE clause mentions the partition key. If your queries never filter by time, partitioning buys nothing.
2. Monotonically increasing — new data always lands in the newest partition; once an old partition is full it never changes again, so "cutting time slices" stays clean.
3. Clean boundaries — archiving is "moving away a time range of data," so partition boundaries must align with archiving boundaries (monthly archiving → monthly partitions).

For request_logs, created_at hits all three. The anti-example: partitioning by user_id — queries do filter by it, but archiving evicts by time, and a user_id partition mixes new and old data so you can never detach it. Remember: the archiving partition key is almost always a time column, because "archiving = evicting by time" by definition.

After the key comes the granularity: daily, weekly, or monthly? Look at two numbers — data per partition, and total partition count. Rule of thumb: 1GB–10GB per partition is the sweet spot. Too small and the partition count explodes (the planner must walk every partition constraint); too big and each detach/archive moves too much at once. For most vibe projects, start monthly: a few GB per month, one partition per archive unit — detach, export, verify, and destroy all operate on "one month," which keeps the mental model simple. Go weekly only when a single month exceeds ~50GB. Don't start daily — three years is 1,095 partitions, painful to even \d+, let alone plan against.

3.2 Installing pg_partman

pg_partman is the partitioning manager extension on PGXN (pgxn.org/dist/pg_partman); version 5.x supports only Postgres native declarative partitioning. Its value in one sentence: automated partition maintenance — pre-creating future partitions and detaching/dropping old ones per retention policy, so you never hand-write DDL in cron.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

Two notes for managed environments: RDS (12.5+), Supabase, Neon and most managed Postgres providers ship pg_partman — enable it from the console or SQL. Its built-in background worker (BGW) needs shared_preload_libraries plus an instance restart, which managed hosts usually don't allow — in that case schedule partman.run_maintenance() via pg_cron; same effect.

3.3 Migrating an existing big table to a partitioned table

Postgres has a hard limitation: a regular table can't be converted into a partitioned table in place — it must be rebuilt. pg_partman turns this into a standard routine: rename the old table, create the partitioned parent under the original name, move the data, drop the old table:

-- 1. Rename the old table (brief write pause, or do it off-peak)
ALTER TABLE request_logs RENAME TO request_logs_legacy;

-- 2. Create the partitioned parent under the original name:
--    RANGE on created_at, monthly, pre-make 2 future partitions
SELECT partman.create_parent(
    p_parent_table => 'public.request_logs',
    p_control      => 'created_at',
    p_type         => 'native',
    p_interval     => 'monthly',
    p_premake      => 2
);

-- 3. Move data in batches: rows are MOVED out of the old table (not copied)
CALL partman.partition_data_proc(
    'public.request_logs',
    p_interval     := '1 month',
    p_batch        := 200,
    p_source_table := 'public.request_logs_legacy'
);

-- 4. Verify: the old table should be empty, the new one should hold all rows
SELECT count(*) FROM request_logs_legacy;  -- expect 0
SELECT count(*) FROM request_logs;         -- expect the pre-migration count

-- 5. Retire the old table once verified
DROP TABLE request_logs_legacy;

Step 3's partition_data_proc is pg_partman's purpose-built migration procedure: it slices by p_interval and commits in p_batch chunks, avoiding one giant transaction that locks the database. After the move, run VACUUM ANALYZE request_logs — otherwise the planner's row estimates for the new partitions are fiction.

3.4 Index handling: where vibe projects crash most

Partitioned-table index rules differ from regular tables, and AI-generated migration code crashes here most often:

First, indexes created on the parent automatically apply to all partitions (existing and future):

CREATE INDEX ON request_logs (user_id, created_at);
CREATE INDEX ON request_logs (created_at, status_code);

Second, primary keys / unique constraints must include the partition key. This is a hard Postgres limitation: uniqueness can only be enforced within a single partition, never across partitions. So this fails outright on a partitioned table:

-- Wrong: AI loves writing this; it errors on partitioned tables
-- ALTER TABLE request_logs ADD PRIMARY KEY (id);
-- Right: the primary key must include the partition key
ALTER TABLE request_logs ADD PRIMARY KEY (id, created_at);

If your business truly needs "globally unique id": keep uniqueness at the application layer (uuids make collisions negligible) and declare the database primary key as (id, created_at). Don't fight the database — it's written in the official docs.

Third, the lock-free way to index a big table. CREATE INDEX on a partitioned parent can't use CONCURRENTLY (it locks). The official workaround: CREATE INDEX ... ON ONLY the parent (shell only, marked invalid), then CREATE INDEX CONCURRENTLY on each partition separately, then ALTER INDEX ... ATTACH PARTITION to stitch them together. Remember this option exists for when your table gets there.

Fourth, confirm partition pruning is on. SHOW enable_partition_pruning; defaults to on — don't turn it off. Verify with EXPLAIN SELECT * FROM request_logs WHERE created_at > now() - interval '7 days'; and check the plan only scans recent partitions.

4. Archive job design: idempotent, re-entrant, observable

The partitioned table is built and data slices itself monthly. But "where do detached old partitions go" is a decision Postgres won't make for you — that's the archive job's work. The single most important sentence in this design:

pg_partman only detaches per retention policy; the archive job only does "move → verify → destroy." Each minds its own business, connected by "detached standalone tables." Never let both do the same work.

-- pg_partman retention: auto-detach partitions older than 6 months,
-- but keep them as standalone tables (don't delete)
UPDATE partman.part_config
SET retention = '6 months',
    retention_keep_table = true,   -- key: detach only, never delete
    retention_keep_index = true
WHERE parent_table = 'public.request_logs';

After this, every maintenance run (BGW or partman.run_maintenance()) detaches overdue partitions (e.g. request_logs_p2025_03_01) into standalone tables waiting for the archive job to claim them. Application queries are unaffected — they only touch the last 90 days anyway.

4.1 pg_cron or application-level job?

	pg_cron (in-database scheduler)	App-level job (BullMQ / node-cron)
Pros	Closest to the data, pure SQL; no app process dependency	Can call S3 SDKs, send alerts, write logs — far more expressive
Cons	Can't do "out-of-database" work like object-storage uploads or HTTP alerts	Stops when the app stops; needs its own HA story
Best for	Pure in-DB actions: detach, view rebuilds, partition drops	The full archive pipeline: Parquet export → S3 upload → verify → alert

The full pipeline in this guide (Parquet export + object-storage upload) must leave the database, so use an app-level job for the main pipeline and keep pg_cron for two small things: calling partman.run_maintenance() (if BGW is unavailable) and scheduled VACUUM ANALYZE. Don't stuff S3 uploads into database functions — that's digging your own grave.

4.2 The archive job state machine

Archive jobs fear two things: crashing halfway with no record of which partitions were moved, and two workers moving the same partition twice. The fix is a state machine + advisory locks:

Figure 2: Archive job state machine. Every transition writes the DB before acting; after a crash the worker resumes from status; only one worker per partition at a time.

State lives in an archive_jobs table — one row per partition per archival:

CREATE TABLE archive_jobs (
    job_id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    partition_name text NOT NULL UNIQUE,
    status         text NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','detaching','exporting','verifying',
                          'retained','destroyed','failed')),
    row_count      bigint,
    sha256         text,
    s3_key         text,
    attempt        int NOT NULL DEFAULT 0,
    last_error     text,
    created_at     timestamptz NOT NULL DEFAULT now(),
    updated_at     timestamptz NOT NULL DEFAULT now()
);

The idempotency trio — all three required:

1. State first: every transition is "UPDATE archive_jobs SET status=... first, then act." After a crash, the worker resumes from status: finished steps are skipped, unfinished ones retried.
2. Partition-level exclusive lock: start with SELECT pg_advisory_xact_lock(hashtext('request_logs_p2025_03_01')) — only one worker holds it per partition; the second waits or skips. Transaction-scoped locks release automatically, so a crashed worker can't leave a deadlock.
3. Handoff checks: before detaching, check whether the partition is still attached (pg_partman may have detached it already); before exporting, check whether the Parquet file already exists with a matching sha256 — if yes, skip straight to registration. Re-runs never redo work.

Worker skeleton (TypeScript + BullMQ style — the state machine is the point, implement directly):

async function archivePartition(partition: string) {
  await db.query(`SELECT pg_advisory_xact_lock(hashtext($1))`, [partition]);
  const job = await upsertJob(partition, 'detaching'); // state first

  try {
    // 1. detach (skip if pg_partman already detached it)
    await ensureDetached(partition);
    await setStatus(partition, 'exporting');

    // 2. COPY export -> Parquet -> S3 upload; returns row count & sha256
    const { rows, sha256, s3Key } = await exportToParquet(partition);
    await setStatus(partition, 'verifying', { rows, sha256, s3Key });

    // 3. verify: partition row count == Parquet row count, sha256 matches
    await verifyExport(partition, rows, sha256);
    await setStatus(partition, 'retained');

    // 4. destroy only after the compliance retention period (section 8)
    // await destroyPartition(partition); // -> 'destroyed'
  } catch (err) {
    await setStatus(partition, 'failed', { error: String(err) });
    await alert(`Archive failed: ${partition}`, err); // alerting is mandatory
    throw err;
  }
}

Note the alert in catch: archive jobs are background work — failures don't 500, nobody screams, they just lie quietly in failed status until three months later you discover the disk is full again. An archive job without alerting is no archive job at all.

5. From DETACH to Parquet: one complete archival in SQL

Translating the state machine's key steps into SQL. Assume pg_partman has detached the March 2025 partition into the standalone table request_logs_p2025_03_01:

-- 0. Confirm it's really detached (don't touch it if it's still a partition)
SELECT inhparent::regclass AS parent
FROM pg_inherits WHERE inhrelid = 'request_logs_p2025_03_01'::regclass;
-- expect: 0 rows (it's a standalone table now)

-- 1. Export: COPY to CSV (on managed Postgres without server file access,
--    use COPY TO STDOUT through the application layer)
COPY (SELECT * FROM request_logs_p2025_03_01)
  TO '/archive/staging/request_logs_2025_03.csv' WITH (FORMAT csv, HEADER);

-- 2. Convert to Parquet and upload (app layer; DuckDB example; use an SDK for S3/R2 in prod)
-- import duckdb
-- duckdb.sql("""COPY (SELECT * FROM read_csv('/archive/staging/request_logs_2025_03.csv', header=true))
--               TO '/archive/staging/request_logs_2025_03.parquet' (FORMAT parquet)""")
-- s3_key = upload('/archive/staging/request_logs_2025_03.parquet',
--                 's3://myapp-archive/request_logs/2025_03.parquet')

-- 3. Verify: row count in the DB == row count in Parquet, sha256 matches (compared in app layer)
SELECT count(*) FROM request_logs_p2025_03_01;

-- 4. Register the archive metadata (only after verification passes)
UPDATE archive_jobs
SET status = 'retained', row_count = 48210391,
    sha256 = 'e3b0c44...', s3_key = 's3://myapp-archive/request_logs/2025_03.parquet',
    updated_at = now()
WHERE partition_name = 'request_logs_p2025_03_01';

-- 5. After the compliance retention period (section 8), destroy the partition to free space
-- DROP TABLE request_logs_p2025_03_01;
-- UPDATE archive_jobs SET status = 'destroyed' WHERE partition_name = 'request_logs_p2025_03_01';

Practical details:

• Detached tables keep their indexes (because retention_keep_index = true). Drop the ones you don't need before exporting — it speeds up the export, and the table gets destroyed afterward anyway.
• One Parquet file per month, not per day — 100k tiny files in object storage are far harder to manage than 36 big ones, and column pruning works better on larger files.
• sha256 is computed on the Parquet file, not the CSV intermediate. The verification chain: DB row count == Parquet row count, and Parquet sha256 == re-downloaded sha256 after upload. Delete intermediates once verified; don't let them hog the staging disk.
• If a month is too small to justify S3 (a few MB), just move the detached table into the archive schema and leave it. Don't force object storage for the sake of "architectural elegance."

6. Post-archive query routing: hot queries hit partitions, cold queries hit archives

After archiving, queries must be routed. One rule: online endpoints only ever query hot data; cold queries go through a dedicated slow lane.

6.1 Application-level routing by time (recommended)

// Keep HOT_DAYS in sync with retention_policies.hot_days — in config, not hardcoded
const HOT_DAYS = 90;

function resolveLogsSource(from: Date, to: Date) {
  const cutoff = new Date(Date.now() - HOT_DAYS * 864e5);
  if (to < cutoff) return 'archive.request_logs_cold'; // fully cold: archives only
  if (from < cutoff) return 'both';                    // straddles the line: merge hot + archive
  return 'public.request_logs';                        // fully hot: partitioned table, pruning applies
}

"Straddling" queries (a range crossing the hot/cold line) are common in admin/audit UIs — implement them as two queries merged in the application. Don't build a giant view to save one round trip; hot and cold differ by two orders of magnitude, and the merge logic is most controllable in app code.

6.2 UNION ALL view (fallback for admin/audit)

CREATE SCHEMA IF NOT EXISTS archive;

CREATE VIEW archive.request_logs_all AS
SELECT * FROM public.request_logs
UNION ALL
SELECT * FROM archive.request_logs_p2025_02_01
UNION ALL
SELECT * FROM archive.request_logs_p2025_01_01;

Fair warning: plain views get no partition pruning — querying this view scans every branch. So it's only for admin panels and audit exports ("already slow, runs a few times a day"), never for online endpoints. Update the view definition whenever an archive partition is added or destroyed — automate it in the job's retained/destroyed transitions.

6.3 ORM mapping notes

Prisma/Drizzle users, two things: the partitioned parent looks like an ordinary table to the ORM — reads and writes work with zero changes; tables under the archive schema get their own read-only models, and writes always go through the hot-table model. The dumbest bug I've seen: an archive view mapped as a writable model, then an UPDATE at 3 AM — views aren't writable, and on-call gets woken up.

7. Lookup & thaw: archived data can come back

"Can we still query archived data?" — the only question your boss cares about. Yes, but in three tiers, each an order of magnitude more expensive. Write the SLAs down so nobody assumes "archived = instant":

Tier	Where the data lives	Lookup cost	Typical use
Hot (0–90 days)	Online partitions	Milliseconds, normal queries	Daily business, user self-service
Warm (detached, not destroyed)	Standalone tables in archive schema	Minutes: ATTACH back into the partitioned table	Complaint investigation, audit sampling
Cold (exported Parquet)	S3/R2 object storage	Hours: download → create table → re-ingest → ATTACH	Legal discovery, historical analysis

7.1 Warm recovery: ATTACH it back

-- Re-attach the archived partition; queries see it immediately
ALTER TABLE request_logs ATTACH PARTITION archive.request_logs_p2025_02_01
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

One performance detail: ATTACH PARTITION scans the whole table to verify rows fit the bounds, holding an ACCESS EXCLUSIVE lock — expensive on tens of millions of rows. The workaround: add a CHECK constraint exactly matching the bounds first, and Postgres skips the validation scan:

ALTER TABLE archive.request_logs_p2025_02_01 ADD CONSTRAINT chk_2025_02
    CHECK (created_at >= DATE '2025-02-01' AND created_at < DATE '2025-03-01');
-- then ATTACH; the validation scan is skipped
ALTER TABLE request_logs ATTACH PARTITION archive.request_logs_p2025_02_01
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

After the lookup, DETACH it back — warm tables are on loan, not transferred. Don't let them squat in the hot table.

7.2 Cold recovery: re-ingesting from Parquet

-- 1. Download the Parquet from S3
-- aws s3 cp s3://myapp-archive/request_logs/2025_01.parquet /archive/restore/
-- 2. Create an empty table (LIKE the parent is the laziest correct way)
CREATE TABLE archive.request_logs_p2025_01_01 (LIKE public.request_logs INCLUDING ALL);
-- 3. Load the data (DuckDB or COPY, pick one)
-- 4. Verify row counts, then ATTACH (same as 7.1, CHECK constraint skips the scan)

Cold recovery might happen once a year, but drill it at least once a year. A Parquet file sitting in S3 for three years can surprise you — a pyarrow upgrade that can't read it anymore, or a lifecycle policy that deleted it. Discovering that during a real incident is worse than losing the data. The drill is simple: pick a random month, download, re-ingest, match row counts, clean up.

8. Archiving, backups & compliance retention: how the three relate

Archiving, backups, and compliance retention are three different things that vibe projects constantly conflate. Untangle them:

• Archiving: moving cold data out of the online database to keep it lean and fast. That's this guide.
• Backups: recovering when data is lost — the "save your life" part. Covered in the backup & disaster recovery guide.
• Compliance retention: how long the law says you must keep certain data (e.g. China's classified-protection 2.0 requires ≥ 6 months of logs). The "don't break the law" part.

8.1 Archiving makes backups cheap again

Archiving is pure upside for backups: the online DB shrinks from 2TB to 300GB, pg_dump drops from 4 hours to 20 minutes, and restore-drill RTO drops proportionally. Exported Parquet files are immutable — write once, verify once, then only spot-check integrity; no daily incrementals needed. The backup strategy goes from "daily full 2TB" to "daily full 300GB + a few new Parquet files a month." Bill and duration both drop.

8.2 Retention has three layers — don't merge them into one

Layer	What it governs	Implementation in this guide	Example
Online retention (hot)	How long data stays queryable online	HOT_DAYS in app routing	90 days
Partition retention (warm)	How long detached tables stay in the DB	pg_partman's retention	6 months
Compliance retention (cold)	How long the law requires keeping it	Parquet lifecycle on S3 + the policy table below	2 years

Who decides retention periods? Legal and product decide; engineering implements. Write the decisions into a table, not into someone's head:

CREATE TABLE retention_policies (
    data_type   text PRIMARY KEY,  -- 'request_logs'
    hot_days    int NOT NULL,      -- days retained online
    warm_months int NOT NULL,      -- months detached tables stay in the DB
    cold_years  int NOT NULL,      -- years Parquet stays in object storage
    legal_basis text NOT NULL      -- basis: compliance regime / business need
);

INSERT INTO retention_policies VALUES
    ('request_logs', 90, 6, 2, 'Log retention >= 6 months per local regulation; GDPR data minimization');

The job's "destroy" step must read this table: no deleting Parquet before cold_years, no DROP on detached tables before warm_months. "Can we delete this" becomes a table lookup, not an ops mood ring.

8.3 Archiving is not a GDPR get-out-of-jail card

The easiest compliance trap: a user exercises their deletion right (GDPR Art. 17), you delete from the online DB and call it done — while three years of Parquet in S3 still hold their user_id and behavior logs. Archived data is equally in scope for deletion obligations.

The practice: on a deletion request, delete by user_id from the hot table and from warm (detached, not yet destroyed) tables; for cold Parquet, either rewrite the file (read, filter the user out, write back, update sha256) when small, or record a tombstone and filter on the next archival/compaction when large. Either way, log it in archive_jobs so you can prove it during an audit. Don't wait for legal to ask "is this user still in your archives" with no answer ready.

9. Launch checklist & common pitfalls

Check these off before going live:

• [ ] Two of the four signals in section 1 are firing — don't archive for archiving's sake
• [ ] Partition key = in every query + monotonically increasing + time column — all three
• [ ] Primary/unique constraints include the partition key ((id, created_at), not (id))
• [ ] enable_partition_pruning = on, verified with EXPLAIN
• [ ] pg_partman retention_keep_table = true — it only detaches; deletion belongs to the archive job
• [ ] archive_jobs state machine + advisory locks + failure alerts — all three before launch
• [ ] Full dry run on staging first: detach → export → verify → destroy → look up
• [ ] Cold-recovery drill on the annual calendar, at least once a year

Top 5 pitfalls:

1. Partition count explosion: daily partitions × 3 years retention = 1,095 partitions; the planner walks every partition constraint and plan time suffers. Start monthly; refine only when needed.
2. The silently growing default partition: rows with NULL keys or out-of-range values land in the default partition. Check its row count regularly and sweep strays into correct partitions with partition_data_proc — otherwise the default partition becomes the next exploding table.
3. DETACH/ATTACH locks: DETACH itself is millisecond-fast but must acquire its lock; a long-running transaction on the table makes DETACH queue. Always run the archive job off-peak, with a lock_timeout — if the lock isn't acquired, try next round.
4. The archive job and pg_partman stepping on each other: hard-code the division of labor — partman only detaches, the job only moves. If both can delete tables, one day a table gets deleted twice (one errors, one succeeds) — or the one that should be deleted isn't.
5. Forgetting the ORM: the partitioned parent is transparent to ORMs, but archive-schema tables and the UNION view need separate mappings. Writes always go through the hot-table model; archive models are read-only.

Closing

Archiving is one of the rites of passage from "toy" to "product" for a vibe project. Tables will get fat, queries will get slow, bills will grow — the only difference is whether you proactively put the database on a fitness plan (partitioning + archive jobs + retention policies) or wait for the heart attack (disk full, DB down, backups that never finish) and then resuscitate.

Start with the smallest closed loop: tonight, run the table-size query from section 1 and find your biggest append-only table; this weekend, give it monthly partitions and pg_partman; next month, let the first detached partition export itself to Parquet automatically. Three months from now, your backup duration, query p95, and cloud bill will tell you it was worth it. Data governance has no silver bullet — but "partition monthly, archive on schedule, retain by policy" will carry a vibe project a long, long way.