SQL.How logical and physical schemas implemented on practice (in Postgres for ex.) ?

High-level idea (keep this in mind)

In PostgreSQL, you define the logical schema explicitly,
and PostgreSQL derives and manages the physical schema implicitly, with optional tuning knobs.

You declare intent → Postgres decides storage.

1️⃣ Logical schema in PostgreSQL (what you define)

This is everything you normally write in DDL.

Logical schema includes

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

What this expresses (logically)

• Entities: users, orders
• Relationships: orders → users
• Constraints:
  • Primary keys
  • Foreign keys
  • Uniqueness
• Column meaning & types

💡 No storage details yet.

2️⃣ What PostgreSQL creates physically (automatically)

From the logical schema above, PostgreSQL creates:

For each table

• A heap file on disk
• Internal row identifiers (ctid)
• System catalog entries

For each constraint

• Indexes (e.g. PK, UNIQUE)
• Internal dependency metadata

Example:

users
├── heap file (rows)
├── PK index (btree on id)
├── UNIQUE index (btree on email)

You did not specify:

• File layout
• Page size
• Index structure

Postgres handled it.

3️⃣ Physical schema in PostgreSQL (how data is stored & accessed)

This is where performance tuning lives.

Physical schema includes

Indexes

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Index types

USING btree
USING hash
USING gin
USING gist

Table partitioning

CREATE TABLE orders (
  ...
) PARTITION BY RANGE (created_at);

Storage parameters

ALTER TABLE orders SET (fillfactor = 70);

Tablespaces

CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd';

All of this:

• Does not change correctness
• Only affects performance & storage

4️⃣ Logical vs physical: same data, different behavior

Logical query (unchanged)

SELECT *
FROM orders
WHERE user_id = 42
  AND created_at >= now() - interval '7 days';

Physical execution (can change!)

• Seq scan → Index scan
• Index scan → Bitmap scan
• Bitmap scan → Partition pruning

Same query.
Different physical plan.

This is the power of declarative SQL.

5️⃣ PostgreSQL catalogs: where logic meets physics

Postgres stores everything in system tables:

• pg_class → tables & indexes
• pg_attribute → columns
• pg_constraint → PK / FK / UNIQUE
• pg_index → index metadata

Logical schema:

• What relationships exist

Physical schema:

• How those relationships are implemented internally

You rarely touch this directly — but tools do.

6️⃣ Migrations: how this works in real teams

Typical workflow

1. Logical change

ALTER TABLE orders ADD COLUMN status TEXT NOT NULL;

2. Physical optimization (optional, later)

CREATE INDEX idx_orders_status ON orders(status);

Important:

• Logical migrations are mandatory
• Physical migrations are optional & iterative

Senior teams separate them consciously.

7️⃣ Expand–Migrate–Contract pattern (production reality)

Example: adding a new constraint safely

Expand (logical)

ALTER TABLE users ADD COLUMN email_new TEXT;

Migrate (data)

UPDATE users SET email_new = email;

Contract (logical + physical)

ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_new TO email;
CREATE UNIQUE INDEX idx_users_email ON users(email);

Logical rules first → physical enforcement last.

8️⃣ Mental model (very important)

Think in layers:

Application
  ↓
Logical schema (tables, constraints, relations)
  ↓
Query planner
  ↓
Physical schema (indexes, partitions, files)
  ↓
Disk / Memory

You control:

• Logical schema directly
• Physical schema indirectly

Postgres controls:

• Execution plans
• Storage layout
• Optimization decisions

---

Common interview mistakes ❌

• Saying indexes are part of logical schema
• Mixing constraints with performance tuning
• Thinking partitioning changes correctness
• Optimizing physical schema before modeling logic

---

One-sentence interview answer (perfect)

In PostgreSQL, the logical schema is defined explicitly using tables, constraints, and relationships, while the physical schema consists of indexes, partitions, and storage parameters that PostgreSQL uses to store and access that logical model efficiently.