Logical schema vs Physical schema
Logical schema describes what the data model is.
Physical schema describes how it is stored and accessed.
That sentence alone already scores points.
1️⃣ Logical schema
What it is
The logical schema defines:
- Entities and attributes
- Relationships
- Constraints
- Business rules
It is DB-engine independent.
Focus
- Correctness
- Meaning
- Domain modeling
Example (logical)
User
- id
- email (unique)
- has many Orders
Order
- id
- user_id
- created_at
This says nothing about:
- Indexes
- Storage
- Performance
- Tablespaces
Typical elements
- Tables (as concepts)
- Columns and data types (abstract)
- Primary keys
- Foreign keys
- UNIQUE constraints
- Cardinality
2️⃣ Physical schema
What it is
The physical schema defines:
- How data is stored on disk
- How it is indexed
- How it is partitioned
- How it is optimized
It is DB-engine specific.
Focus
- Performance
- Scalability
- Storage efficiency
Example (physical)
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL
);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
PARTITION BY RANGE (created_at);
Typical elements
- Index types (B-tree, hash)
- Index order
- Partitioning
- Tablespaces
- Storage parameters
- Fillfactor, compression
Key differences (interview gold)
| Aspect | Logical schema | Physical schema |
|---|---|---|
| Purpose | Data meaning | Data storage |
| Level | Conceptual | Implementation |
| DB-specific | No | Yes |
| Changes when scaling | Rarely | Often |
| Concerned with | Correctness | Performance |
Why this distinction matters
1️⃣ You can change physical schema without changing logic
- Add indexes
- Change partitioning
- Tune storage
➡️ Application logic remains untouched.
2️⃣ Logical mistakes are fatal; physical mistakes are fixable
- Wrong relationships → broken domain
- Missing constraints → corrupted data
- Bad index → slow query (but correct)
3️⃣ Interview insight
Senior engineers think:
- Model correctness first (logical)
- Optimize later (physical)
Real-world example
Logical decision
“An order must belong to a user.”
Physical decisions
- Index on
orders.user_id - Partition orders by date
- Cluster table by PK
The rule doesn’t change — the implementation does.
Common interview mistakes ❌
- Mixing logical and physical concepts
- Saying indexes are part of logical schema
- Ignoring constraints as “physical”
- Designing for performance before correctness
One-sentence interview answer (perfect)
The logical schema defines the structure and rules of the data model independently of storage, while the physical schema defines how that model is implemented, stored, and optimized in a specific database system.
Bonus follow-up (very common)
“Can indexes change logical behavior?”
Answer:
No — they only affect performance, not correctness.