Archiving Old Database Data: Strategy and Implementation

The events table with 500 million rows and growing by 2 million per day is a problem that gets worse every day. SELECT slows down, VACUUM can't keep up, indexes take gigabytes. Without archiving old database data, the database bloats, locks interfere with operations, and SSD storage costs hit the budget. We solve such tasks end-to-end — implement an archiving system that separates hot from cold data without downtime or performance loss. Our experience shows that a proper data archiving strategy reduces database load by 70% and cuts storage costs by factor of 2–3, saving roughly $500 per month on a 1TB dataset.

What problems does archiving old data solve?

The main pain is performance degradation. When a table grows, even simple SELECT with an index slows due to B-tree depth and fragmentation. VACUUM can't clean dead rows, and autovacuum lags behind. Rising storage costs — expensive SSD for data accessed once a year. Lock contention during bulk deletes — a DELETE without batching locks the table for minutes. We solve this with batch archiving using SKIP LOCKED — parallel workers don't conflict. Understanding MVCC (Multi-Version Concurrency Control) and WAL (Write-Ahead Log) overhead helps tune autovacuum thresholds and prevent bloat.

After archiving, disk load drops by 70%, SELECT time reduces by 3–5 times, and database size shrinks by factor of 2.

Data Archiving Strategy Overview

Choosing the right method for archiving old database data is critical. Below we compare common strategies.

Archiving strategies: comparison of methods

How to choose the right method?

How we implement archiving: step-by-step instructions

Here is a concise outline of the steps involved in archiving old database data:

1. Analyze structure and load
2. Choose strategy (e.g., batch archiving with SKIP LOCKED)
3. Write archiving function with SKIP LOCKED
4. Configure scheduler (e.g., Laravel command)
5. Monitor and VACUUM
6. Restore from archive
7. Verify integrity

-- Archive table (can be in a separate schema or database) CREATE TABLE archive.events ( LIKE public.events INCLUDING ALL ); -- Archiving function with batches CREATE OR REPLACE FUNCTION archive_old_events( p_before_date TIMESTAMPTZ, p_batch_size INTEGER DEFAULT 10000 ) RETURNS TABLE(batches_processed INTEGER, rows_archived BIGINT) LANGUAGE plpgsql AS $$ DECLARE v_batches INTEGER := 0; v_total BIGINT := 0; v_moved INTEGER; BEGIN LOOP -- Move one batch to archive WITH moved AS ( DELETE FROM public.events WHERE id IN ( SELECT id FROM public.events WHERE created_at < p_before_date LIMIT p_batch_size FOR UPDATE SKIP LOCKED -- skip locked rows ) RETURNING * ) INSERT INTO archive.events SELECT * FROM moved; GET DIAGNOSTICS v_moved = ROW_COUNT; EXIT WHEN v_moved = 0; v_batches := v_batches + 1; v_total := v_total + v_moved; -- Pause between batches — don't overload the disk PERFORM pg_sleep(0.1); -- Progress RAISE NOTICE 'Batch %: % rows archived (total: %)', v_batches, v_moved, v_total; END LOOP; RETURN QUERY SELECT v_batches, v_total; END $$; 

SELECT * FROM archive_old_events('old_date'::timestamptz, 10000); 

// app/Console/Commands/ArchiveOldData.php class ArchiveOldData extends Command { protected $signature = 'db:archive {--days=365 : Archive data older than N days}'; protected $description = 'Archive old records to archive tables'; public function handle(): int { $beforeDate = now()->subDays($this->option('days'))->toDateTimeString(); $this->info("Archiving events before {$beforeDate}..."); $result = DB::selectOne( 'SELECT * FROM archive_old_events(?::timestamptz, 5000)', [$beforeDate] ); $this->info("Done: {$result->batches_processed} batches, {$result->rows_archived} rows"); // VACUUM after mass deletion DB::statement('VACUUM ANALYZE events'); return self::SUCCESS; } } 

// app/Console/Kernel.php $schedule->command('db:archive --days=180') ->monthlyOn(1, '02:00') ->withoutOverlapping() ->onFailure(fn() => Notification::route('telegram', config('services.telegram.ops_chat')) ->notify(new ArchivingFailedNotification())); 

# Restore data from CSV archive back to database gunzip -c /mnt/archive/events/2024-01/events_2024-01.csv.gz | \ psql -d mydb -c "COPY events FROM STDIN CSV HEADER" 

What's included in the work

• Analysis of table structure and load
• Designing archive schema (partitioning, separate DB, or files)
• Writing archiving functions/scripts
• Configuring scheduler and monitoring
• Retention policy with rotation
• Restore scenario from archive
• Documentation and team training

Implementation timeline

Typical mistakes

• Forgetting VACUUM after mass deletion — table bloat.
• Using one transaction for the entire volume — risk of hours-long rollback.
• Not verifying archive before deletion — data loss.
• Ignoring SKIP LOCKED — parallel processes block each other.

Why trust us with archiving

We have over 10 years of experience in PostgreSQL and MySQL administration. We have implemented archiving systems for projects with petabytes of data. We guarantee that the process will not affect core business logic and will be fully automated. Contact us — we will evaluate your project and offer the optimal solution. Get a free database performance engineer consultation.

For more information refer to the official PostgreSQL documentation at https://www.postgresql.org/docs/current/sql-createtable.html. Retention policy is agreed upon with business and regulatory requirements (e.g., GDPR).