Taming a Growing Database — 6-Month Retention with CSV Archives on a Media Server

The Problem

One table alone held roughly 94.5 million rows at around 35 GB, climbing by about 3.5 GB every month — growing every single day and never shrinking. Queries that used to be instant started crawling, nightly backups ballooned, and disk usage on the database server crept toward the danger zone. But I couldn't just delete the old rows — we needed to retain them for auditing.

The realisation that fixed everything: almost every query only ever touched recent data. Reports, dashboards, the app itself — all of it lived inside the last few months. The years of history behind that were dead weight sitting on expensive primary storage.

So I split the data into two tiers:

• Hot — the last 6 months, in PostgreSQL, indexed and fast.
• Cold — everything older, exported as plain CSV onto a cheap, roomy media server, still retained but out of the database's way.

The Architecture

The trick was getting cold data off the app server without the app or the export script needing to know anything about networking. I used a Samba share plus a symlink so the script just writes to a local-looking folder — and the bytes physically land on the media server.

Component	Role
App server	Runs the production app + PostgreSQL, and the daily cron
Media server	Cheap bulk storage for the cold CSV archives
Samba share	Exposes the media server's archive folder over the LAN
CIFS mount (/mnt/archive)	The app server mounts that share
Symlink (storage/archives → /mnt/archive)	The script writes "locally"; bytes land on the media server
Cron + shell script	Runs daily: export, verify, then prune

1. Share the folder from the media server

On the media server, in /etc/samba/smb.conf:

[archive]
   path = /srv/archive
   browseable = yes
   writable = yes
   valid users = appserver
   create mask = 0664
   directory mask = 0775

2. Mount it on the app server

A credentials file at /etc/samba/archive.cred (chmod 600):

username=appserver
password=********

And a line in /etc/fstab so it survives reboots:

//192.168.1.50/archive  /mnt/archive  cifs  credentials=/etc/samba/archive.cred,uid=postgres,gid=postgres,iocharset=utf8,vers=3.0  0  0

3. Symlink it into the app's storage

ln -s /mnt/archive /var/www/app/storage/archives

Now anything written to storage/archives/ quietly travels across the network onto the media server.

The Daily Script

I set up a crontab on the app server that runs a shell script once a day. The script finds every day older than the cutoff, writes one CSV per date, and only then prunes those rows:

#!/usr/bin/env bash
set -euo pipefail

ARCHIVE_DIR="/var/www/app/storage/archives"   # symlink → media server via Samba
DB="appdb"
DB_USER="app"
TABLE="events"
DATE_COL="created_at"
CUTOFF="6 months"

# Safety: never run if the media share isn't actually mounted —
# otherwise CSVs land on the local disk and we'd prune data we never archived.
if ! mountpoint -q /mnt/archive; then
    echo "FATAL: archive share not mounted, aborting" >&2
    exit 1
fi

# Every distinct day older than the cutoff
psql -U "$DB_USER" -d "$DB" -At -c "
  SELECT DISTINCT ${DATE_COL}::date
  FROM ${TABLE}
  WHERE ${DATE_COL} < now() - interval '${CUTOFF}'
  ORDER BY 1;
" | while read -r day; do
    out="${ARCHIVE_DIR}/${TABLE}_${day}.csv"

    # \copy runs client-side: it writes to wherever THIS machine can,
    # i.e. the mounted share. (Server-side COPY would write on the DB
    # host and need superuser — not what we want here.)
    psql -U "$DB_USER" -d "$DB" -c "\copy (
        SELECT * FROM ${TABLE}
        WHERE ${DATE_COL}::date = DATE '${day}'
        ORDER BY ${DATE_COL}
    ) TO '${out}' WITH (FORMAT csv, HEADER true)"

    # Only delete once the file genuinely exists and isn't empty.
    if [[ -s "$out" ]]; then
        psql -U "$DB_USER" -d "$DB" -c "
            DELETE FROM ${TABLE}
            WHERE ${DATE_COL}::date = DATE '${day}';"
        echo "archived + pruned ${day}"
    else
        echo "WARN: export failed for ${day}, keeping rows" >&2
    fi
done

And the crontab entry on the app server:

# Daily at 02:30 — archive + prune data older than 6 months
30 2 * * *  /var/www/app/scripts/archive-old-data.sh >> /var/log/archive.log 2>&1

The Gotchas That Bit Me

\copy vs COPY. This one cost me an hour. PostgreSQL's COPY ... TO 'file' runs server-side — it writes on the database host's filesystem and needs elevated privileges. The backslash version, \copy, runs through the psql client, so it writes wherever the script's user can reach — including the Samba mount. On a setup like this you almost always want \copy.

Export first, verify, then delete. The order is the whole safety story. If the Samba mount silently drops, a naive "delete then export" loses data forever. I check mountpoint -q before doing anything, and I only DELETE a given day after confirming its CSV exists and is non-empty ([[ -s "$out" ]]).

Per-day, not all-at-once. Looping one day at a time means a single bad day doesn't block the rest, re-runs are safe (already-archived days are simply re-exported and re-pruned), and each delete is small.

A DELETE doesn't shrink anything — and plain VACUUM won't hand disk back to the OS. This is the part that caught me out. In PostgreSQL, DELETE never physically removes rows; it just marks them dead. A regular VACUUM (or autovacuum) then marks that space reusable inside the table — but the file on disk stays exactly as big as it was. To actually reclaim the space and return it to the operating system, you need VACUUM FULL, which rewrites the entire table from scratch:

VACUUM FULL events;

The catch: VACUUM FULL takes an ACCESS EXCLUSIVE lock and blocks every read and write on that table while it runs. So I scheduled it for a low-traffic window after the big prune — only after VACUUM FULL finished did the 35 GB actually come down.

Index the date column. Both the daily cutoff scan and the delete lean entirely on the timestamp column — without an index on it, every run is a full sequential scan of a 35 GB table.

What I'd Reach For Next Time

This works and has run quietly for months. But the more scalable version is native range partitioning — partition the table by month, and "archiving" becomes DETACH PARTITION + a single COPY of that partition. Dropping or detaching a partition frees its disk instantly — no row-by-row delete, no dead tuples, and no VACUUM FULL lock to schedule around at 2am. The Samba + symlink + cron backbone stays exactly the same; only the database side gets cleaner.

The core idea is cheap and portable: make the remote storage look local with a mount and a symlink, and never prune data you haven't proven you archived.