GuidesTable Design

Schema Evolution

Schema evolution is the process of safely modifying database table structures to accommodate changing application requirements while preserving existing data and maintaining system availability. Tacnode provides robust tools for evolving schemas without disrupting production operations.

Why Schema Evolution Matters

In modern application development, database schemas must evolve to support:

  • New Feature Requirements: Adding fields for new functionality
  • Business Logic Changes: Modifying constraints or data types
  • Performance Optimizations: Restructuring for better query performance
  • Data Quality Improvements: Adding validation rules and constraints
  • Integration Needs: Adapting schemas for new system integrations

The challenge is making these changes safely without:

  • Losing existing data
  • Breaking application functionality
  • Causing extended downtime
  • Disrupting dependent database objects

Tacnode's Online Schema Changes

Tacnode implements Online Schema Change technology that allows most schema modifications to occur without locking tables or blocking operations. This means:

✅ Zero Downtime: Tables remain readable and writable during changes

✅ Non-Blocking: Other transactions continue normally

✅ Safe Rollback: Changes can be reverted if issues arise

✅ Large Table Support: Efficient handling of tables with millions of rows

Column Management

Adding Columns

Adding columns is one of the most common schema evolution operations and typically the safest.

-- Start with an existing table
CREATE TABLE customer_orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2)
);
 
-- Add a simple column
ALTER TABLE customer_orders ADD COLUMN status VARCHAR(20);
 
-- Add column with default value
ALTER TABLE customer_orders ADD COLUMN created_at TIMESTAMP DEFAULT NOW();
 
-- Add column with constraints
ALTER TABLE customer_orders ADD COLUMN priority INTEGER CHECK (priority BETWEEN 1 AND 5) DEFAULT 3;

Smart Default Handling

Tacnode optimizes column additions with deterministic defaults:

-- ✅ Efficient: Deterministic default (no table rewrite needed)
ALTER TABLE customer_orders ADD COLUMN order_source VARCHAR(50) DEFAULT 'web';
 
-- ✅ Efficient: Static timestamp
ALTER TABLE customer_orders ADD COLUMN schema_version INTEGER DEFAULT 1;
 
-- ⚠️ Less efficient: Non-deterministic default (requires row updates)
ALTER TABLE customer_orders ADD COLUMN created_timestamp TIMESTAMP DEFAULT NOW();

Pro Tip: For large tables, consider adding columns with deterministic defaults first, then updating values in batches if needed.

Adding Columns with Complex Constraints

-- Add column with multiple constraints
ALTER TABLE customer_orders 
ADD COLUMN shipping_address TEXT 
CHECK (LENGTH(shipping_address) > 10)
NOT NULL;
 
-- For existing data, you might need a multi-step approach:
-- Step 1: Add nullable column
ALTER TABLE customer_orders ADD COLUMN shipping_address TEXT;
 
-- Step 2: Populate existing rows
UPDATE customer_orders 
SET shipping_address = 'Address to be updated' 
WHERE shipping_address IS NULL;
 
-- Step 3: Add constraints
ALTER TABLE customer_orders 
ALTER COLUMN shipping_address SET NOT NULL,
ADD CHECK (LENGTH(shipping_address) > 10);

Removing Columns

Column removal requires careful consideration of dependencies and data preservation.

-- Step 1: Identify dependencies
-- Check for indexes, constraints, and references
SELECT 
    conname AS constraint_name,
    contype AS constraint_type
FROM pg_constraint 
WHERE conrelid = 'customer_orders'::regclass;
 
-- Step 2: Remove column (with CASCADE if needed)
ALTER TABLE customer_orders DROP COLUMN old_field;
 
-- Or remove with all dependencies
ALTER TABLE customer_orders DROP COLUMN old_field CASCADE;

Best Practices:

  1. Audit Dependencies: Check for foreign keys, indexes, views, and application code
  2. Backup Data: Consider exporting column data before removal
  3. Gradual Approach: Stop writing to the column first, then remove later
  4. Coordinate with Applications: Ensure no application code references the column
-- Example: Safe removal process
-- Step 1: Stop using the column in applications (deploy application changes)
-- Step 2: Make column nullable if it isn't already (optional safety step)
ALTER TABLE customers ALTER COLUMN deprecated_field DROP NOT NULL;
-- Step 3: Remove the column after confirming no issues
ALTER TABLE customers DROP COLUMN deprecated_field;

Modifying Column Types

Column type changes require careful validation and may involve data transformation.

-- Expanding size (usually safe)
ALTER TABLE customer_orders ALTER COLUMN status TYPE VARCHAR(50);
 
-- Increasing numeric precision
ALTER TABLE customer_orders ALTER COLUMN total_amount TYPE DECIMAL(12,2);
 
-- Text to larger text type
ALTER TABLE customers ALTER COLUMN description TYPE TEXT;
 
-- Converting with custom logic using USING clause
ALTER TABLE customer_orders 
ALTER COLUMN order_date TYPE TIMESTAMP 
USING order_date::timestamp + INTERVAL '12:00:00';
 
-- Converting string to integer with validation
ALTER TABLE products 
ALTER COLUMN price_text TYPE DECIMAL(10,2) 
USING CASE 
    WHEN price_text ~ '^[0-9]+\.?[0-9]*$' 
    THEN price_text::DECIMAL(10,2)
    ELSE NULL 
END;
 
-- Multi-step approach for complex changes:
-- Step 1: Add new column with desired type
ALTER TABLE customer_orders ADD COLUMN status_new VARCHAR(30);
 
-- Step 2: Populate new column with converted data
UPDATE customer_orders 
SET status_new = CASE 
    WHEN status = '1' THEN 'pending'
    WHEN status = '2' THEN 'processing'  
    WHEN status = '3' THEN 'completed'
    ELSE 'unknown'
END;
 
-- Step 3: Update application to use new column, then remove old column
ALTER TABLE customer_orders DROP COLUMN status;
ALTER TABLE customer_orders RENAME COLUMN status_new TO status;

Changing Default Values

Default value changes are immediate and only affect future INSERT operations.

-- Set new default value
ALTER TABLE customer_orders ALTER COLUMN priority SET DEFAULT 1;
 
-- Remove default value (makes it NULL)
ALTER TABLE customer_orders ALTER COLUMN priority DROP DEFAULT;
 
-- Update default for existing NULL values if needed
UPDATE customer_orders SET priority = 1 WHERE priority IS NULL;

Constraint Management

Adding Constraints

Constraint addition requires existing data to satisfy the new rules.

-- NOT NULL constraints: Check data first
SELECT COUNT(*) FROM customer_orders WHERE email IS NULL;
 
-- If no NULL values exist, add constraint
ALTER TABLE customer_orders ALTER COLUMN email SET NOT NULL;
 
-- If NULL values exist, handle them first
UPDATE customer_orders 
SET email = 'no-email@example.com' 
WHERE email IS NULL;
 
-- CHECK constraints with validation
ALTER TABLE customer_orders 
ADD CONSTRAINT check_positive_amount 
CHECK (total_amount > 0);
 
ALTER TABLE customer_orders 
ADD CONSTRAINT check_valid_status 
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));
 
-- Foreign key relationships
ALTER TABLE customer_orders 
ADD CONSTRAINT fk_customer_orders_customer 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
 
ALTER TABLE order_items 
ADD CONSTRAINT fk_order_items_order 
FOREIGN KEY (order_id) REFERENCES customer_orders(order_id) 
ON DELETE CASCADE ON UPDATE CASCADE;
 
-- UNIQUE constraints
ALTER TABLE customers 
ADD CONSTRAINT unique_customer_email 
UNIQUE (email);
 
ALTER TABLE customer_orders 
ADD CONSTRAINT unique_customer_date 
UNIQUE (customer_id, order_date);

Removing Constraints

Constraint removal requires knowing the constraint name.

-- Find all constraints on a table
SELECT 
    con.conname AS constraint_name,
    con.contype AS constraint_type,
    CASE con.contype
        WHEN 'c' THEN 'CHECK'
        WHEN 'f' THEN 'FOREIGN KEY'
        WHEN 'p' THEN 'PRIMARY KEY'
        WHEN 'u' THEN 'UNIQUE'
        WHEN 't' THEN 'TRIGGER'
        ELSE con.contype::text
    END AS constraint_description
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE rel.relname = 'customer_orders'
AND nsp.nspname = 'public';
 
-- Remove named constraint
ALTER TABLE customer_orders DROP CONSTRAINT check_positive_amount;
 
-- Remove NOT NULL constraint (special syntax)
ALTER TABLE customer_orders ALTER COLUMN optional_field DROP NOT NULL;
 
-- Remove constraint with CASCADE (removes dependent objects)
ALTER TABLE customer_orders DROP CONSTRAINT fk_customer_orders_customer CASCADE;

Renaming Operations

-- Rename columns
ALTER TABLE customer_orders RENAME COLUMN total_amount TO order_total;
 
-- Rename tables
ALTER TABLE customer_orders RENAME TO orders;
 
-- Consider the impact on application code, views, stored procedures, reports, and API responses

Best Practices

Pre-Change Checklist:

  • Document the change and its business justification
  • Identify all affected database objects (views, functions, triggers)
  • Review application code dependencies
  • Plan rollback strategy
  • Schedule maintenance window if needed
  • Prepare monitoring and validation queries
-- Find objects that depend on a table
SELECT DISTINCT
    dependent_ns.nspname as dependent_schema,
    dependent_view.relname as dependent_view, 
    source_ns.nspname as source_schema,
    source_table.relname as source_table
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace 
WHERE source_table.relname = 'your_table_name';
 
-- Progressive approach: Multi-phase changes
-- Phase 1: Additive changes only (safe)
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
-- Phase 2: Application deployment to use new field
-- Phase 3: Data migration to populate new field
UPDATE users SET phone_number = extract_phone(contact_info) 
WHERE phone_number IS NULL;
-- Phase 4: Add constraints after data is clean
ALTER TABLE users ALTER COLUMN phone_number SET NOT NULL;
-- Phase 5: Remove old field after confirming everything works
ALTER TABLE users DROP COLUMN contact_info;
 
-- Testing in development
CREATE TABLE test_schema_change AS SELECT * FROM production_table LIMIT 10000;
ALTER TABLE test_schema_change ADD COLUMN new_field INTEGER DEFAULT 1;
SELECT COUNT(*), COUNT(new_field) FROM test_schema_change;
 
-- Monitoring changes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE tablename = 'your_table';
 
-- Monitor query performance
SELECT query, mean_time, calls, rows 
FROM pg_stat_statements 
WHERE query LIKE '%your_table%'
ORDER BY mean_time DESC;

Rollback Strategies:

  • Keep backup of original table structure
  • Document reverse operations for each change
  • Test rollback procedures in development environment
  • Consider using transactions for atomic multi-step changes

Change Documentation Template:

Schema Change: [Brief Description]

Date: YYYY-MM-DD
Tables Affected: table_name
Change Type: ADD COLUMN / ALTER COLUMN / etc.
Business Justification: Why this change is needed
Dependencies: Applications, reports, ETL processes affected
Rollback Plan: How to reverse the change if needed
**Testing**: What testing was performed
**Performance Impact**: Expected impact on queries/operations

Summary

Effective schema evolution is crucial for maintaining healthy, adaptable database systems. Key principles include:

Safety First:

  • Always backup data before major changes
  • Test changes in development environments
  • Plan rollback strategies for every modification
  • Use Tacnode's online schema change capabilities

Gradual Implementation:

  • Prefer additive changes over destructive ones
  • Implement complex changes in multiple phases
  • Coordinate schema changes with application deployments
  • Monitor system performance after changes

Best Practices:

  • Document all changes and their business justification
  • Communicate changes to all stakeholders
  • Use consistent naming conventions
  • Regular review and cleanup of unused schema elements

By following these guidelines and leveraging Tacnode's advanced schema evolution capabilities, you can safely adapt your database structures to meet evolving application requirements while maintaining system reliability and performance.

Previous

Index Design

Next

Data Types

On this page