Denormalization Techniques

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Analysis queries to identify merge candidates
 
-- 1. Find one-to-one relationships (candidate for merge)
SELECT 
    tc.table_name AS referencing_table,
    ccu.table_name AS referenced_table,
    kcu.column_name AS fk_column,
    (SELECT COUNT(*) FROM information_schema.columns 
     WHERE table_name = tc.table_name) AS referencing_cols,
    (SELECT COUNT(DISTINCT fk_col.column_value) 
     FROM your_referencing_table) AS unique_fk_values,
    (SELECT COUNT(*) FROM your_referencing_table) AS total_rows
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu 
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
 
-- If unique_fk_values = total_rows, it's one-to-one (or one-to-zero/one)
 
-- 2. Analyze access patterns: Are tables always joined together?
SELECT 
    query_pattern,
    COUNT(*) AS execution_count,
    AVG(execution_time_ms) AS avg_time
FROM query_log
WHERE query_pattern LIKE '%orders%' AND query_pattern LIKE '%order_details%'
GROUP BY query_pattern
ORDER BY execution_count DESC;
 
-- 3. Measure join overhead for candidate relationships
EXPLAIN ANALYZE
SELECT o.*, od.*
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE o.order_id = 12345;
 
-- Compare with merged table performance:
EXPLAIN ANALYZE
SELECT * FROM orders_merged
WHERE order_id = 12345;
 
-- 4. Check for NULL ratio in optional relationships
SELECT 
    COUNT(*) AS total_rows,
    COUNT(related_table_fk) AS rows_with_relationship,
    (COUNT(*) - COUNT(related_table_fk))::FLOAT / COUNT(*) * 100 AS null_percentage
FROM main_table;
 
-- High null percentage suggests optional relationship - merge with caution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- Pattern 1: Full Merge - Mandatory One-to-One Relationship
-- Example: Users and User Profiles (every user has exactly one profile)
 
-- BEFORE: Separated tables
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(128) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE user_profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE REFERENCES users(user_id),
    display_name VARCHAR(100) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    timezone VARCHAR(50) DEFAULT 'UTC',
    notification_preferences JSONB,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- AFTER: Merged table
CREATE TABLE users_merged (
    user_id SERIAL PRIMARY KEY,
    
    -- From original users table
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(128) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- From user_profiles table (absorbed)
    display_name VARCHAR(100) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    timezone VARCHAR(50) DEFAULT 'UTC',
    notification_preferences JSONB,
    profile_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Migration from separated to merged
INSERT INTO users_merged (
    user_id, email, password_hash, created_at,
    display_name, bio, avatar_url, timezone, notification_preferences, profile_updated_at
)
SELECT 
    u.user_id, u.email, u.password_hash, u.created_at,
    p.display_name, p.bio, p.avatar_url, p.timezone, p.notification_preferences, p.updated_at
FROM users u
JOIN user_profiles p ON u.user_id = p.user_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- Pattern 2: Partial Merge - Optional One-to-One Relationship
-- Example: Customers and Premium Membership (not all customers are premium)
 
-- BEFORE: Separated tables
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE premium_memberships (
    membership_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL UNIQUE REFERENCES customers(customer_id),
    tier VARCHAR(20) NOT NULL,  -- 'silver', 'gold', 'platinum'
    started_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    benefits JSONB
);
 
-- AFTER: Merged with nullable premium columns
CREATE TABLE customers_merged (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Premium membership fields (NULL if not premium)
    is_premium BOOLEAN GENERATED ALWAYS AS 
        (premium_tier IS NOT NULL AND premium_expires_at > CURRENT_TIMESTAMP) STORED,
    premium_tier VARCHAR(20),  -- NULL if not premium
    premium_started_at TIMESTAMP,
    premium_expires_at TIMESTAMP,
    premium_benefits JSONB
);
 
-- Index for premium membership queries
CREATE INDEX idx_customers_premium ON customers_merged(is_premium, premium_tier)
WHERE is_premium = TRUE;
 
-- Migration
INSERT INTO customers_merged (
    customer_id, name, email, created_at,
    premium_tier, premium_started_at, premium_expires_at, premium_benefits
)
SELECT 
    c.customer_id, c.name, c.email, c.created_at,
    p.tier, p.started_at, p.expires_at, p.benefits
FROM customers c
LEFT JOIN premium_memberships p ON c.customer_id = p.customer_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

-- Pattern 3: Lookup Table Embedding
-- Example: Orders with Order Status (small, static lookup table)
 
-- BEFORE: Separated tables
CREATE TABLE order_statuses (
    status_id INT PRIMARY KEY,
    status_code VARCHAR(20) UNIQUE NOT NULL,
    display_name VARCHAR(50) NOT NULL,
    description TEXT,
    is_terminal BOOLEAN DEFAULT FALSE
);
 
-- Status table has only ~10 rows, rarely changes
INSERT INTO order_statuses VALUES
    (1, 'pending', 'Pending', 'Order received, awaiting processing', FALSE),
    (2, 'processing', 'Processing', 'Order being prepared', FALSE),
    (3, 'shipped', 'Shipped', 'Order dispatched', FALSE),
    (4, 'delivered', 'Delivered', 'Order received by customer', TRUE),
    (5, 'cancelled', 'Cancelled', 'Order was cancelled', TRUE);
 
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status_id INT REFERENCES order_statuses(status_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- AFTER: Embed lookup values directly (with ENUM or CHECK constraint)
CREATE TYPE order_status AS ENUM (
    'pending', 'processing', 'shipped', 'delivered', 'cancelled'
);
 
CREATE TABLE orders_merged (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status order_status NOT NULL DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Status metadata can be handled in application code or a config table
-- that's only joined for admin/reporting, not for every order query
 
-- Alternative: Keep status_id but embed common display data
CREATE TABLE orders_with_status_cache (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status_id INT NOT NULL,  -- Keep for referential integrity
    
    -- Embedded lookup values (duplicated from order_statuses)
    status_code VARCHAR(20) NOT NULL,
    status_display_name VARCHAR(50) NOT NULL,
    status_is_terminal BOOLEAN NOT NULL,
    
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Trigger to maintain embedded values
CREATE OR REPLACE FUNCTION embed_order_status()
RETURNS TRIGGER AS $$
BEGIN
    SELECT status_code, display_name, is_terminal
    INTO NEW.status_code, NEW.status_display_name, NEW.status_is_terminal
    FROM order_statuses
    WHERE status_id = NEW.status_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER order_status_embed_trigger
BEFORE INSERT OR UPDATE OF status_id ON orders_with_status_cache
FOR EACH ROW EXECUTE FUNCTION embed_order_status();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- Phase 1: Preparation - Create merged table alongside originals
 
-- 1. Create the merged table structure
CREATE TABLE users_merged (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(128) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Absorbed from user_profiles
    display_name VARCHAR(100),  -- Temporarily nullable for migration
    bio TEXT,
    avatar_url VARCHAR(500),
    timezone VARCHAR(50) DEFAULT 'UTC',
    notification_preferences JSONB,
    profile_updated_at TIMESTAMP
);
 
-- 2. Create indexes matching current access patterns
CREATE INDEX idx_users_merged_email ON users_merged(email);
CREATE INDEX idx_users_merged_display_name ON users_merged(display_name);
 
-- 3. Migrate existing data
INSERT INTO users_merged (
    user_id, email, password_hash, created_at,
    display_name, bio, avatar_url, timezone, 
    notification_preferences, profile_updated_at
)
SELECT 
    u.user_id, u.email, u.password_hash, u.created_at,
    p.display_name, p.bio, p.avatar_url, p.timezone,
    p.notification_preferences, p.updated_at
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id;
 
-- 4. Verify row counts
SELECT 
    (SELECT COUNT(*) FROM users) AS original_users,
    (SELECT COUNT(*) FROM user_profiles) AS original_profiles,
    (SELECT COUNT(*) FROM users_merged) AS merged_rows;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

-- Phase 2: Dual-Write - Maintain both structures during transition
 
-- Trigger to propagate writes to original tables into merged table
CREATE OR REPLACE FUNCTION sync_user_to_merged()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO users_merged (user_id, email, password_hash, created_at)
        VALUES (NEW.user_id, NEW.email, NEW.password_hash, NEW.created_at)
        ON CONFLICT (user_id) DO UPDATE SET
            email = EXCLUDED.email,
            password_hash = EXCLUDED.password_hash;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users_merged
        SET email = NEW.email,
            password_hash = NEW.password_hash
        WHERE user_id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM users_merged WHERE user_id = OLD.user_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER user_sync_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_to_merged();
 
-- Similar trigger for user_profiles
CREATE OR REPLACE FUNCTION sync_profile_to_merged()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE users_merged
        SET display_name = NEW.display_name,
            bio = NEW.bio,
            avatar_url = NEW.avatar_url,
            timezone = NEW.timezone,
            notification_preferences = NEW.notification_preferences,
            profile_updated_at = NEW.updated_at
        WHERE user_id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users_merged
        SET display_name = NULL,
            bio = NULL,
            avatar_url = NULL,
            timezone = 'UTC',
            notification_preferences = NULL,
            profile_updated_at = NULL
        WHERE user_id = OLD.user_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER profile_sync_trigger
AFTER INSERT OR UPDATE OR DELETE ON user_profiles
FOR EACH ROW EXECUTE FUNCTION sync_profile_to_merged();
 
-- Verify sync is working (run after some production traffic)
SELECT 
    um.user_id,
    um.email = u.email AS email_match,
    um.display_name = p.display_name AS name_match
FROM users_merged um
JOIN users u ON um.user_id = u.user_id
LEFT JOIN user_profiles p ON um.user_id = p.user_id
WHERE um.email != u.email OR um.display_name != p.display_name
LIMIT 100;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Phase 3: Switch and Cleanup
 
-- 1. Update application to read from merged table (application deployment)
-- All queries now go to users_merged instead of users + user_profiles
 
-- 2. After stable period with reads from merged table, drop sync triggers
DROP TRIGGER user_sync_trigger ON users;
DROP TRIGGER profile_sync_trigger ON user_profiles;
DROP FUNCTION sync_user_to_merged();
DROP FUNCTION sync_profile_to_merged();
 
-- 3. Rename tables for safety (don't delete yet)
ALTER TABLE users RENAME TO users_deprecated;
ALTER TABLE user_profiles RENAME TO user_profiles_deprecated;
ALTER TABLE users_merged RENAME TO users;
 
-- 4. Add NOT NULL constraints now that migration is complete
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
 
-- 5. Update sequences if needed
SELECT setval('users_user_id_seq', 
    (SELECT MAX(user_id) FROM users) + 1);
 
-- 6. After extended validation period (days/weeks), drop old tables
-- DROP TABLE users_deprecated CASCADE;
-- DROP TABLE user_profiles_deprecated CASCADE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- Query transformation examples after table merge
 
-- BEFORE: Join required
SELECT u.user_id, u.email, p.display_name, p.bio, p.timezone
FROM users u
JOIN user_profiles p ON u.user_id = p.user_id
WHERE u.email = 'jane@example.com';
 
-- AFTER: Simple select
SELECT user_id, email, display_name, bio, timezone
FROM users
WHERE email = 'jane@example.com';
 
-- BEFORE: Existence check requires subquery or join
SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1 FROM user_profiles p WHERE p.user_id = u.user_id);
 
-- AFTER: Direct filter on merged data
SELECT *
FROM users
WHERE display_name IS NOT NULL;  -- If optional relationship was merged
 
-- BEFORE: Aggregation across tables
SELECT 
    COUNT(DISTINCT u.user_id) AS total_users,
    COUNT(DISTINCT p.user_id) AS users_with_profiles,
    COUNT(CASE WHEN p.timezone != 'UTC' THEN 1 END) AS custom_timezone_users
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id;
 
-- AFTER: Single-table aggregation
SELECT 
    COUNT(*) AS total_users,
    COUNT(display_name) AS users_with_profiles,
    COUNT(CASE WHEN timezone != 'UTC' THEN 1 END) AS custom_timezone_users
FROM users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- Index strategy for merged tables
 
-- BEFORE: Indexes on separate tables
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_profiles_user ON user_profiles(user_id);
CREATE INDEX idx_profiles_display ON user_profiles(display_name);
 
-- AFTER: Consolidated indexes on merged table
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_display_name ON users(display_name);
 
-- Composite index for common combined queries
CREATE INDEX idx_users_email_display ON users(email, display_name);
 
-- Partial indexes for optional relationship data
CREATE INDEX idx_users_with_profiles ON users(user_id, display_name)
WHERE display_name IS NOT NULL;
 
-- Merged tables can have wider rows, impacting index sizes
-- Monitor index sizes post-migration:
SELECT 
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'users'
ORDER BY pg_relation_size(indexrelid) DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- Example: Why NOT to merge orders with order_audit_log
 
-- Orders table: Compact, frequently accessed
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);  -- ~100 bytes per row, 10M rows, frequently queried
 
-- Audit log: Large, rarely accessed
CREATE TABLE order_audit_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id),
    action VARCHAR(50) NOT NULL,
    changed_by VARCHAR(100),
    old_values JSONB,
    new_values JSONB,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);  -- ~2KB per row, 100M rows, rarely queried
 
-- WHY NOT MERGE:
-- 1. Audit has many rows per order (one-to-many, not one-to-one)
-- 2. Order queries would load massive audit data they don't need
-- 3. Audit has different retention (compliance: 7 years) vs orders (operational: 2 years)
-- 4. Audit writes constantly; orders are mostly stable after creation
 
-- Keep separated. If needed, add order summary fields as derived columns:
ALTER TABLE orders ADD COLUMN last_status_change TIMESTAMP;
-- This gives you the "merge benefit" for specific commonly-needed data
-- without the overhead of full merge