← Live Compression Software

Should I archive, partition or compress old database rows?

Updated · DBShifts

Short answer

Archive when old rows are almost never read and can live somewhere else. Partition when you design the table around time and want cleanup to be instant. Compress in place when old rows must stay in the database and readable by key, but you no longer want them taking full space. Many teams combine two.

Key facts

  • Archiving frees the most space in the live database but moves old rows to a place every reader has to know about.
  • Dropping or detaching a partition is a metadata change, so removing a month of rows takes no longer than removing a day.
  • Partitioning an existing large table means copying it into the partitioned layout.
  • In MySQL, the partitioning column must be part of every unique key, including the primary key.
  • Compressing old rows in place keeps them in the same database, under the same backups and permissions.
  • Every approach that moves rows should do it in short transactions, so a failure halfway leaves each row in exactly one place.

Archive: move them out

Archiving copies old rows to another table, another database, or files in object storage, then deletes them from the live table. It is the right call for data nobody reads in normal operation: audit trails kept for a regulator, events older than any report looks back.

The cost shows up later. Anyone who needs an old row — a support agent, a refund, a year-end report — needs to know it moved and where to. If the archive is files, reading one row means loading a file; if it is another database, it has its own access, backups and drift to manage.

Partition: make time a unit

Range partitioning by month or week stores each period in its own physical table. Queries that filter on the date only read the partitions they need, and cleanup is DROP or DETACH PARTITION: instant, no bloat, no WAL storm.

It is a design decision more than a cleanup tool. Converting a large existing table means rebuilding it, every query should filter on the partition column to benefit, and in MySQL every unique key has to include it. Done from the start on a time-series table, it is often the best answer.

Compress in place: keep them, smaller

The third option keeps old rows in the same database and readable by key, but stores them in a much smaller form. Nothing moves to another system, backups and permissions stay as they are, and a support agent can still look up last year's order.

The trade-off is how old rows are read. Compressed rows are usually read through a specific interface rather than as ordinary rows, and reading one costs a little more than reading a recent row. For data that is read occasionally and by key, that is usually fine.

How to choose

Ask who reads old rows, how, and how often. Never, except for compliance: archive. Mostly by date range, with cleanup by period: partition. By key, occasionally, and they must stay in the database: compress in place.

They combine well. A table partitioned by month can drop partitions after seven years, while rows from the last few years are compressed in place and the current month stays as it is.

Checklist

  1. 1

    List who reads rows older than three months, six months and a year, and how.

  2. 2

    Measure what share of the table those age bands are.

  3. 3

    Check whether queries already filter on a date column.

  4. 4

    Check retention rules: what must be kept, and for how long.

  5. 5

    Pick the approach per age band, not one for the whole table.

  6. 6

    Test the move on a copy and check the row count and contents on both sides.

Common mistakes

Archiving rows people still look up

The space comes back, and then the support tickets arrive. Find the readers before you move anything.

Partitioning without the queries to match

If queries do not filter on the partition column, every partition is read and nothing gets faster.

One huge move

Moving years of rows in one transaction holds locks, bloats the table and makes a failure expensive. Move in small batches.

How LCS does it

  • LCS is the compress-in-place option for PostgreSQL, MySQL and MariaDB: rows older than an age you set move into compressed blocks in the same database, and recent rows stay ordinary rows.
  • Old rows stay readable by key and key range from your DBShifts account; plain SQL sees only the recent rows.
  • It needs a single-column integer primary key and a date or timestamp column, and refuses tables that other tables reference by foreign key or that have INSERT or DELETE triggers, because moving rows fires both.

Questions people also ask

Can I partition a table that already has data?

Yes, but it means creating the partitioned table and copying the data into it, or attaching existing tables as partitions in PostgreSQL. On a large table that is a planned migration, not a quick change.

Is archiving to object storage cheaper?

Per gigabyte, usually, but reading one row back means loading and scanning a file. It suits data that is read in bulk or not at all.

Does compressing old rows slow down the current ones?

Not when only old rows are compressed and recent rows stay in the original table. Whole-table compression is different: it costs every read and write.

Keep old rows in the database, at a fraction of the size

LCS for PostgreSQL, MySQL and MariaDB. Measure a table first; nothing changes until you enable it.

Start free

4 guides on keeping old rows small. Back to Live Compression Software.