A logically perfect schema can be devastatingly slow in production. The elegant normalized tables, the carefully designed constraints, the meticulously documented relationships—all of this means nothing if queries take minutes instead of milliseconds. Physical design is where we bridge the gap between logical correctness and real-world performance.

Physical design operates at the intersection of database theory and computer architecture. It acknowledges that data lives on physical storage devices with specific characteristics: disks have seek times, memory has limited capacity, CPUs have cache hierarchies. Ignoring these realities produces databases that are theoretically beautiful but practically unusable.

This phase is also where the expertise of experienced database architects becomes most visible. Two architects given the same logical schema might produce physical designs with 100x performance differences. The difference lies in understanding access patterns, anticipating growth, and making informed trade-offs between competing concerns.

Physical design determines how the logical schema is implemented on physical storage. It introduces new structures (indexes, partitions) and may modify the logical schema itself (denormalization) to meet performance requirements.

Formal Definition:

Physical database design is the process of producing a description of the implementation of the database on secondary storage: the storage structures and access methods used to achieve efficient access to the data. It involves defining base tables, file organizations, indexes, integrity constraints, and security measures.

Physical Design Scope:

The Performance Triangle:

Physical design constantly balances three competing concerns:

                    Query Performance
                          /\
                         /  \
                        /    \
                       /      \
                      /________\
          Storage Efficiency — Write Performance

• Query Performance: How fast can we read data?
• Write Performance: How fast can we insert/update data?
• Storage Efficiency: How much space does data consume?

Trade-off Examples:

• Indexes improve query performance but slow writes and consume space
• Compression improves storage efficiency but adds CPU overhead to reads/writes
• Denormalization improves query performance but increases storage and complicates writes

Input to Physical Design:

The underlying storage structure determines how rows are physically arranged on disk. This affects full table scan performance, clustering of related rows, and index effectiveness.

Primary Storage Structures:

Heap vs. Clustered Trade-offs:

Heap Tables (No Clustering):

-- Most DBMS default; explicit in some
CREATE TABLE log_entries (
    entry_id    BIGINT PRIMARY KEY,
    timestamp   TIMESTAMP,
    message     TEXT
); -- Heap by default

Characteristics:

• Fastest inserts (append to end)
• Rows become fragmented after updates/deletes
• Full table scans read in physical order (may be random)
• Good for: staging, logging, temporary data

Clustered Tables (B-Tree Organized):

-- SQL Server clustered index
CREATE CLUSTERED INDEX idx_orders_date 
    ON orders(order_date);

-- PostgreSQL: order table by column
CLUSTER orders USING idx_orders_date;

Characteristics:

• Rows physically ordered by clustering key
• Range queries on clustering key extremely efficient
• Inserts may cause page splits (slower for random keys)
• Only ONE clustering key per table
• Good for: frequently range-scanned tables, time-series data

Column Store for Analytics:

Column stores organize data by column rather than row, dramatically improving analytical query performance:

Row Store (Traditional):

Page 1: [Row1: id=1, name='Alice', age=30, salary=50000]
        [Row2: id=2, name='Bob', age=25, salary=45000]
        ...

Column Store:

id column:     [1, 2, 3, 4, ...]
name column:   ['Alice', 'Bob', 'Carol', 'Dave', ...]
age column:    [30, 25, 35, 28, ...]
salary column: [50000, 45000, 60000, 55000, ...]

Column Store Advantages:

• Queries touching few columns read only those columns
• Better compression (similar values together)
• Vectorized processing (SIMD operations on column blocks)
• Ideal for: SELECT AVG(salary) FROM employees WHERE age > 30

Column Store Disadvantages:

• Single-row access requires reading multiple column segments
• Updates require modifying multiple column files
• Poor for OLTP workloads (many single-row operations)

Indexes are the most powerful tool for query optimization—and the most commonly misused. An index is an auxiliary data structure that provides fast access to rows based on column values, trading storage space and write performance for read performance.

Index Types:

Covering Indexes:

A covering index includes all columns needed by a query, eliminating the need to access the base table:

-- Query:
-- SELECT customer_id, order_date, total 
-- FROM orders WHERE customer_id = 123;

-- Covering index:
CREATE INDEX idx_orders_covering 
    ON orders(customer_id) INCLUDE (order_date, total);

The query can be satisfied entirely from the index—no table access needed. This is particularly valuable for:

• High-frequency queries
• Tables with large rows where accessing the table is expensive
• Read-heavy workloads

Index Selection Guidelines:

Partitioning divides large tables into smaller, more manageable pieces. From the application perspective, the table remains a single logical entity; physically, it comprises multiple partitions that can be managed, optimized, and queried independently.

Why Partition?

• Query Performance: Partition pruning skips irrelevant partitions entirely
• Maintenance Efficiency: Operations on individual partitions (backup, index rebuild) are faster
• Data Lifecycle: Old partitions can be archived or dropped without affecting others
• Parallel Processing: Different partitions can be processed simultaneously
• Storage Tiering: Move old partitions to cheaper storage

Partitioning Strategies:

Partition Key Selection:

The effectiveness of partitioning depends entirely on choosing the right partition key:

Partition Maintenance:

-- Adding new partitions (before data arrives)
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Detaching old partitions for archival
ALTER TABLE orders DETACH PARTITION orders_2022_01;
-- Now orders_2022_01 is standalone table, can be archived/dropped

-- Dropping old data (instant, no row-by-row delete)
DROP TABLE orders_2020_01;

Denormalization is the deliberate introduction of redundancy to improve query performance. It reverses normalization decisions when the cost of joins exceeds the cost of maintaining redundant data.

Important: Denormalization is not poor design—it is an optimization applied after normalization. Never skip normalization and call it denormalization.

When to Denormalize:

• Query performance requirements cannot be met with indexes alone
• Specific queries are critical path (user-facing, high frequency)
• Join overhead is measured bottleneck (not assumed)
• Write frequency is low relative to read frequency
• Data is relatively stable (redundant data won't diverge often)

Denormalization Techniques:

3. Merged Tables:

Combine frequently joined 1:1 related tables:

-- Normalized (2 tables):
employees(emp_id, name, hire_date, dept_id)
employee_details(emp_id, emergency_contact, tax_info, photo_url)

-- Denormalized (1 table):
employees(emp_id, name, hire_date, dept_id, 
          emergency_contact, tax_info, photo_url)

4. Summary Tables (Materialized Views):

Pre-computed query results refreshed periodically:

-- Materialized view for dashboard
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT DATE(order_date) as day,
       COUNT(*) as order_count,
       SUM(total) as revenue,
       AVG(total) as avg_order_value
FROM orders
GROUP BY DATE(order_date);

-- Refresh strategy
REFRESH MATERIALIZED VIEW daily_sales_summary;
-- Or: CONCURRENTLY for no locks

Physical design must account for not just current data volumes but projected growth. A schema that works perfectly at 1GB may completely collapse at 100GB if capacity wasn't considered.

Estimating Table Sizes:

Growth Model Considerations:

Capacity Planning Checklist:

Physical design must consider the full spectrum of performance factors: query execution, write efficiency, concurrency, and operational overhead.

Query Performance Factors:

Write Performance Optimization:

Indexes and constraints that improve read performance often hurt write performance:

Batch Insert Optimization:

-- Slow: single row inserts with constraint checks
INSERT INTO orders VALUES (...);
INSERT INTO orders VALUES (...);
-- ... 10,000 times

-- Fast: batch insert with deferred constraints
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
COPY orders FROM '/data/orders.csv';  -- Bulk load
COMMIT;  -- Constraints checked once

Index Maintenance Strategies:

• Drop indexes before bulk loads, rebuild after
• Use unlogged tables for staging data
• Consider fillfactor for tables with updates (leave room in pages)

Compression Trade-offs:

Physical design transforms logically correct schemas into performant production systems. It requires understanding hardware characteristics, workload patterns, and the trade-offs between competing optimization goals.

What's Next:

With physical design complete, we reach the final phase: implementation. Here, we translate all our design work into actual database objects, deploy to production environments, load initial data, and establish ongoing operational procedures.

The next page explores implementation: DDL script development, deployment strategies, data migration, testing, and the transition from design artifacts to running database systems.