Introduction

Database scalability challenges often emerge as systems mature and data volumes grow beyond initial design assumptions. One frequently encountered constraint is the PostgreSQL INTEGER type limitation for primary keys, which represents a 32-bit signed value with a maximum threshold of 2,147,483,647 records.

In a production environment we recently encountered, an activity_id column serving as the primary key across more than 10 interconnected tables had accumulated approximately 1.7 billion records. The affected tables varied significantly in size, ranging from 100 MB for reference tables to 500 GB for core transactional data. Under these conditions, executing a conventional ALTER TABLE … TYPE BIGINT operation would introduce unacceptable locking periods and service disruption risks.

This article details a comprehensive, non-blocking migration strategy that enables the expansion of INTEGER primary keys to BIGINT on high-volume, interconnected PostgreSQL tables while maintaining continuous application availability.

Technical Limitations of Direct Column Type Modification

PostgreSQL’s approach to column type changes requires a complete table rewrite, even when converting between compatible numeric types such as INTEGER and BIGINT. This architectural behavior stems from PostgreSQL’s MVCC (Multi-Version Concurrency Control) implementation and storage format requirements.

For large-scale production systems, direct column modification presents several critical issues:

Exclusive Table Locks: The ALTER COLUMN operation acquires an AccessExclusiveLock on the target table, blocking all concurrent read and write operations for the duration of the migration.

Cascading Lock Propagation: Foreign key relationships trigger cascading locks across dependent tables, potentially affecting the entire application’s data access layer.

Extended Downtime Windows: Processing times scale linearly with table size, making migration windows unpredictable and often exceeding acceptable maintenance windows.

Limited Rollback Options: Failed migrations leave tables in intermediate states with complex recovery scenarios.

Migration Architecture and Design Principles

Our solution implements a three-table architecture pattern that maintains data consistency while enabling gradual migration. This approach leverages PostgreSQL’s view system and trigger mechanisms to provide transparent data access during the transition period.

Core Design Requirements

Zero-downtime operation with continuous read/write availability
Transactional consistency across all migration phases
Backward compatibility with existing application code
Granular progress monitoring and rollback capabilities
Minimal resource contention during peak operational hours

Table Structure Design

Component	Role	Purpose
activity_old	Legacy data container	Preserves existing INT-based records in read-only state
activity_new	New data receptor	Captures incoming records with BIGINT primary keys
activity_final	Migration destination	Consolidates transformed data with final BIGINT schema
activity (view)	Unified interface	Provides transparent access to all data sources

Implementation Methodology

Phase 1 & 2: Infrastructure Setup and Access Layer (Single Transaction)

Execute the initial setup operations within a single transaction to ensure atomicity and provide a clean rollback point if any step fails:

BEGIN;

-- Phase 1: Table Restructuring and Preparation
-- Preserve existing data structure
ALTER TABLE activity RENAME TO activity_old;

-- Create the new table structure with enhanced primary key capacity
CREATE TABLE activity_new (
    activity_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    -- Additional columns matching original schema
    CONSTRAINT activity_new_name_check CHECK (length(name) > 0)
);

-- Establish appropriate indexing strategy
CREATE INDEX idx_activity_new_created_at ON activity_new (created_at);
CREATE INDEX idx_activity_new_name ON activity_new (name);

-- Prepare the final consolidation table
CREATE TABLE activity_final (
    activity_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    -- Mirror complete schema from original table
    CONSTRAINT activity_final_name_check CHECK (length(name) > 0)
);

-- Replicate indexing structure for optimal performance
CREATE INDEX idx_activity_final_created_at ON activity_final (created_at);
CREATE INDEX idx_activity_final_name ON activity_final (name);

-- Phase 2: Transparent Data Access Layer
-- Implement unified view that abstracts underlying table complexity
CREATE OR REPLACE VIEW activity AS
SELECT activity_id, name, created_at FROM activity_new
UNION ALL
SELECT activity_id::BIGINT, name, created_at FROM activity_old
UNION ALL
SELECT activity_id, name, created_at FROM activity_final;

-- Verify the view is accessible before committing
SELECT COUNT(*) FROM activity;

COMMIT;

This consolidated transaction ensures that all existing queries continue functioning without modification while providing access to data across all storage locations. If any step fails, the entire setup rolls back automatically.

Phase 3: Write Operation Redirection (Transaction)

Execute the insertion redirection setup within a transaction to ensure consistency:

BEGIN;

-- Implement insertion redirection to ensure new data flows into BIGINT-enabled structure
CREATE OR REPLACE RULE activity_insert_redirect AS
ON INSERT TO activity DO INSTEAD
INSERT INTO activity_new (activity_id, name, created_at) 
VALUES (NEW.activity_id, NEW.name, NEW.created_at);

-- Test the redirection rule with a sample insert
INSERT INTO activity (activity_id, name, created_at) 
VALUES (999999999999, 'test_insert', NOW());

-- Verify the insert went to activity_new
SELECT COUNT(*) FROM activity_new WHERE name = 'test_insert';

-- Clean up test record
DELETE FROM activity_new WHERE name = 'test_insert';

COMMIT;

Phase 4: Update and Delete Operation Handling (Transaction)

Implement comprehensive CRUD operation management within a transaction to ensure trigger consistency:

BEGIN;

-- Create the update handler function
CREATE OR REPLACE FUNCTION activity_update_handler()
RETURNS trigger AS $$
DECLARE
    update_count INTEGER;
BEGIN
    -- Attempt update in activity_new first (most recent data)
    UPDATE activity_new
    SET name = NEW.name,
        created_at = NEW.created_at
    WHERE activity_id = OLD.activity_id;
    
    GET DIAGNOSTICS update_count = ROW_COUNT;
    IF update_count > 0 THEN
        RETURN NEW;
    END IF;

    -- Check activity_old for legacy records
    UPDATE activity_old
    SET name = NEW.name,
        created_at = NEW.created_at
    WHERE activity_id = OLD.activity_id;
    
    GET DIAGNOSTICS update_count = ROW_COUNT;
    IF update_count > 0 THEN
        RETURN NEW;
    END IF;

    -- Finally attempt update in activity_final
    UPDATE activity_final
    SET name = NEW.name,
        created_at = NEW.created_at
    WHERE activity_id = OLD.activity_id;
    
    GET DIAGNOSTICS update_count = ROW_COUNT;
    IF update_count > 0 THEN
        RETURN NEW;
    END IF;
    
    -- Record not found in any table
    RAISE EXCEPTION 'Record with activity_id % not found', OLD.activity_id;
END;
$$ LANGUAGE plpgsql;

-- Create the delete handler function
CREATE OR REPLACE FUNCTION activity_delete_handler()
RETURNS trigger AS $$
BEGIN
    -- Try deleting from activity_new first (most recent data)
    DELETE FROM activity_new WHERE activity_id = OLD.activity_id;
    IF FOUND THEN
        RETURN OLD;
    END IF;

    -- Try deleting from activity_old
    DELETE FROM activity_old WHERE activity_id = OLD.activity_id;
    IF FOUND THEN
        RETURN OLD;
    END IF;

    -- Finally try deleting from activity_final
    DELETE FROM activity_final WHERE activity_id = OLD.activity_id;
    IF FOUND THEN
        RETURN OLD;
    END IF;

    -- Record not found in any table - PostgreSQL will handle this gracefully
    -- by returning NULL, which indicates no rows were affected
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Attach the triggers to the view
CREATE TRIGGER activity_update_trigger
    INSTEAD OF UPDATE ON activity
    FOR EACH ROW
    EXECUTE FUNCTION activity_update_handler();

CREATE TRIGGER activity_delete_trigger
    INSTEAD OF DELETE ON activity
    FOR EACH ROW
    EXECUTE FUNCTION activity_delete_handler();

-- Test update and delete functionality if there are existing records
DO $$
DECLARE
    test_id BIGINT;
BEGIN
    -- Insert a test record for comprehensive testing
    INSERT INTO activity (name, created_at) 
    VALUES ('crud_test_record', NOW());
    
    -- Get the ID of our test record
    SELECT activity_id INTO test_id 
    FROM activity 
    WHERE name = 'crud_test_record' 
    LIMIT 1;
    
    IF test_id IS NOT NULL THEN
        -- Test update functionality
        UPDATE activity 
        SET name = 'crud_test_updated' 
        WHERE activity_id = test_id;
        
        -- Verify update worked
        IF NOT EXISTS (SELECT 1 FROM activity WHERE activity_id = test_id AND name = 'crud_test_updated') THEN
            RAISE EXCEPTION 'Update test failed';
        END IF;
        
        -- Test delete functionality
        DELETE FROM activity WHERE activity_id = test_id;
        
        -- Verify delete worked
        IF EXISTS (SELECT 1 FROM activity WHERE activity_id = test_id) THEN
            RAISE EXCEPTION 'Delete test failed';
        END IF;
        
        RAISE NOTICE 'CRUD operations tested successfully';
    END IF;
END $$;

COMMIT;

Phase 5: Controlled Data Migration Process (Transactional Batches)

Execute the data migration using transactional batch processing to ensure data consistency and provide rollback capabilities at each step:

-- Migration script for activity_old to activity_final with transaction control
DO $$
DECLARE
    batch_size INTEGER := 50000;
    processed_count INTEGER;
    total_migrated INTEGER := 0;
    batch_start_id INTEGER;
    batch_end_id INTEGER;
BEGIN
    LOOP
        -- Start transaction for each batch
        BEGIN
            -- Get the range for this batch
            SELECT MIN(activity_id), MAX(activity_id) 
            FROM (
                SELECT activity_id
                FROM activity_old
                WHERE activity_id NOT IN (SELECT activity_id FROM activity_final)
                ORDER BY activity_id
                LIMIT batch_size
            ) batch_range
            INTO batch_start_id, batch_end_id;
            
            -- Exit if no more records to process
            IF batch_start_id IS NULL THEN
                EXIT;
            END IF;
            
            -- Migrate batch from activity_old to activity_final
            INSERT INTO activity_final (activity_id, name, created_at)
            SELECT activity_id::BIGINT, name, created_at 
            FROM activity_old
            WHERE activity_id >= batch_start_id 
            AND activity_id <= batch_end_id
            AND activity_id NOT IN (SELECT activity_id FROM activity_final);
            
            GET DIAGNOSTICS processed_count = ROW_COUNT;
            
            -- Remove migrated records from activity_old
            DELETE FROM activity_old
            WHERE activity_id >= batch_start_id 
            AND activity_id <= batch_end_id
            AND activity_id IN (SELECT activity_id FROM activity_final);
            
            total_migrated := total_migrated + processed_count;
            
            -- Log progress
            RAISE NOTICE 'Batch completed: migrated % records (IDs: % to %), total: %', 
                processed_count, batch_start_id, batch_end_id, total_migrated;
            
            -- Brief pause to allow other operations
            PERFORM pg_sleep(0.1);
            
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Error in batch % to %: %. Rolling back batch.', 
                    batch_start_id, batch_end_id, SQLERRM;
                -- Batch will be rolled back automatically
                -- Continue with next batch or exit based on error severity
                EXIT;
        END;
    END LOOP;
    
    RAISE NOTICE 'Migration from activity_old completed. Total records migrated: %', total_migrated;
END $$;

-- Separate migration script for activity_new to activity_final
DO $$
DECLARE
    batch_size INTEGER := 10000;
    processed_count INTEGER;
    total_migrated INTEGER := 0;
    current_batch_ids BIGINT[];
BEGIN
    LOOP
        BEGIN
            -- Collect IDs for this batch
            SELECT ARRAY(
                SELECT activity_id
                FROM activity_new
                WHERE activity_id NOT IN (SELECT activity_id FROM activity_final)
                ORDER BY activity_id
                LIMIT batch_size
            ) INTO current_batch_ids;
            
            -- Exit if no more records
            IF array_length(current_batch_ids, 1) IS NULL THEN
                EXIT;
            END IF;
            
            -- Migrate batch from activity_new to activity_final
            INSERT INTO activity_final (activity_id, name, created_at)
            SELECT activity_id, name, created_at
            FROM activity_new
            WHERE activity_id = ANY(current_batch_ids);
            
            GET DIAGNOSTICS processed_count = ROW_COUNT;
            
            -- Remove migrated records from activity_new
            DELETE FROM activity_new
            WHERE activity_id = ANY(current_batch_ids);
            
            total_migrated := total_migrated + processed_count;
            
            RAISE NOTICE 'Migrated % records from activity_new, total: %', 
                processed_count, total_migrated;
            
            PERFORM pg_sleep(0.05);
            
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Error migrating batch from activity_new: %. Rolling back batch.', SQLERRM;
                EXIT;
        END;
    END LOOP;
    
    RAISE NOTICE 'Migration from activity_new completed. Total records migrated: %', total_migrated;
END $$;

Phase 6: Sequence Management and Auto-increment Configuration (Transaction)

Establish sequence management within a transaction to ensure consistency:

BEGIN;

-- Create BIGINT sequence
CREATE SEQUENCE activity_id_seq AS BIGINT;

-- Determine current maximum ID across all tables and set sequence accordingly
DO $$
DECLARE
    max_id BIGINT;
    old_max BIGINT;
    new_max BIGINT;
    final_max BIGINT;
BEGIN
    -- Get maximum from each table separately for better visibility
    SELECT COALESCE(MAX(activity_id), 0) INTO old_max FROM activity_old;
    SELECT COALESCE(MAX(activity_id), 0) INTO new_max FROM activity_new;
    SELECT COALESCE(MAX(activity_id), 0) INTO final_max FROM activity_final;
    
    -- Log current maximums for verification
    RAISE NOTICE 'Maximum IDs - Old: %, New: %, Final: %', old_max, new_max, final_max;
    
    -- Calculate overall maximum
    max_id := GREATEST(old_max, new_max, final_max);
    
    -- Set sequence to continue from current maximum + safety margin
    PERFORM setval('activity_id_seq', max_id + 1000);
    
    RAISE NOTICE 'Sequence set to start from: %', max_id + 1000;
END $$;

-- Configure default value for new records
ALTER TABLE activity_new 
ALTER COLUMN activity_id SET DEFAULT nextval('activity_id_seq');

-- Test sequence functionality
INSERT INTO activity (name, created_at) 
VALUES ('sequence_test', NOW());

-- Verify the sequence-generated ID
SELECT activity_id, name FROM activity WHERE name = 'sequence_test';

-- Clean up test record
DELETE FROM activity WHERE name = 'sequence_test';

COMMIT;

Phase 7: Migration Completion and Cleanup (Final Transaction)

Execute the final cleanup within a comprehensive transaction with verification steps:

BEGIN;

-- Comprehensive verification before cleanup
DO $$
DECLARE
    old_count INTEGER;
    new_count INTEGER;
    final_count INTEGER;
    total_original INTEGER;
    view_count INTEGER;
BEGIN
    -- Count remaining records in each table
    SELECT COUNT(*) INTO old_count FROM activity_old;
    SELECT COUNT(*) INTO new_count FROM activity_new;
    SELECT COUNT(*) INTO final_count FROM activity_final;
    SELECT COUNT(*) INTO view_count FROM activity;
    
    RAISE NOTICE 'Pre-cleanup verification:';
    RAISE NOTICE '  activity_old: % records', old_count;
    RAISE NOTICE '  activity_new: % records', new_count;
    RAISE NOTICE '  activity_final: % records', final_count;
    RAISE NOTICE '  activity view: % records', view_count;
    
    -- Ensure migration is complete
    IF old_count > 0 OR new_count > 0 THEN
        RAISE EXCEPTION 'Migration not complete. activity_old: %, activity_new: %', old_count, new_count;
    END IF;
    
    -- Verify data integrity
    IF final_count != view_count THEN
        RAISE EXCEPTION 'Data integrity check failed. Final table: %, View: %', final_count, view_count;
    END IF;
    
    RAISE NOTICE 'All verification checks passed. Proceeding with cleanup.';
END $$;

-- Perform final cleanup operations
DROP VIEW IF EXISTS activity CASCADE;
DROP TABLE IF EXISTS activity_old;
DROP TABLE IF EXISTS activity_new;

-- Rename final table to original name
ALTER TABLE activity_final RENAME TO activity;

-- Restore original sequence association if it was originally SERIAL
ALTER TABLE activity ALTER COLUMN activity_id SET DEFAULT nextval('activity_id_seq');

-- Recreate any indexes that might have been dropped during the process
-- (These should already exist from Phase 1, but included for completeness)
CREATE INDEX IF NOT EXISTS idx_activity_created_at ON activity (created_at);
CREATE INDEX IF NOT EXISTS idx_activity_name ON activity (name);

-- Update table statistics
ANALYZE activity;

-- Final verification
DO $$
DECLARE
    final_record_count INTEGER;
    sequence_current_value BIGINT;
BEGIN
    SELECT COUNT(*) INTO final_record_count FROM activity;
    SELECT last_value INTO sequence_current_value FROM activity_id_seq;
    
    RAISE NOTICE 'Migration completed successfully:';
    RAISE NOTICE '  Final activity table: % records', final_record_count;
    RAISE NOTICE '  Sequence current value: %', sequence_current_value;
    RAISE NOTICE '  Primary key type: %', pg_typeof((SELECT activity_id FROM activity LIMIT 1));
END $$;

COMMIT;

-- Post-migration verification (outside transaction)
SELECT 'Migration Complete' as status,
       COUNT(*) as total_records,
       MIN(activity_id) as min_id,
       MAX(activity_id) as max_id,
       pg_typeof(activity_id) as id_type
FROM activity;

Transaction Summary and Rollback Strategy

Each phase is now properly encapsulated in transactions, providing the following benefits:

Phase 1 & 2 (Combined Transaction):

Atomic setup of all infrastructure components
Single rollback point for entire foundation
Immediate view availability upon commit

Phase 3 (Insert Redirection Transaction):

Safe rule creation with testing
Rollback capability if redirection fails

Phase 4 (Update and Delete Handling Transaction):

Atomic trigger and function creation for all CRUD operations
Built-in testing to verify update and delete functionality
Comprehensive error handling for missing records

Phase 5 (Migration Batches):

Each batch operates in its own transaction
Individual batch rollback on errors
Progress preservation between batches

Phase 6 (Sequence Setup Transaction):

Atomic sequence configuration
Built-in testing and verification

Phase 7 (Cleanup Transaction):

Comprehensive verification before cleanup
All-or-nothing final transformation
Complete rollback if any verification fails

This transactional approach ensures data consistency, provides multiple rollback points, and maintains system integrity throughout the entire migration process.

Performance Considerations and Monitoring

Throughout the migration process, monitor key performance indicators to ensure system stability:

Lock contention metrics: Track blocked queries and lock wait times
Replication lag: Monitor replica synchronization if using streaming replication
I/O utilization: Ensure migration batches don’t saturate storage subsystems
Connection pool utilization: Verify application connection patterns remain stable

Risk Mitigation and Rollback Strategy

This migration approach provides multiple rollback points:

Pre-migration state: Simple table rename reversal
During migration: Pause batch processing and maintain current state
Post-view creation: Remove view and restore original table access
Complete rollback: Restore from backup with minimal data loss window

Conclusion

This methodology successfully addresses the challenge of migrating critical primary key columns from INTEGER to BIGINT in high-availability PostgreSQL environments. The approach demonstrates several key advantages: continuous application availability, predictable resource utilization, granular progress control, and comprehensive rollback capabilities.

The three-table architecture pattern with view-based abstraction proves effective for managing complex schema migrations while maintaining operational stability. The inclusion of comprehensive CRUD operation handling through INSTEAD OF triggers ensures that all application operations continue to function seamlessly during the migration process.

This technique extends beyond simple type changes and provides a robust framework for various high-impact schema evolution scenarios in production PostgreSQL systems.

Migrating PostgreSQL INT to BIGINT on Large Primary Key Tables Without Downtime