Physical Database Design and Performance

At the logical level, you think in terms of tables, columns, and relationships. At the physical level, the database must still respect that logical model, but it is ultimately stored as blocks^[1] on disk or SSD.

To manage data efficiently, the operating system and the RDBMS divide storage into fixed-size units (blocks or pages). Each block can hold part of a table, an index, or other internal structures. If a table or index is larger than a single block, the RDBMS spreads it across many blocks.

When related data is kept together in as few blocks as possible, the database engine can satisfy queries with fewer I/O operations. When data is scattered across many blocks, the engine must perform more random I/O, which slows performance—even on fast SSDs under heavy workloads.

In this illustration, three files share the disk, but each file’s blocks are lined up contiguously. Sequential reads are fast because the storage layer can read adjacent blocks with minimal movement or overhead.

Over time, as files (or database segments) are created, expanded, and removed, it becomes harder to keep every object in one contiguous stretch of blocks. The result is fragmentation: a single file or table ends up spread across non-adjacent blocks.

The same thing happens inside a database. As rows are inserted, updated, and deleted, free space appears in different places and the physical layout of a table becomes less orderly. Queries that once needed just a handful of blocks may now require many more physical reads.

The more frequently the storage engine must jump between blocks to assemble a rowset, the higher the latency and the lower the throughput. Physical database design is about making deliberate choices—indexes, clustering, and partitioning—that minimize unnecessary I/O and keep related data logically and physically close together.

Modern RDBMSs provide many tools to improve performance without changing the logical schema:

1. Indexing – Adds access paths that allow the optimizer to find rows with fewer reads.
2. Clustering – Influences how related rows are physically co-located on storage.
3. Partitioning – Splits large tables or indexes into smaller pieces for better manageability and selective access.

Together, these techniques let you:

• Reduce the number of blocks that must be read for common queries
• Align physical layout with real-world access patterns
• Scale to larger data volumes while maintaining acceptable response times

The following figures offer a high-level overview of these techniques. Later lessons in this module dive deeper into each one and show how to apply them in practice.

There is no single “perfect” physical design. Performance depends on:

1. The schema and workload. OLTP workloads, analytic workloads, and mixed workloads benefit from different indexing and partitioning strategies.
2. How the RDBMS implements joins and access paths. Two vendors may execute the same SQL in very different ways.
3. Data growth and change over time. A design that works well for thousands of rows may need to be revisited at tens or hundreds of millions of rows.

As a result, physical design is an iterative optimization process:

• Start from a well-normalized logical model.
• Apply indexing, clustering, and partitioning based on typical queries and SLAs.
• Measure performance using the RDBMS’s execution plans and monitoring tools.
• Refine the design as the workload and data volume evolve.

Whenever possible, evaluate alternative physical designs in a test or staging environment before applying them to production.

A standard database design methodology distinguishes three main phases:

1. Conceptual design – Capture business concepts and relationships.
2. Logical design – Map concepts into a relational schema (tables, keys, constraints).
3. Physical design – Decide how to implement that schema for performance and reliability.

Logical design is largely independent of the specific DBMS, but physical design is platform-specific. The RDBMS and storage engine determine:

• Available index types and partitioning strategies
• How clustering is expressed (e.g., clustered indexes, index-organized tables, table/partition placement)
• How statistics, caching, and compression affect query plans

The inputs to physical design include:

1. An ER or relational diagram
2. A relational schema definition
3. Supporting documentation such as a data dictionary and workload description

Physical design choices may also feed back into logical and application design—for example, denormalization or materialized views for heavy analytic workloads.

Logical database design answers “what data must we represent and how should it relate?” Physical database design answers “how do we store and access this data efficiently on a specific platform?”

Effective physical design requires:

• Understanding how the DBMS uses indexes, caches, and statistics
• Knowing how storage behaves (latency, throughput, concurrency characteristics)
• Reading execution plans and identifying I/O hot spots

Design activities are tightly connected:

1. Physical design may suggest minor changes to the logical schema (for example, adding surrogate keys or summary tables).
2. Application design may need to adapt to the chosen partitions, indexes, or clustering strategies.
3. Ongoing monitoring may reveal new access patterns that call for additional physical tuning.

The process of producing a description of how the database is implemented on secondary storage is known as physical design. A physical design describes:

1. The base relations and how they are stored
2. The file organizations and access methods used for tables and indexes
3. The indexes, partitions, clustering strategies, and related constraints and security measures

During this phase, the designer chooses among the options offered by a specific RDBMS to meet performance, scalability, and reliability goals. Because different systems implement indexes, clustering, and partitioning differently, physical design must be tailored to the target DBMS. Decisions taken here may feed back into logical and application design when significant performance improvements are possible.

The next lesson focuses on indexing in more detail and shows how index design affects query performance.

[1]block: The smallest unit of storage that the operating system or RDBMS addresses as a whole when reading from or writing to disk.