PostgreSQL: Migrating INT to BIGINT Primary Keys

Overview

When tables approach INT limits (~2.1 billion rows), migrating to BIGINT becomes necessary. This guide covers the migration process including foreign key dependencies and maintenance operations.

Pre-Migration Checklist

Before starting:

• Scale down application task count to 0
• Stop background workers (Celery beat, Celery cam, etc.)
• Consider temporarily upgrading database instance for faster ALTER operations
• Complete any pending database updates/maintenance

Migration Steps

Step 1: Alter Primary Key Column

ALTER TABLE journal_ent ALTER COLUMN id TYPE bigint;

Step 2: Find Foreign Key Dependencies

Identify all tables referencing the modified table:

SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE confrelid = 'journal_ent'::regclass
  AND contype = 'f';

Step 3: Update Foreign Key Columns

Alter each referencing column to match the new type:

-- Example: c_item table references journal_ent
ALTER TABLE c_item 
ALTER COLUMN journal_ent_id TYPE bigint;

-- Repeat for each foreign key relationship
ALTER TABLE p_item 
ALTER COLUMN journal_ent_id TYPE bigint;

Post-Migration Maintenance

Check Table Sizes

Before vacuum, assess current table sizes:

SELECT 
    schemaname AS table_schema,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC,
         pg_relation_size(relid) DESC;

Vacuum Operations

Run VACUUM FULL on high-churn tables to reclaim space:

-- Primary altered table
VACUUM FULL journal_ent;

-- Related high-volume tables
VACUUM FULL ch_item;
VACUUM FULL em_log;
VACUUM FULL inv_item;
VACUUM FULL sm_log;

Session Cleanup

Truncate session tables if safe to do so:

TRUNCATE TABLE django_session;

Post-Migration Steps

1. Scale database instance back to normal size
2. Restore application task counts
3. Start background workers
4. Re-enable scheduled jobs and lambdas
5. Verify auto-settlement processes

Rollback Considerations

⚠️ Warning: ALTER COLUMN TYPE operations are not easily reversible. BIGINT → INT conversion will fail if any values exceed INT range.

If rollback is needed before data exceeds INT limits:

-- Only works if max(id) < 2147483647
ALTER TABLE journal_ent ALTER COLUMN id TYPE integer;

Performance Notes

Minimizing Downtime

For large tables, consider alternative approaches:

1. Shadow table migration: Create new table, copy data, swap names
2. pg_repack: Online table restructuring without locks
3. Logical replication: Migrate to new table structure incrementally

INT vs BIGINT Limits

Rule of thumb: If inserting >1000 rows/day, plan for BIGINT migration within 5 years.