Phase 02: Advanced SQL & Data Modeling¶

Chapter 6: Data Types & Hidden Architectural Costs¶

6.1 Column Alignment and Memory Padding (The 8-Byte Rule)¶

In PostgreSQL, every data type has an alignment requirement dictated by CPU hardware architectures (32-bit vs 64-bit). The engine requires that types of size \(N\) bytes begin at an offset divisible by \(N\).

char / bool: 1 byte (alignment: 1)
int2 (smallint): 2 bytes (alignment: 2)
int4 (integer): 4 bytes (alignment: 4)
int8 (bigint), float8, timestamptz, pointers: 8 bytes (alignment: 8)
varlena (variable length like text, varchar, jsonb): 4-byte header (alignment: 4)

Visualizing Struct Padding¶

Consider a poorly ordered table schema:

CREATE TABLE suboptimal_order (
    is_active    BOOLEAN,     -- 1 byte  | offset 0..0
    -- [PAD 7 BYTES TO REACH MULTIPLE OF 8]
    account_id   BIGINT,      -- 8 bytes | offset 8..15
    status_code  SMALLINT,    -- 2 bytes | offset 16..17
    -- [PAD 6 BYTES TO REACH MULTIPLE OF 8]
    created_at   TIMESTAMPTZ  -- 8 bytes | offset 24..31
);
-- Total row payload = 32 bytes (13 bytes wasted on padding = 40.6% overhead!)

Reordered optimally by descending alignment:

CREATE TABLE optimal_order (
    account_id   BIGINT,      -- 8 bytes | offset 0..7
    created_at   TIMESTAMPTZ, -- 8 bytes | offset 8..15
    status_code  SMALLINT,    -- 2 bytes | offset 16..17
    is_active    BOOLEAN      -- 1 byte  | offset 18..18
    -- [PAD 5 BYTES TO ALIGN TUPLE TO MULTIPLE OF 8]
);
-- Total row payload = 24 bytes (Saves 8 bytes per row!)

On a 100-million row table, this single optimization reclaims 800MB of disk and 800MB of shared_buffers cache space, directly reducing physical I/O overhead.

Inspecting Padding with `pg_column_size()`¶

SELECT pg_column_size(row(true, 100::bigint, 1::smallint, now())) AS suboptimal_bytes;
SELECT pg_column_size(row(100::bigint, now(), 1::smallint, true)) AS optimal_bytes;

6.2 Primary Keys: BIGINT Identity vs UUID v4 vs UUID v7¶

BIGINT (IDENTITY / BIGSERIAL):
Storage: 8 bytes.
Index behavior: Monotonically increasing. Every new key appends to the rightmost leaf page of the B-Tree index. Page splits = 0. Cache locality is near 100%.
Risk: Exposes business volume; vulnerable to enumeration attacks.
UUID v4 (Random):
Storage: 16 bytes (double BIGINT).
Index behavior: Completely random distribution. Inserts hit random B-Tree leaf pages throughout the entire index.
The Choke Point: As soon as the B-Tree index exceeds RAM, every single insert requires an 8KB disk read and an 8KB page write. Causes massive B-Tree fragmentation and early page splits.
UUID v7 (Time-Ordered):
Storage: 16 bytes.
Index behavior: Starts with a 48-bit UNIX millisecond timestamp followed by random bits. Monotonically increasing like BIGINT, but globally unique like UUID v4. Preserves index locality.

6.3 `TEXT` vs `VARCHAR(n)` vs `CHAR(n)`¶

Myth: VARCHAR(50) is faster than TEXT.
Reality: Internally, TEXT and VARCHAR share the exact same C struct (varlena). There is zero performance difference.
CHAR(n) is actively harmful: It pads spaces to fill \(n\) characters, wasting storage and requiring trailing whitespace truncation on reads.
Use TEXT with CHECK (length(col) <= 50) if validation is needed.

Chapter 7: Advanced Querying & Execution Mechanics¶

7.1 Common Table Expressions (CTEs): The Inlining Revolution¶

Prior to PostgreSQL 12, CTEs (WITH clauses) were strict optimization fences. The query engine always materialized the CTE into a temporary data structure before running the outer query.

WITH recent_orders AS (
    SELECT * FROM orders -- PG < 12 evaluated the ENTIRE table here!
)
SELECT * FROM recent_orders WHERE user_id = 42;

* Pre-PG 12: Even if orders had an index on user_id, the CTE did a full sequential scan of orders, materialized it in memory, and then filtered user_id = 42. * PG 12+: CTEs are automatically inlined like subqueries unless explicitly forced with AS MATERIALIZED.

7.2 Recursive CTEs (Graph & Hierarchy Traversal)¶

A recursive CTE contains an Anchor Member and a Recursive Member joined by UNION ALL.

-- Traversing an organization chart or bill of materials
WITH RECURSIVE org_tree AS (
    -- Anchor Member: Root CEO
    SELECT employee_id, manager_id, full_name, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Subordinates
    SELECT e.employee_id, e.manager_id, e.full_name, t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.employee_id
)
SELECT * FROM org_tree ORDER BY depth;

* Engine Execution: Postgres maintains a private internal working table. It loads the anchor result, feeds it into the recursive member, loads the next iteration into the working table, and repeats until the working table returns 0 rows. * Choke Point: Circular references in data cause infinite loops. Always add loop detection using an array: WHERE NOT (e.employee_id = ANY(path_array)).

7.3 `LATERAL` Joins (Per-Row Foreach Loop)¶

A LATERAL join allows a subquery in the FROM clause to reference columns of preceding tables in the join order—acting like an inline for-each loop.

-- Find the top 3 latest orders for EVERY customer
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
    LIMIT 3
) o;

Without LATERAL, achieving this requires expensive ROW_NUMBER() window functions over the entire orders table. With LATERAL and an index on orders(customer_id, order_date DESC), Postgres performs an Index Scan for 3 rows per customer.

Chapter 8: Window Functions & Analytics Internals¶

8.1 The Execution Pipeline¶

Window functions execute after WHERE, GROUP BY, and HAVING clauses, but before ORDER BY, DISTINCT, and LIMIT.

SELECT 
    dept_id, employee_id, salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_in_dept
FROM employee_salaries;

8.2 Window Framing Gotchas: `ROWS` vs `RANGE`¶

By default, OVER (ORDER BY col) uses: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

The Trap: RANGE considers duplicate values in ORDER BY to be ties, requiring Postgres to read ahead to find duplicate values. It incurs substantial memory buffer overhead.
The Optimization: If you just need a cumulative moving calculation, explicitly specify ROWS:
```
SUM(amount) OVER (
    ORDER BY transaction_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
```
ROWS reads purely physical tuples in sequence without looking ahead, providing a 2x-5x performance improvement.

Chapter 9: JSONB Mastery & Document Modeling¶

9.1 JSON vs JSONB (Internal Encoding)¶

JSON: Stored as raw text. Preserves whitespace, preserves key order, preserves duplicate keys. Must re-parse the string on every single query. Never use JSON.
JSONB: Decomposed binary format. Whitespace is stripped; keys are deduplicated and sorted. Parsing overhead occurs on write; reads and key lookups are near instantaneous.

9.2 JSONB Operators Cheat Sheet¶

data -> 'key': Extracts value as jsonb.
data ->> 'key': Extracts value as text.
data #> '{a, b, 0}': Navigates nested path as jsonb.
data @> '{"status": "active"}': Containment operator. Checks if the left JSONB contains the right JSONB. (Accelerated by GIN indexes).
data ? 'key': Checks if top-level key exists.

9.3 Indexing JSONB with GIN¶

CREATE TABLE audit_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    payload JSONB NOT NULL
);

-- Fast containment query acceleration:
CREATE INDEX idx_events_payload ON audit_events USING gin (payload);

-- Query using the GIN index:
EXPLAIN ANALYZE 
SELECT * FROM audit_events 
WHERE payload @> '{"action": "USER_LOGIN", "app": "ios"}';

* Choke Point: GIN indexes on large, arbitrary JSON documents can grow larger than the table itself. Use jsonb_path_ops for smaller, faster containment indexes: CREATE INDEX idx_events_fast ON audit_events USING gin (payload jsonb_path_ops);

9.4 The JSONB Update Trap¶

Postgres does not support in-place partial updates of JSONB sub-keys. If you update a single boolean inside a 500KB JSONB document: UPDATE docs SET payload = jsonb_set(payload, '{is_read}', 'true') WHERE id = 1; Postgres copies and writes the entire 500KB document to a new heap/TOAST tuple and writes WAL for all 500KB. * Architectural Rule: High-velocity mutation fields belong in native SQL columns; document-like payload attributes belong in JSONB.

Chapter 10: Table Partitioning (Declarative & Inheritance)¶

10.1 Partitioning Types (PostgreSQL 10+)¶

Range Partitioning: Subdivided by date or numerical ranges (e.g., orders_2026_01, orders_2026_02).
List Partitioning: Subdivided by discrete values (e.g., country code: orders_us, orders_eu).
Hash Partitioning: Subdivided by modulus/hash of an ID for horizontal distribution across tablespaces.

CREATE TABLE sensor_readings (
    sensor_id INT NOT NULL,
    reading_time TIMESTAMPTZ NOT NULL,
    value DOUBLE PRECISION
) PARTITION BY RANGE (reading_time);

CREATE TABLE sensor_readings_2026_10 
    PARTITION OF sensor_readings
    FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');

10.2 Partition Pruning¶

Postgres eliminates non-matching partitions during planning or execution: * Static Pruning (Planning time): Filter has constant literal (WHERE reading_time >= '2026-10-15'). The planner discards all partitions outside October 2026. * Dynamic Pruning (Execution time): Filter depends on a parameter or subquery (WHERE reading_time >= now() - interval '1 day'). Enabled by enable_partition_pruning = on.

10.3 Partitioning Choke Points¶

Over-partitioning: Creating thousands of partitions (e.g., partitioning by day for 10 years = 3,650 tables).
Every partition is a full OS file on disk.
Catalog cache memory explodes.
Query planning time grows linearly with partition count, turning a 2ms query into a 150ms planner bottleneck.
Global Indexes Do Not Exist: In PostgreSQL, every partition maintains its own local indexes. Unique constraints across the entire dataset must include the partition key.