PostgreSQL Table Optimization Part - II: Best Techniques for Efficient Shrinking

Copy-Swap-Drop Method:

The Copy-Swap-Drop method is a best-practice approach for modifying, migrating, or optimizing large PostgreSQL tables with minimal downtime.

Instead of directly altering a large table, we:

• Copy data to a new table without constraints or indexes (for speed).
• Rebuild indexes and constraints on the new table.
• Swap the old and new tables to avoid downtime.
• Drop the old table after verification.

This method is commonly used in schema changes, table optimizations, and large data migrations.

Below are the 2 approaches to use copy-swap-drop method:

1. Inserting Data with Table Constraints
2. Inserting Data without Table Constraints

1. Inserting Data with Table Constraints:

1. Prepare the New Table with Indexes & Constraints
   
   

   This creates a new table with: All columns, Indexes, Constraints (But does NOT copy triggers, foreign keys, and sequences).
   
   

   Copy Data Efficiently:
   

2. Swap the Tables (Rename)
   
   Rename the old table before replacing it:
   
3. Verify Data Integrity
   
   Before dropping the old table, verify that the row count matches:
   
4. Drop the Old Table After Confirmation
   

Advantages:

• Maintains data integrity during data copy (constraints ensure no invalid data).
• No need to re-add indexes or constraints later.
• Good for small-to-medium tables where performance impact is minimal.
  
  

Disadvantages:

• Slower data copy because every row must be validated against constraints.
• Indexes slow down inserts (each row update must update the index).
• Foreign keys lock referenced tables during insertions.
• More disk I/O and CPU usage due to constraint checking.
  
  

Performance Impact on Large Tables:

If the table has millions or billions of rows, inserting with indexes and constraints enabled can be extremely slow.

   2. Inserting Data without Table Constraints:

1. Prepare the New Table Without Indexes & Constraints
   
   This ensures fast data loading without the overhead of constraints and indexes
   
   

   This only copies the structure (columns & data types), skipping indexes, primary keys, and constraints.
   
   

   Load Data Efficiently:
   

   Option 1: Using INSERT (Slower, But Works Remotely)
   
   Tip: If data is large, consider inserting in batches using LIMIT and OFFSET to avoid locks.
   
   

   Option 2: Fastest Method – Using COPY
   
   If the database is on the same server, use COPY for the best speed
   

2. Create Indexes in Parallel
   
   a) Check indexes in original table:
   
   b) As per original table or your requirement you can create indexes, foreign keys as well as constraints.
   
   
3. Swap Tables with Minimal Downtime
   
   Once everything is ready, swap tables instantly.
   
   

   Why Rename Instead of Drop?
   
   If issues occur, the old table is still available for rollback
   
   

4. Verify Data Integrity
   
   Before dropping the old table, verify that the row count matches:
   
5. Drop the Old Table After Confirmation
   
   If everything matches, drop the old table
   

Advantages:

• Much faster data copy (COPY operates at raw disk speed).
• No locking issues since foreign keys aren’t enforced during the copy.
• Indexes can be built in parallel, reducing downtime.
• Less impact on performance (data is inserted without index overhead).
  
  

Disadvantages:

• Risk of data inconsistency if constraints are added after invalid data is inserted.
• Requires extra steps to rebuild indexes and constraints after copying.
  
  

Best Use Case: Large Tables:

This method is recommended for very large tables (100M+ rows) because it reduces downtime and improves performance.

Note: Copy-Swap-Drop method can significantly impact WAL (Write-Ahead Logging) files, especially for large tables. So, make sure you have sufficient storage at OS level.