Performance Considerations in Denormalization

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

-- Step 1: Measure current table sizes
-- PostgreSQL example
 
SELECT 
    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_indexes_size(relid)) AS index_size,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(relid) DESC;
 
-- Example output:
-- table_name    | total_size | data_size | index_size | row_count
-----------------|------------|-----------|------------|----------
-- orders        | 1.2 GB     | 800 MB    | 400 MB     | 10000000
-- customers     | 45 MB      | 30 MB     | 15 MB      | 100000
-- products      | 120 MB     | 80 MB     | 40 MB      | 50000
 
 
-- Step 2: Calculate average row sizes
 
SELECT 
    'orders' AS table_name,
    AVG(pg_column_size(orders.*))::int AS avg_row_bytes
FROM orders
UNION ALL
SELECT 
    'customers',
    AVG(pg_column_size(customers.*))::int
FROM customers;
 
-- Example output:
-- table_name | avg_row_bytes
--------------|---------------
-- orders     | 80
-- customers  | 300
 
 
-- Step 3: Calculate denormalized row size
 
-- Identify columns to denormalize from customers
SELECT 
    column_name,
    AVG(LENGTH(customer_name::text))::int AS avg_bytes
FROM customers
CROSS JOIN LATERAL (
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_name = 'customers'
) cols;
 
-- Manual calculation for planned denormalization:
-- customer_name: avg 35 bytes
-- customer_email: avg 45 bytes  
-- customer_tier: 10 bytes
-- Total added: 90 bytes per order row
 
-- New order row: 80 + 90 = 170 bytes (2.125× increase)

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

-- Example: Selective Denormalization
-- Instead of duplicating all customer fields, only add what's needed
 
-- Bad: Full customer denormalization (high storage)
CREATE TABLE orders_full_denorm AS
SELECT 
    o.*,
    c.customer_name,
    c.email,
    c.phone,
    c.address_line1,
    c.address_line2,
    c.city,
    c.state,
    c.zip,
    c.country,
    c.created_at AS customer_created,
    c.tier,
    c.preferences,    -- JSONB, potentially large
    c.notes           -- TEXT, potentially large
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Result: Row size increases 4-5×
 
 
-- Good: Selective denormalization (controlled storage)
CREATE TABLE orders_smart_denorm AS
SELECT 
    o.*,
    c.customer_name,  -- Only fields needed for display
    c.tier            -- Only fields needed for filtering/sorting
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Result: Row size increases 20-30%
 
 
-- Better: Derived values only
ALTER TABLE orders ADD COLUMN customer_tier VARCHAR(20);
-- Just the tier for filtering, fetch name via customer_id when needed
-- Minimal storage overhead, still enables index-based tier filtering

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

-- Compression Example (PostgreSQL)
-- Enable compression on a denormalized table
 
-- Check current size
SELECT pg_size_pretty(pg_total_relation_size('orders_denormalized'));
-- Result: 1.2 GB
 
-- Enable compression using pg_lz compression via TOAST
-- (Automatic for variable-length columns > ~2KB)
 
-- For more aggressive compression, use extensions:
-- TimescaleDB (time-series), pg_columnar, or external tools
 
-- Manual compression approach: Archive with compression
CREATE TABLE orders_denorm_archive (
    year_month DATE,
    compressed_data BYTEA
);
 
-- Compress and archive old data monthly
INSERT INTO orders_denorm_archive
SELECT 
    DATE_TRUNC('month', order_date) AS year_month,
    compress_bytea(row_to_json(array_agg(o.*))::bytea) AS compressed_data
FROM orders_denormalized o
WHERE order_date < CURRENT_DATE - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', order_date);
 
-- Delete archived data from hot table
DELETE FROM orders_denormalized 
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';
 
-- Compression ratios: 3-5× typical for JSON/tabular data

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

-- PostgreSQL Storage Monitoring Queries
 
-- Table size trends (run daily, store results)
WITH size_snapshot AS (
    SELECT 
        current_timestamp AS snapshot_time,
        relname AS table_name,
        pg_total_relation_size(relid) AS total_bytes,
        pg_relation_size(relid) AS table_bytes,
        pg_indexes_size(relid) AS index_bytes,
        n_live_tup AS live_rows,
        n_dead_tup AS dead_rows
    FROM pg_stat_user_tables
    WHERE relname LIKE '%denorm%'
)
SELECT 
    snapshot_time,
    table_name,
    pg_size_pretty(total_bytes) AS total_size,
    pg_size_pretty(table_bytes) AS table_size,
    pg_size_pretty(index_bytes) AS index_size,
    live_rows,
    ROUND(100.0 * dead_rows / NULLIF(live_rows + dead_rows, 0), 2) AS dead_pct
FROM size_snapshot;
 
 
-- Index bloat detection
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans,
    ROUND(100.0 * pg_relation_size(indexrelid) / 
          NULLIF(pg_total_relation_size(tablename::regclass), 0), 2) AS index_pct
FROM pg_stat_user_indexes
WHERE tablename LIKE '%denorm%'
ORDER BY pg_relation_size(indexrelid) DESC;
 
 
-- Growth rate calculation (requires historical data)
SELECT 
    table_name,
    today.total_bytes - yesterday.total_bytes AS daily_growth_bytes,
    pg_size_pretty(today.total_bytes - yesterday.total_bytes) AS daily_growth,
    (today.total_bytes - yesterday.total_bytes) * 30 AS monthly_projection_bytes,
    pg_size_pretty((today.total_bytes - yesterday.total_bytes) * 30) AS monthly_projection
FROM storage_snapshots today
JOIN storage_snapshots yesterday 
    ON today.table_name = yesterday.table_name
    AND today.snapshot_date = yesterday.snapshot_date + 1
WHERE today.snapshot_date = CURRENT_DATE;