Data Archiving Strategies: Scaling Performance via Data Management

Previously in this course, we discussed handling soft deletes, which allows you to hide records without losing them. However, soft deletes don't solve the problem of ever-growing tables that eventually degrade query performance. In this lesson, we will implement data archiving, the practice of moving inactive, historical data to secondary storage to ensure your primary operational database remains performant.

The Problem: When "Large" Becomes "Slow"

As your SaaS application matures, tables like audit_logs, notifications, or even old orders grow indefinitely. Even with proper indexing, large tables increase the size of B-Tree indexes, leading to more I/O operations and slower cache hits.

Archiving is not just about freeing up disk space; it is about maintaining scalability. By keeping only the "hot" (frequently accessed) data in your primary tables, you ensure that indexes stay small and memory-resident, which is critical for consistent application response times.

Designing the Archival Schema

An archival strategy generally follows a "Mirror and Move" pattern. You create an archival table that mimics the structure of the primary table but lives in a different partition, schema, or even a separate database instance.

For our SaaS project, let’s consider a system_logs table.

SQL
-- Primary table
CREATE TABLE system_logs (
    id BIGINT PRIMARY KEY,
    user_id INT,
    action TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Archival table (identical structure)
CREATE TABLE system_logs_archive (
    id BIGINT PRIMARY KEY,
    user_id INT,
    action TEXT,
    created_at TIMESTAMP
);

Implementing Archiving Logic

The goal is to move rows older than a specific threshold (e.g., 90 days) from the primary table to the archive. We do this in a single transaction to ensure data integrity.

Step 1: The Move Operation

We use a WITH clause (Common Table Expression) to delete the rows from the primary table while returning them to be inserted into the archive.

SQL
BEGIN;

-- Move rows older than 90 days
WITH moved_rows AS (
    DELETE FROM system_logs
    WHERE created_at < CURRENT_DATE - INTERVAL '90 days'
    RETURNING *
)
INSERT INTO system_logs_archive (id, user_id, action, created_at)
SELECT * FROM moved_rows;

COMMIT;

Note: If you have foreign key constraints, ensure your archival table handles them gracefully—often, these constraints are removed in the archive to facilitate faster bulk inserts.

Hands-on Exercise

Identify one table in our project (e.g., notifications or audit_logs) that grows linearly with user activity.

1. Write a CREATE TABLE ... AS SELECT * FROM ... WHERE 1=0 statement to create an empty archive version of that table.
2. Draft a SQL script that uses a transaction to move data older than 6 months to your new archive table.
3. Challenge: How would you verify that no data was lost during the transfer? (Hint: Compare COUNT(*) before and after the transaction).

Common Pitfalls in Archiving

• Blocking Operations: Running a single massive DELETE on a table with millions of rows can lock the table for extended periods. Solution: Delete in smaller batches (e.g., 5,000 rows at a time) using a loop in your application or a stored procedure.
• Missing Indexes on Archive: Users often forget to index the archive table. While performance isn't as critical as the primary, you still need to query it occasionally for compliance or customer support. Add minimal indexing to support common search patterns.
• Fragmentation: Frequent deletes can leave "holes" in your primary table (dead tuples in PostgreSQL). Remember to run periodic VACUUM or table maintenance to reclaim that space.

FAQ

Q: Should I use a separate database for archiving? A: For most SaaS applications, keeping the archive in the same database (but a different schema) is fine. If the data volume is massive (terabytes), moving to a cheaper, read-only storage or a data warehouse (like BigQuery or Snowflake) is the correct architectural pivot.

Q: How do I handle reports that need both current and archived data? A: Use a VIEW that performs a UNION ALL between the primary table and the archive table. This allows application code to query the union as if it were a single table.

Recap

Archiving is an essential performance maintenance task. By isolating historical data, we keep our primary indexes lean and our queries fast. We've covered the structure of archival tables and the logic to move data safely using transactions.

Up next: We will dive into modeling audit trails to ensure we can track changes to our data over time without bloating our main entities.