Data Systems Architecture

Data Systems Architecture

Overview

Core principle: Good data system architecture balances reliability (correct operation under faults), scalability (handling growth gracefully), and maintainability (enabling productive change over time). Every architectural decision involves trade-offs between these concerns.

This skill synthesizes knowledge from three foundational texts:

• Designing Data-Intensive Applications (Kleppmann) - distributed systems, storage engines, scaling
• The Art of PostgreSQL (Fontaine) - PostgreSQL-specific patterns, SQL as programming
• PostgreSQL Query Optimization (Dombrovskaya et al.) - execution plans, performance tuning

When to Use

Navigation

Reference Files (Load as needed)

01-foundational-principles.md    - Reliability/Scalability/Maintainability, load parameters
02-data-modeling.md              - Normalization, denormalization, schema design patterns
03-storage-engines.md            - B-trees, LSM-trees, OLTP vs OLAP, PostgreSQL internals
04-indexing.md                   - Index types, compound indexes, covering indexes, maintenance
05-scaling-patterns.md           - Replication, partitioning, sharding strategies
06-transactions-concurrency.md   - ACID, isolation levels, MVCC, locking patterns
07-application-integration.md    - ORM pitfalls, N+1, business logic placement, batch processing

Quick Decision Framework

New system design?
├─ Yes → Read 01, then 02 for data model
└─ No → What's the problem?
         ├─ "Queries are slow" → Read 04 (indexing) + 03 (storage patterns)
         ├─ "Data is inconsistent" → Read 02 (modeling) + 06 (transactions)
         ├─ "Can't handle the load" → Read 05 (scaling) + 03 (OLTP vs OLAP)
         ├─ "App makes too many queries" → Read 07 (N+1, ORM patterns)
         └─ "Race conditions/deadlocks" → Read 06 (concurrency)

Core Concepts (Quick Reference)

The Three Pillars

Data Model Selection

OLTP vs OLAP

Index Type Quick Reference

Isolation Levels

Design Checklist

Before finalizing a data architecture:

• [ ] Identified load parameters (read/write ratio, data volume, latency requirements)
• [ ] Chose appropriate data model (relational/document/graph hybrid?)
• [ ] Normalized to 3NF first, denormalized only with measured justification
• [ ] Designed indexes for actual query patterns (not hypothetical)
• [ ] Considered 10x growth scenario
• [ ] Established isolation level requirements
• [ ] Defined where business logic lives (app vs DB vs both)
• [ ] Planned for operations (backups, monitoring, migrations)

References

• Kleppmann, M. Designing Data-Intensive Applications (O'Reilly, 2017)
• Fontaine, D. The Art of PostgreSQL (2nd ed., 2020)
• Dombrovskaya, H., Novikov, B., Bailliekova, A. PostgreSQL Query Optimization (Apress, 2021)

Foundational Principles of Data Systems Architecture

Overview

This chapter establishes the core concepts that underpin all data system architecture decisions. Before diving into specific technologies or optimization techniques, you must understand the fundamental trade-offs that shape every data-intensive application.

Key insight: There is no "best" database design, only trade-offs. Every architectural choice involves sacrificing something to gain something else. Understanding these trade-offs is the foundation of good data systems thinking.

---

The Three Pillars: Reliability, Scalability, Maintainability

Every data system must balance three fundamental concerns. These are not features to add later—they are architectural properties that must be designed in from the start.

Reliability

Definition: The system continues to work correctly (performing the correct function at the desired level of performance) even in the face of adversity—hardware faults, software errors, and human mistakes.

Reliability means more than just "it works." A reliable system:

• Performs the function the user expected
• Tolerates the user making mistakes or using the software in unexpected ways
• Maintains good performance under expected load and data volume
• Prevents unauthorized access and abuse

The distinction between faults and failures is critical:

The goal is not to prevent all faults (impossible), but to design fault-tolerance mechanisms that prevent faults from causing failures. You build reliable systems from unreliable parts.

Types of Faults

Hardware Faults

• Hard disk crash (MTTF: 10-50 years; with 10,000 disks, expect one failure per day)
• RAM errors
• Power grid blackouts
• Network cable disconnections

Traditional response: Add redundancy (RAID, dual power supplies, diesel generators). Modern approach: Design software to tolerate entire machine loss through redundancy at the system level.

Software Errors Unlike hardware faults (which are random and independent), software bugs are systematic and correlated—they can take down many nodes simultaneously.

Common patterns:

• Bugs triggered by unusual inputs (leap second bugs, etc.)
• Runaway processes consuming shared resources
• Cascading failures where one component's failure triggers others
• Services slowing down and becoming unresponsive

Software faults often lie dormant until triggered by unusual circumstances. They exploit assumptions about the environment that stop being true.

Human Errors Configuration errors by operators are the leading cause of outages—hardware plays a role in only 10-25% of failures.

Mitigation strategies: 1. Design interfaces that make "the right thing" easy and "the wrong thing" hard 2. Provide sandbox environments for safe experimentation with real data 3. Test thoroughly at all levels (unit, integration, manual) 4. Enable quick rollback of configuration changes 5. Implement detailed monitoring and alerting 6. Roll out changes gradually (canary deployments)

Scalability

Definition: The ability to cope with increased load gracefully. Scalability is not a binary property—you cannot say "X is scalable" without context. Instead, ask: "If load grows in a particular way, what are our options for coping?"

Describing Load

Before discussing scalability, you must describe current load using load parameters. The best choice of parameters depends on your system architecture:

• Requests per second to a web server
• Ratio of reads to writes in a database
• Number of simultaneously active users
• Hit rate on a cache
• Size of working data set relative to memory

Example: Twitter's Load Parameters (2012)

The scaling challenge wasn't tweet volume—it was fan-out. Each user follows many people and is followed by many. Some users have 30 million followers. A single tweet can require 30 million writes to home timeline caches.

Load distribution matters: The average follower count hides that some users have orders of magnitude more followers than others. Your architecture must handle the outliers, not just the average.

Describing Performance

Once you've described load, investigate what happens when it increases:

1. If load increases with fixed resources, how does performance degrade? 2. To maintain performance under increased load, how many resources must you add?

Key metrics differ by system type:

Response Time vs. Latency:

• Response time: What the client sees (service time + network delays + queueing delays)
• Latency: Duration a request waits before being handled (the "waiting" portion)

Use percentiles, not averages:

Averages hide the distribution of actual user experience. A service with 200ms average response time might have 5% of users waiting 1.5+ seconds.

Why high percentiles matter: Your slowest customers are often your most valuable—they have the most data because they're power users. Amazon observed that 100ms latency increase reduces sales by 1%.

Tail latency amplification: If a request requires multiple backend calls, the probability of experiencing slow response increases. With 5 parallel calls each having 1% chance of being slow, you have ~5% chance of the overall request being slow.

Approaches for Coping with Load

Stateless vs. stateful scaling:

• Stateless services: Straightforward to distribute
• Stateful data systems: Introduces significant complexity; traditionally kept on single node until forced to distribute

There is no generic scalable architecture. The architecture for 100,000 requests/second of 1KB each looks completely different from 3 requests/minute of 2GB each—even though throughput is identical.

Maintainability

Definition: The ease with which the system can be operated, understood, and modified over time.

Most software cost is in ongoing maintenance, not initial development. Maintainability has three components:

Operability (Making Life Easy for Operations)

Operations teams must:

• Monitor system health and restore service quickly
• Track down causes of problems
• Keep software and platforms up to date
• Anticipate and prevent future problems
• Maintain security as configuration changes

Good operability means:

• Visibility into runtime behavior with good monitoring
• Support for automation and integration with standard tools
• No dependency on individual machines (allow maintenance without downtime)
• Good documentation and predictable behavior
• Sensible defaults with override capability

Simplicity (Managing Complexity)

Complexity symptoms:

• Explosion of state space
• Tight coupling between modules
• Tangled dependencies
• Inconsistent naming and terminology
• Hacks for performance problems
• Special-casing to work around issues

Complexity increases bug risk—hidden assumptions and unexpected interactions are easily overlooked in complex systems.

The antidote is abstraction. Good abstractions hide implementation details behind clean facades. SQL is an abstraction over disk structures, memory management, and concurrent access. High-level languages abstract over machine code.

Evolvability (Making Change Easy)

Requirements constantly change: new use cases, business priorities, user requests, regulatory requirements, and growth patterns.

Design for change through:

• Agile practices (TDD, refactoring)
• Good abstractions that isolate change
• Clear module boundaries
• Test coverage that enables confident modification

---

Think Like a Database

This concept from PostgreSQL Query Optimization is essential for writing performant queries.

Core insight: To optimize effectively, you must understand how the database engine processes your query. Imagine you have to execute the query yourself, manually, against the data on disk. What would you have to do?

Declarative vs. Imperative Thinking

SQL is a declarative language: you describe what result you want, not how to obtain it. This is fundamentally different from imperative languages where you specify the sequence of steps.

The trap: Developers naturally think imperatively. When approaching a query, they think about steps: 1. First, find all frequent flyers with level 4 2. Then, get their account numbers 3. Then, find their bookings 4. Then, filter by date and departure...

This thinking produces nested subqueries and CTEs that lock in a specific execution order, preventing the optimizer from choosing better approaches.

Example: Imperative Style (Anti-pattern)

WITH bk AS (
  WITH level4 AS (
    SELECT * FROM account WHERE frequent_flyer_id IN (
      SELECT frequent_flyer_id FROM frequent_flyer WHERE level = 4
    )
  )
  SELECT * FROM booking WHERE account_id IN
    (SELECT account_id FROM level4)
)
SELECT * FROM bk WHERE bk.booking_id IN (
  SELECT booking_id FROM booking_leg WHERE leg_num = 1
    AND is_returning IS false
    AND flight_id IN (
      SELECT flight_id FROM flight
      WHERE departure_airport IN ('ORD', 'MDW')
        AND scheduled_departure::DATE = '2020-07-04'
    )
)

This forces the database to follow your specified order, even if it's suboptimal.

Example: Declarative Style (Correct)

SELECT count(*) FROM
  booking bk
  JOIN booking_leg bl ON bk.booking_id = bl.booking_id
  JOIN flight f ON f.flight_id = bl.flight_id
  JOIN account a ON a.account_id = bk.account_id
  JOIN frequent_flyer ff ON ff.frequent_flyer_id = a.frequent_flyer_id
  JOIN passenger ps ON ps.booking_id = bk.booking_id
WHERE level = 4
  AND leg_num = 1
  AND is_returning IS false
  AND departure_airport IN ('ORD', 'MDW')
  AND scheduled_departure BETWEEN '2020-07-04' AND '2020-07-05'

This tells the database what you need, allowing the optimizer to choose the best execution order based on statistics and indexes.

Why Two Equivalent Queries Can Have Different Performance

Consider these two queries that return identical results:

-- Query A: BETWEEN operator
SELECT flight_id, departure_airport, arrival_airport
FROM flight
WHERE scheduled_arrival BETWEEN '2020-10-14' AND '2020-10-15';

-- Query B: Cast to date
SELECT flight_id, departure_airport, arrival_airport
FROM flight
WHERE scheduled_arrival::date = '2020-10-14';

Query A can use an index on scheduled_arrival. Query B cannot—the cast transforms every value, preventing index usage. Same result, vastly different performance.

The lesson: Understanding how the database engine works is not optional. It determines whether your application performs well or collapses under load.

---

OLTP vs. OLAP: Different Optimization Strategies

Understanding whether you're optimizing for OLTP or OLAP fundamentally changes your approach.

Characteristics

Query Classification: Short vs. Long

Short queries (typical in OLTP):

• Access small number of rows (ideally single digits to hundreds)
• Should complete in milliseconds
• Must be highly concurrent
• Index-driven

Long queries (typical in OLAP):

• Access large portions of data
• Completion in seconds or minutes is acceptable
• Full table scans may be optimal
• Aggregate-focused

Critical insight: The length of the SQL statement text has nothing to do with whether a query is "short" or "long." A one-line query that scans millions of rows is a long query. A multi-page query that retrieves one row by primary key is a short query.

Optimization Differences

---

Setting SMART Optimization Goals

Optimization without defined goals leads to wasted effort. Use the SMART framework:

Response Time Varies by Context

What is "good enough" depends entirely on context:

Beyond Response Time

Other optimization goals may include:

• Throughput: Maximize transactions per second (important for service providers)
• Resource utilization: Minimize hardware costs while maintaining performance
• Consistency: Ensure data correctness under concurrent access
• Availability: Minimize downtime

---

Data Systems Thinking

Modern applications rarely use a single tool for all data needs. Instead, they compose multiple specialized components:

• Databases for persistent storage
• Caches for accelerating reads
• Search indexes for text queries
• Message queues for async processing
• Stream processors for real-time events
• Batch processors for periodic analysis

When you combine tools, you become a data system designer, not just an application developer. Your composite system must provide guarantees:

• Cache correctly invalidated on writes
• Indexes kept in sync with source data
• Consistent results across components

Data-Intensive vs. Compute-Intensive

Data-intensive applications: Data is the primary challenge—quantity, complexity, or speed of change. Most modern applications fall here.

Compute-intensive applications: CPU cycles are the bottleneck.

This book focuses on data-intensive applications, where the limiting factor is data management, not computation.

---

Principles for Data System Design

1. Start with Load Parameters

Before designing anything, identify your load parameters:

• What are your read/write ratios?
• How much data are you storing?
• What are your latency requirements?
• What are your access patterns?
• What's your 10x growth scenario?

2. Design for the Outliers, Not the Average

The Twitter example teaches this: average follower count doesn't predict system behavior. Celebrity accounts with millions of followers drive the architecture.

Identify your outliers:

• What are your largest records?
• Who are your most active users?
• What are your most complex queries?

3. Make Trade-offs Explicit

Every decision has consequences. Document them:

• "We're choosing eventual consistency for higher availability"
• "We're denormalizing this table for read performance at the cost of write complexity"
• "We're partitioning by customer_id, which makes cross-customer queries expensive"

4. Measure, Don't Guess

Before optimizing, measure:

• Use percentiles, not averages
• Monitor in production, not just test environments
• Track trends over time

5. Question Requirements

Before optimizing a slow report, ask: "Is this report still needed?" One organization cut 40% of reporting server traffic by questioning report purposes.

---

Common Mistakes

---

Key Takeaways

1. Reliability, Scalability, Maintainability are not features—they are properties that must be designed in from the start.

2. Faults are inevitable; failures are preventable. Build systems that tolerate component faults without failing as a whole.

3. Scalability requires understanding your load. Define your load parameters before discussing how to scale.

4. Use percentiles, not averages to understand real user experience.

5. Think like a database. Understand how the engine processes queries to write performant SQL.

6. Declarative thinking enables optimization. Let the query planner do its job by describing what you want, not how to get it.

7. OLTP and OLAP require different strategies. Know which you're optimizing for.

8. Set SMART optimization goals. Vague goals lead to wasted effort.

9. You are a data system designer. When combining multiple tools, you're responsible for the guarantees of the whole system.

---

References

• Kleppmann, M. Designing Data-Intensive Applications, Chapter 1
• Dombrovskaya, H. et al. PostgreSQL Query Optimization, Chapter 1
• Fontaine, D. The Art of PostgreSQL, Introduction

Data Modeling for Data-Intensive Applications

Overview

Data modeling is arguably the most important skill for building data-intensive applications. A good data model makes every query easy to write, keeps data clean, and enables the system to evolve gracefully. A poor model creates endless workarounds, performance problems, and maintenance headaches.

Core insight: Your data model determines what operations are easy and what are hard. The model should reflect how your application actually uses data, not how you initially imagine it might.

---

Data Model Fundamentals

The Layered Nature of Data

Every application works through layers of data abstraction:

1. Real world: People, organizations, events, transactions 2. Application model: Objects, data structures, APIs 3. Database model: Tables, documents, graphs, columns 4. Storage model: Bytes on disk, memory structures, indexes 5. Hardware: Electrical signals, magnetic fields, light pulses

Each layer hides complexity from the layer above. Your job is to choose the right database model for your application layer's needs.

Why Data Model Choice Matters

Every data model embodies assumptions about how data will be used:

Key question: What operations does your application need to perform frequently? Model for those operations.

---

Relational Model

The relational model (SQL) organizes data into relations (tables), where each relation is an unordered collection of tuples (rows).

Strengths of Relational Model

When to Use Relational Model

• Data has complex relationships (many-to-many)
• Need for strong consistency guarantees
• Query patterns are varied or unknown in advance
• Multiple applications access the same data
• Need for ACID transactions

The Query Optimizer Advantage

In relational databases, the query optimizer automatically decides:

• Which parts of the query to execute first
• Which indexes to use
• How to join tables

This is a fundamental advantage: You don't need to manually specify access paths. Declare what you want, and the database figures out how to get it efficiently.

If you want to query data in new ways, just declare a new index. Queries automatically use the most appropriate indexes without code changes.

---

Document Model

Document databases store data as self-contained documents (typically JSON or similar).

Strengths of Document Model

When to Use Document Model

• Data is naturally hierarchical (tree structure)
• Documents are self-contained (rarely need joins)
• Schema changes frequently
• One-to-many relationships dominate
• Application objects map naturally to documents

The Locality Advantage

When you need to display all information about an entity at once (like a user profile), document models win:

{
  "user_id": 251,
  "name": "Jane Smith",
  "positions": [
    {"title": "CTO", "company": "Acme Inc"},
    {"title": "Engineer", "company": "StartupCo"}
  ],
  "education": [
    {"school": "MIT", "degree": "PhD"}
  ]
}

One query retrieves everything. In a relational model, this requires joins across multiple tables.

Document Model Limitations

Poor join support: If you need many-to-many relationships, document databases become awkward. You either:

• Denormalize data (creating update complexity)
• Emulate joins in application code (slower, more complex)

Data interconnection tendency: Even if your initial model fits documents well, data often becomes more interconnected as features are added.

---

Graph Model

Graph databases use nodes (entities) and edges (relationships) as their fundamental structures.

When to Use Graph Model

• Highly connected data
• Relationship traversal is the primary query pattern
• Variable or recursive relationship depth
• Finding paths, shortest routes, patterns

Examples

• Social networks (who knows whom)
• Fraud detection (connected suspicious transactions)
• Recommendation engines (similar items/users)
• Knowledge graphs

---

Schema Design: Schema-on-Write vs Schema-on-Read

Schema-on-Write (Traditional Relational)

The schema is explicit and enforced at write time. All data must conform to the schema.

Advantages:

• Data is always consistent with schema
• Errors caught at write time
• Clear documentation of structure
• Optimizer can use schema knowledge

Disadvantages:

• Schema changes can be expensive
• Less flexible for heterogeneous data

Schema-on-Read (Document/NoSQL)

No schema enforcement at write time. Structure is interpreted when data is read.

Advantages:

• Flexibility for varied data structures
• Easy to store heterogeneous data
• No migration needed for new fields

Disadvantages:

• Application must handle missing/unexpected fields
• No database-level integrity guarantees
• Schema still exists (implicitly in code)

Practical Guidance

---

Normalization: The Foundation

What is Normalization?

Normalization means structuring data to eliminate redundancy. The key idea: store each piece of information in exactly one place, and reference it by ID elsewhere.

Why Normalize?

Example: Phone Numbers

Single-table design (denormalized):

account(id, name, home_phone, work_phone, cell_phone)

Two-table design (normalized):

account(id, name)
phone(id, account_id, phone_type, number, is_primary)

Which is better? It depends on usage:

The Normalization Debate

Many developers argue endlessly about normalization vs. denormalization. Here's the practical approach:

1. Start normalized (typically 3NF) 2. Measure actual performance 3. Denormalize only with measured justification 4. Document the trade-off explicitly

Premature denormalization is a common mistake. Normalize first, then optimize based on real data.

---

Practical Schema Design Patterns

IDs vs. Plain Text

Always use IDs for:

• Values that might change (city names, company names)
• Values that need consistency (avoid typos)
• Values that need localization
• Values used for joins

-- BAD: Plain text that might change
SELECT * FROM users WHERE city = 'Greater Seattle Area';

-- GOOD: ID that references canonical value
SELECT * FROM users WHERE region_id = 91;

The advantage: IDs never need to change. If "Greater Seattle Area" is renamed, you update one row in the regions table, not thousands of user rows.

One-to-Many Relationships

Pattern: Parent table with child table referencing parent ID.

CREATE TABLE booking (
    booking_id SERIAL PRIMARY KEY,
    account_id INT REFERENCES account(id),
    booking_date DATE
);

CREATE TABLE booking_leg (
    leg_id SERIAL PRIMARY KEY,
    booking_id INT REFERENCES booking(booking_id),
    flight_id INT REFERENCES flight(id),
    leg_num INT
);

Many-to-Many Relationships

Pattern: Junction/bridge table connecting two entities.

CREATE TABLE user_role (
    user_id INT REFERENCES users(id),
    role_id INT REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

Surrogate Keys vs. Natural Keys

Surrogate key: Artificial identifier (auto-increment, UUID) Natural key: Meaningful business identifier (email, SSN, ISBN)

Recommendation: Use surrogate keys as primary keys, but add unique constraints on natural keys:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    -- ...
);

---

Anti-Patterns to Avoid

Entity-Attribute-Value (EAV)

The pattern:

CREATE TABLE attributes (
    entity_id INT,
    attribute_name VARCHAR(100),
    attribute_value TEXT
);

Why it's problematic:

• No type safety
• Can't use constraints
• Queries become complex and slow
• Schema is hidden in data

When it's acceptable: True arbitrary key-value metadata. Even then, consider PostgreSQL's jsonb type instead.

Multiple Values in One Column

Bad:

-- Comma-separated values in one column
INSERT INTO users (name, skills) VALUES ('Jane', 'python,sql,java');

Problems:

• Can't index individual values efficiently
• Complex queries to search for single value
• Difficult to maintain data integrity

Better: Use a junction table or PostgreSQL arrays with proper indexing.

Storing Calculated Values Without Strategy

Storing calculated values (denormalization) is fine with a clear strategy for keeping them updated:

• Triggers that update on source change
• Materialized views with refresh schedule
• Documented manual update process

Without a strategy, calculated values become stale and unreliable.

---

PostgreSQL-Specific Data Modeling

JSON/JSONB for Semi-Structured Data

PostgreSQL's jsonb type offers the best of both worlds:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    created_at TIMESTAMP,
    metadata JSONB  -- Flexible, queryable, indexable
);

-- Query into JSON
SELECT * FROM events
WHERE metadata->>'user_id' = '123';

-- Index JSON paths
CREATE INDEX idx_events_user ON events ((metadata->>'user_id'));

Use JSONB when:

• Part of your data varies by record
• You need flexibility but still want to query
• You're integrating with JSON APIs

Avoid JSONB when:

• Data structure is well-known and consistent
• You need foreign key constraints on JSON fields
• Heavy analytical queries across JSON fields

Arrays for Multi-Valued Attributes

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]  -- Array of tags
);

-- Query for articles with specific tag
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);

-- GIN index for array containment
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);

Range Types for Temporal Data

CREATE TABLE room_bookings (
    room_id INT,
    during TSTZRANGE,  -- Timestamp range with timezone
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)  -- Prevent overlaps
);

-- Query for active bookings
SELECT * FROM room_bookings
WHERE during @> NOW();

---

Design for Performance

Query-Driven Design

Critical principle: Design your schema based on how you'll query it.

Before finalizing any table structure, write out the most important queries your application will execute. Then design the schema to make those queries efficient.

Example process:

1. List top 10 most frequent queries 2. List queries with strictest latency requirements 3. Design schema to support those queries 4. Verify with EXPLAIN ANALYZE 5. Adjust based on actual execution plans

The Phone Number Example Revisited

From PostgreSQL Query Optimization:

"Which of the two designs is the right one? It depends on the intended usage of the data."

The lesson: There is no universally correct design. Design follows use case.

Indexing Considerations at Design Time

While full indexing strategy is covered elsewhere, consider at design time:

• Which columns will be in WHERE clauses?
• What are your JOIN columns?
• Will you search within text fields?
• Are there range queries (dates, numbers)?

Design your types accordingly (e.g., use TIMESTAMP WITH TIME ZONE if you'll do range queries on time).

---

When to Denormalize

Valid Reasons to Denormalize

1. Measured performance problem with normalized design 2. Read-heavy workload where join cost matters 3. Reporting/analytics that aggregate across tables 4. Caching calculated values for expensive computations

Denormalization Strategies

Materialized Views:

CREATE MATERIALIZED VIEW order_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent
FROM orders
GROUP BY customer_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW order_summary;

Pre-aggregated columns with triggers:

-- Add to parent table
ALTER TABLE customers ADD COLUMN order_count INT DEFAULT 0;

-- Trigger to maintain count
CREATE TRIGGER update_order_count
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION maintain_order_count();

Caching tables for analytics: Separate denormalized tables optimized for reporting, updated on schedule.

The Denormalization Contract

When you denormalize, you must:

1. Document what invariant you're maintaining 2. Implement mechanism to keep it consistent 3. Handle failure scenarios (what if update fails?) 4. Monitor for drift between source and cache

---

Evolving Your Schema

Schema Changes in Production

Database schemas must evolve. Plan for it:

Safe changes:

• Adding nullable columns
• Adding tables
• Adding indexes (with CONCURRENTLY)
• Adding constraints with validation

Risky changes:

• Dropping columns (break queries)
• Changing column types
• Renaming columns/tables
• Adding NOT NULL to existing columns

Migration Strategy

1. Expand: Add new structure alongside old 2. Migrate: Copy/transform data to new structure 3. Verify: Confirm new structure works 4. Contract: Remove old structure

This allows rollback at each step.

---

Common Mistakes

---

Key Takeaways

1. Data model choice is fundamental. It determines what operations are easy and what are hard.

2. Match model to access patterns. Relational for joins and complex relationships, document for hierarchical data, graph for connected data.

3. Start normalized. Denormalize only with measured justification.

4. Design for your queries. Write the important queries first, then design the schema.

5. Use IDs for references. Store human-readable values once, reference by ID elsewhere.

6. PostgreSQL offers flexibility. JSONB, arrays, and range types let you mix paradigms.

7. Plan for evolution. Schemas change; design for safe migration.

8. There is no perfect design. Only trade-offs appropriate to your use case.

---

References

• Kleppmann, M. Designing Data-Intensive Applications, Chapter 2
• Dombrovskaya, H. et al. PostgreSQL Query Optimization, Chapter 1, 9
• Fontaine, D. The Art of PostgreSQL, Part VI: Data Modeling

Storage Engines and Data Structures

Overview

Understanding how databases store and retrieve data is essential for making good architectural decisions. The choice of storage engine fundamentally shapes your system's performance characteristics—what operations are fast, what are slow, and where the trade-offs lie.

Core insight: There is a fundamental trade-off between write performance and read performance. Different storage engines make different trade-offs, and choosing the right one requires understanding your workload.

---

The Fundamental Trade-off

Every database must do two things: 1. Store data when you give it data 2. Retrieve data when you ask for it later

The simplest possible storage is an append-only log file:

# Write: append to file (O(1))
echo "$key,$value" >> database

# Read: scan entire file (O(n))
grep "^$key," database | tail -n 1

This has excellent write performance (just append) but terrible read performance (scan entire file).

Indexes exist to solve this problem. They're additional data structures that speed up reads at the cost of slowing down writes.

"This is an important trade-off in storage systems: well-chosen indexes speed up read queries, but every index slows down writes."

---

Storage Engine Families

There are two major families of storage engines:

---

Hash Indexes

The simplest indexing strategy for key-value data:

1. Store data in an append-only file 2. Keep an in-memory hash map: key → byte offset in file

How it works:

• Write: Append to file, update hash map with new offset
• Read: Look up offset in hash map, seek to that position

Strengths:

• Very fast reads (O(1) lookup + one disk seek)
• Very fast writes (append-only)
• Simple to implement

Limitations:

• Hash map must fit in memory
• Range queries are not efficient
• Cannot scan keys in order

Compaction: To prevent the file from growing forever, segments are periodically compacted (removing duplicate keys, keeping only most recent value) and merged.

Use case: High write throughput with limited key space (e.g., URL to view count).

---

SSTables and LSM-Trees

SSTable (Sorted String Table): Like the append-only log, but with key-value pairs sorted by key within each segment.

Advantages of Sorted Segments

1. Efficient merging: Merge-sort algorithm, simple and efficient even for large files 2. Sparse index: No need to index every key—know offset of surrounding keys and scan 3. Compression: Group and compress blocks of records

LSM-Tree Structure

LSM (Log-Structured Merge) Tree algorithm:

1. Memtable: Writes go to in-memory balanced tree (red-black, AVL) 2. Flush: When memtable exceeds threshold (~MB), write to disk as SSTable 3. Compaction: Background process merges SSTables, discards old values 4. Read path: Check memtable → recent segments → older segments

Durability: Separate write-ahead log captures writes before memtable (restored on crash).

Compaction Strategies

LSM-Tree Characteristics

Strengths:

• High write throughput (sequential writes)
• Efficient use of disk bandwidth
• Better compression (no fragmentation)
• Works well even when dataset >> memory

Weaknesses:

• Compaction can interfere with reads/writes
• Higher read latency (check multiple SSTables)
• Space amplification during compaction
• Key may exist in multiple places (complicates locking)

Used by: LevelDB, RocksDB, Cassandra, HBase, Lucene

---

B-Trees

The most widely used indexing structure, standard in almost all relational databases.

Structure

• Database broken into fixed-size pages (typically 4KB)
• Pages reference other pages (tree structure)
• Leaf pages contain values or references to values
• Internal pages contain keys and child page references

Key Properties

• Balanced: All leaf pages at same depth
• Branching factor: Number of child references per page (typically hundreds)
• Depth: O(log n) — a 4-level tree with branching factor 500 can store 256TB

Operations

Lookup: 1. Start at root page 2. Binary search for key range containing target 3. Follow reference to child page 4. Repeat until leaf page

Insert: 1. Find leaf page that should contain key 2. If space available, add key to page 3. If full, split page into two, update parent

Update: 1. Find leaf page containing key 2. Modify value in place 3. Write page back to disk

Reliability Mechanisms

Write-Ahead Log (WAL):

• Every modification written to append-only log first
• WAL replayed on crash to restore consistent state

Latches:

• Lightweight locks protecting tree during concurrent access
• Necessary because in-place updates could leave tree inconsistent

B-Tree Optimizations

---

B-Trees vs LSM-Trees

Write Performance

LSM-trees typically have higher write throughput due to sequential writes.

Read Performance

B-trees have more predictable read latency; LSM-trees can spike during compaction.

Space Efficiency

LSM-trees generally use disk more efficiently.

When to Choose Each

Choose B-trees when:

• Read-heavy workload
• Need predictable latency
• Strong transactional semantics (locks on key ranges)
• Proven, mature implementation needed

Choose LSM-trees when:

• Write-heavy workload
• High write throughput needed
• Disk space is constrained
• Range queries over sorted data are common

---

In-Memory Databases

With RAM prices falling, many datasets fit entirely in memory.

Examples

• Caching only: Memcached (data loss acceptable on restart)
• Durable: VoltDB, MemSQL (WAL + periodic snapshots)
• Hybrid: Redis (async persistence)

Performance Characteristics

Counterintuitively, the main performance benefit is not avoiding disk reads:

• OS filesystem cache means disk-based DBs often serve from memory too
• Real benefit: avoiding overhead of encoding data for disk format

In-Memory Advantages

• Simpler data structures possible
• Can implement types hard on disk (Redis: queues, sets)
• Lower latency for complex operations

In-Memory Limitations

• Dataset limited by RAM (or cluster RAM)
• Durability requires careful design
• More expensive per GB than disk

---

OLTP vs OLAP Storage

Access Pattern Differences

Why Separate Systems?

Running analytics on OLTP databases:

• Expensive queries harm transactional performance
• Different indexes needed
• Different storage formats optimal

Data warehouses solve this: separate read-only copy optimized for analytics.

---

Column-Oriented Storage

Traditional row-oriented storage: all values from one row stored together.

Column-oriented storage: All values from each column stored together.

Why Columns?

Analytic queries typically:

• Scan millions/billions of rows
• Use only a few columns per query
• Aggregate values

Row storage wastes bandwidth loading unused columns.

Example Query

SELECT date.weekday, product.category, SUM(quantity)
FROM fact_sales
JOIN dim_date ON ...
JOIN dim_product ON ...
WHERE date.year = 2023
  AND product.category IN ('Fruit', 'Candy')
GROUP BY date.weekday, product.category;

This only needs 3 columns from fact_sales, but row storage loads all 100+.

Column Compression

Columns often have repeated values → excellent compression:

Bitmap encoding:

• N distinct values → N bitmaps
• Each bitmap: 1 bit per row (is this value present?)
• Sparse bitmaps: run-length encoding

Bitwise operations:

WHERE product_sk IN (30, 68, 69)
-- OR the three bitmaps

WHERE product_sk = 31 AND store_sk = 3
-- AND two bitmaps

Vectorized Processing

Column data enables CPU-efficient processing:

• Load column chunk into L1 cache
• Tight loops with no function calls
• SIMD instructions for parallel processing

Column Storage in Practice

---

Star and Snowflake Schemas

Common data warehouse schema patterns:

Star Schema

• Fact table: Central table with events (sales, clicks, etc.)
• Dimension tables: Who, what, where, when, why, how

           dim_date
              |
dim_store -- fact_sales -- dim_product
              |
          dim_customer

Fact tables can have 100+ columns, billions of rows.

Snowflake Schema

Dimensions further normalized into sub-dimensions:

• dim_product → dim_brand, dim_category
• More normalized, but harder for analysts to query

Star schemas generally preferred for simplicity.

---

PostgreSQL Storage Internals

PostgreSQL uses a page-oriented storage engine with these characteristics:

Heap Tables

• Data stored in 8KB pages
• Rows stored in insertion order (no clustering by default)
• Dead tuples from updates/deletes remain until VACUUM

TOAST (The Oversized-Attribute Storage Technique)

Large values (>2KB) stored separately:

• Compressed
• Stored in separate TOAST table
• Transparently fetched when needed

Implication: Large columns are expensive to fetch; avoid SELECT *.

MVCC Storage

Multi-Version Concurrency Control means:

• Updates create new row versions
• Old versions kept for concurrent transactions
• VACUUM reclaims dead tuples

Bloat: Without regular VACUUM, tables grow with dead tuples.

Index Types in PostgreSQL

---

Choosing Storage Strategy

Questions to Ask

1. Read/write ratio?

• Read-heavy → B-tree, traditional RDBMS
• Write-heavy → LSM-tree, consider NoSQL

2. Data size vs memory?

• Fits in memory → Many options
• Much larger than memory → Need efficient disk access

3. Query patterns?

• Point lookups → Hash index, B-tree
• Range scans → B-tree, LSM-tree
• Aggregations → Column store

4. Consistency requirements?

• Strong ACID → Traditional RDBMS
• Eventual consistency OK → More options

5. OLTP or OLAP?

• OLTP → Row-oriented
• OLAP → Column-oriented

PostgreSQL Defaults

For most applications, PostgreSQL's defaults work well:

• B-tree indexes for most queries
• Row-oriented heap storage
• MVCC for concurrency

Consider extensions (TimescaleDB, Citus) for specialized needs.

---

Common Mistakes

---

Key Takeaways

1. Two families: Log-structured (LSM) vs page-oriented (B-tree) with different trade-offs.

2. Indexes trade write for read performance. Every index slows writes.

3. LSM-trees excel at writes; sequential I/O, good compression.

4. B-trees excel at reads; predictable latency, mature implementations.

5. Column storage enables analytics by reading only needed columns.

6. OLTP and OLAP need different storage optimizations.

7. PostgreSQL uses B-trees and row storage by default, well-suited for OLTP.

8. Understand your workload before choosing storage strategy.

---

References

• Kleppmann, M. Designing Data-Intensive Applications, Chapter 3
• Dombrovskaya, H. et al. PostgreSQL Query Optimization, Chapters 2-3
• PostgreSQL Documentation: Storage and TOAST

Indexing Strategies

Overview

Indexes are the primary tool for accelerating database queries. A well-designed indexing strategy can transform a query from minutes to milliseconds. A poorly designed one wastes disk space and slows writes with no benefit.

Core insight: Indexes trade write performance for read performance. Every index you add must be justified by the queries it accelerates.

---

What Is an Index?

An index is: 1. A redundant data structure — Can be dropped without data loss, rebuilt from table data 2. Invisible to the application — Same query results with or without index 3. Designed to speed up data selection — Based on specific filtering criteria

"An index provides additional data access paths; it allows us to determine what values are stored in the rows of a table without actually reading the table."

The Fundamental Trade-off

---

PostgreSQL Index Types

B-tree (Default)

The most common index structure, suitable for most queries.

Supports:

• Equality: =
• Range: <, <=, >, >=, BETWEEN
• Pattern: LIKE 'prefix%' (prefix only)
• Ordering: ORDER BY
• NULL handling: IS NULL, IS NOT NULL

Structure:

• Balanced tree with O(log n) depth
• Typical branching factor: hundreds of pointers per page
• 4-level tree can store billions of rows

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_date ON orders (order_date);

When to use: Default choice for most columns. Use unless you have specific reason for another type.

Hash

Uses hash function to compute index block address.

Supports:

• Equality only: =

Does NOT support:

• Range queries
• Ordering
• Pattern matching

CREATE INDEX idx_users_id_hash ON users USING HASH (id);

When to use: Only for exact-match lookups where you'll never need range queries. B-tree is almost always a better choice.

GiST (Generalized Search Tree)

Framework for building custom index types.

Built-in support for:

• Geometric data (points, boxes, polygons)
• Range types
• Full-text search
• Nearest-neighbor queries

-- Geometric containment
CREATE INDEX idx_locations ON stores USING GIST (location);
SELECT * FROM stores WHERE location <@ box '((0,0),(10,10))';

-- Range exclusion constraint
CREATE TABLE reservations (
    room_id INT,
    during TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

When to use: Spatial data, range types, or any data where you need overlap/containment queries.

GIN (Generalized Inverted Index)

Maps values to lists of row locations. Excellent for multi-valued columns.

Ideal for:

• Arrays: @>, &&, <@
• JSONB: @>, ?, ?|, ?&
• Full-text search: @@
• Trigram similarity

-- Array containment
CREATE INDEX idx_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

-- JSONB containment
CREATE INDEX idx_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"type": "click"}';

-- Full-text search
CREATE INDEX idx_content ON documents USING GIN (to_tsvector('english', content));

When to use: Columns containing multiple values that need to be individually searchable.

BRIN (Block Range Index)

Stores summary info about ranges of physical table blocks.

Characteristics:

• Very small (orders of magnitude smaller than B-tree)
• Best for naturally ordered data (timestamps, sequences)
• Less precise — may read extra blocks

-- Time-series data with natural ordering
CREATE INDEX idx_events_time ON events USING BRIN (created_at);

When to use: Very large tables where data is physically ordered by the indexed column (e.g., append-only time-series).

---

Index Type Decision Matrix

---

How Indexes Are Used

Selectivity: The Key Factor

Selectivity = (rows matching filter) / (total rows)

The choice between index scan and sequential scan depends on selectivity:

Why? Random I/O (index scan) is expensive. When you're reading most of the table anyway, sequential I/O (full scan) is faster.

Data Access Algorithms

PostgreSQL chooses between several access methods:

Sequential Scan:

Read all blocks → Filter rows → Return matches

Cost: O(n) I/O + O(n) CPU

Index Scan:

Read index → For each match, fetch table row → Return

Cost: O(log n + k) where k = matching rows Problem: May read same table block multiple times

Bitmap Index Scan:

Read index → Build bitmap of matching blocks → Read blocks → Filter rows

Benefits:

• Reads each block at most once
• Can combine multiple indexes with AND/OR
• Better for medium selectivity

Index-Only Scan:

Read index → Return data directly from index

Best case: Index contains all needed columns (covering index)

When Indexes Cannot Be Used

Indexes won't help when:

1. Expression applied to column:

-- BAD: Cannot use index on created_at
WHERE EXTRACT(YEAR FROM created_at) = 2023

-- GOOD: Use range instead
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'

2. Type mismatch:

-- If column is INTEGER
WHERE user_id = '123'  -- String comparison, may not use index

3. Leading wildcard:

WHERE name LIKE '%smith'  -- Cannot use B-tree index

4. OR conditions on different columns:

WHERE email = 'x' OR phone = 'y'  -- May not use either index efficiently

5. Low selectivity:

WHERE active = true  -- If 95% of rows are active, index scan is slower

---

Composite Indexes

Index on multiple columns in specific order.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Column Order Matters

The index above supports:

• WHERE customer_id = ? — Uses index
• WHERE customer_id = ? AND order_date = ? — Uses index fully
• WHERE customer_id = ? AND order_date > ? — Uses index fully
• WHERE order_date = ? — Cannot use this index (wrong prefix)

Rule: Put equality columns first, then range columns.

Covering Indexes (INCLUDE)

Include additional columns in index for index-only scans:

CREATE INDEX idx_orders_covering ON orders (customer_id, order_date)
    INCLUDE (total, status);

Now this query uses index-only scan:

SELECT order_date, total, status
FROM orders
WHERE customer_id = 123;

---

Partial Indexes

Index only a subset of rows.

-- Only index active users
CREATE INDEX idx_active_users ON users (email)
    WHERE active = true;

-- Only index recent orders
CREATE INDEX idx_recent_orders ON orders (customer_id)
    WHERE order_date > '2023-01-01';

Benefits:

• Smaller index size
• Faster to maintain
• Better cache utilization

Use when:

• Query always includes the filter condition
• Most rows don't match the condition

---

Expression Indexes

Index on computed expression.

-- Index on lowercase email
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Query uses the index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Index on JSON field
CREATE INDEX idx_events_user ON events ((data->>'user_id'));

Important: Query must use exact same expression.

---

Index Strategies by Query Pattern

Single-Column Equality

SELECT * FROM users WHERE id = 123;

Strategy: B-tree index on id

Single-Column Range

SELECT * FROM orders WHERE created_at > '2023-01-01';

Strategy: B-tree index on created_at

Multiple Equality Conditions

SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

Strategy: Composite index (customer_id, status) or separate indexes (PostgreSQL can combine with bitmap)

Equality + Range

SELECT * FROM orders
WHERE customer_id = 123
AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

Strategy: Composite index (customer_id, order_date) — equality column first

Sorting

SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC;

Strategy: Composite index (customer_id, order_date DESC)

Pattern Matching

-- Prefix match
SELECT * FROM users WHERE name LIKE 'John%';
-- Strategy: B-tree index (works for prefix)

-- Anywhere match
SELECT * FROM users WHERE name LIKE '%john%';
-- Strategy: GIN with pg_trgm extension
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

JSONB Queries

SELECT * FROM events WHERE data @> '{"type": "click"}';

Strategy: GIN index on JSONB column

CREATE INDEX idx_events_data ON events USING GIN (data);

Full-Text Search

SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('postgresql');

Strategy: GIN index on tsvector

CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('english', body));

---

Query Planner Behavior

Viewing Execution Plans

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- With actual timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Reading EXPLAIN Output

Key things to look for:

Statistics and Cost Estimation

PostgreSQL relies on table statistics to choose execution plans:

-- Update statistics
ANALYZE users;

-- View statistics
SELECT * FROM pg_stats WHERE tablename = 'users';

Critical statistics:

• Row count
• Column cardinality (distinct values)
• Most common values
• Histogram of value distribution

---

Common Indexing Mistakes

1. Over-Indexing

Problem: Creating indexes for every possible query.

Cost:

• Each index slows INSERT/UPDATE/DELETE
• Disk space usage
• Maintenance overhead

Solution: Index only what you query. Monitor and drop unused indexes.

-- Find unused indexes
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

2. Indexing Low-Cardinality Columns

Problem: Index on boolean or status column with few values.

-- BAD: Most queries select 95% of rows
CREATE INDEX idx_users_active ON users (active);
SELECT * FROM users WHERE active = true;  -- Will use seq scan anyway

Solution: Use partial index if one value is rare:

CREATE INDEX idx_users_inactive ON users (id) WHERE active = false;

3. Wrong Column Order in Composite Index

Problem: Range column before equality column.

-- BAD: Can only use index partially
CREATE INDEX idx_orders ON orders (order_date, customer_id);
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

-- GOOD: Equality first, then range
CREATE INDEX idx_orders ON orders (customer_id, order_date);

4. Function Wrapping Indexed Column

Problem: Applying function to column in WHERE clause.

-- BAD: Cannot use index
WHERE UPPER(email) = 'TEST@EXAMPLE.COM';

-- GOOD: Expression index or fix at write time
CREATE INDEX idx_email_upper ON users (UPPER(email));

5. Neglecting Index Maintenance

Problem: Bloated indexes after many updates/deletes.

Solution:

-- Rebuild index
REINDEX INDEX idx_users_email;

-- Rebuild concurrently (no lock)
REINDEX INDEX CONCURRENTLY idx_users_email;

---

Index Maintenance

Creating Indexes Without Locking

-- Blocks writes during creation (default)
CREATE INDEX idx_users_email ON users (email);

-- Does not block writes (takes longer)
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Monitoring Index Usage

-- Index usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Index Size

SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users';

Bloat Detection

After many updates, indexes can become bloated:

-- Install pgstattuple extension
CREATE EXTENSION pgstattuple;

-- Check bloat
SELECT * FROM pgstattuple('idx_users_email');

---

Indexing Strategy Process

Step 1: Identify Critical Queries

List your most important queries:

• Most frequently executed
• Slowest response times
• Business-critical paths

Step 2: Analyze Without Indexes

EXPLAIN ANALYZE SELECT ...;

Look for:

• Seq Scans on large tables
• High row estimates
• Long execution times

Step 3: Design Indexes

For each query: 1. Identify filter columns (WHERE) 2. Identify join columns 3. Identify sort columns (ORDER BY) 4. Consider covering columns (SELECT list)

Step 4: Test and Measure

-- Create index
CREATE INDEX CONCURRENTLY ...;

-- Update statistics
ANALYZE tablename;

-- Verify usage
EXPLAIN ANALYZE SELECT ...;

Step 5: Monitor Over Time

• Track query performance
• Monitor index usage
• Remove unused indexes
• Adjust as data and queries change

---

Key Takeaways

1. Indexes trade write for read performance. Only create indexes that accelerate queries you actually run.

2. B-tree is the default. Use other types only for specific needs (GIN for arrays/JSONB, GiST for geometry).

3. Selectivity determines usefulness. Low-selectivity indexes may never be used.

4. Column order matters. Put equality columns before range columns in composite indexes.

5. Use EXPLAIN ANALYZE. Don't guess—verify that indexes are being used.

6. Create concurrently. Use CREATE INDEX CONCURRENTLY in production.

7. Monitor and maintain. Track usage, remove unused indexes, rebuild bloated ones.

8. Expression indexes match expressions. Query must use exact same expression.

---

References

• Dombrovskaya, H. et al. PostgreSQL Query Optimization, Chapters 3, 5, 14
• Fontaine, D. The Art of PostgreSQL, Indexing chapters
• PostgreSQL Documentation: Indexes

Scaling Patterns

Overview

When a single machine cannot handle your data volume, read load, or write load, you must distribute data across multiple machines. This chapter covers the two fundamental techniques for distributed data: replication (keeping copies on multiple nodes) and partitioning (splitting data across nodes).

Core insight: Scaling a stateful data system is fundamentally harder than scaling stateless services. There are no magic solutions—only trade-offs between consistency, availability, and complexity.

---

Why Distribute Data?

Three primary reasons to distribute a database:

---

Scaling Approaches

Vertical Scaling (Scale Up)

Move to a more powerful machine: more CPUs, RAM, disk.

Advantages:

• Simple — no distributed systems complexity
• Strong consistency by default
• All data in one place

Disadvantages:

• Cost grows faster than linearly
• Single point of failure
• Limited by available hardware

Horizontal Scaling (Scale Out)

Distribute across multiple machines (shared-nothing architecture).

Advantages:

• Can scale beyond single-machine limits
• Fault tolerance through redundancy
• Geographic distribution possible

Disadvantages:

• Significant added complexity
• Network latency and partitions
• Weaker consistency guarantees

Practical Guidance

Start vertical, go horizontal when necessary:

1. Optimize queries and indexes first 2. Scale up until cost/capability limits 3. Add read replicas if read-heavy 4. Partition only when unavoidable

"In some cases, a simple single-threaded program can perform significantly better than a cluster with over 100 CPU cores."

---

Replication

Replication keeps copies of the same data on multiple machines.

Purposes of Replication

• High availability: Keep running if nodes fail
• Latency reduction: Serve from nearby replicas
• Read scalability: Distribute read load

Replication Topologies

Single-Leader (Master-Slave)

How it works: 1. One node is designated leader (master, primary) 2. All writes go to the leader 3. Leader streams changes to followers (slaves, replicas) 4. Reads can go to leader or followers

         Writes
           │
           ▼
        ┌──────┐
        │Leader│
        └──┬───┘
           │ Replication stream
     ┌─────┼─────┐
     ▼     ▼     ▼
┌────────┐ ┌────────┐ ┌────────┐
│Follower│ │Follower│ │Follower│
└────────┘ └────────┘ └────────┘
     ▲         ▲         ▲
     └─────────┴─────────┘
           Reads

Advantages:

• No write conflicts (single writer)
• Easy to understand
• Well-supported in PostgreSQL, MySQL, etc.

Disadvantages:

• Leader is single point of failure
• All writes go through one node
• Failover can be complex

Used by: PostgreSQL, MySQL, MongoDB, RabbitMQ

Multi-Leader

Multiple nodes accept writes; replicate changes to each other.

Use cases:

• Multi-datacenter deployment
• Offline-capable clients
• Collaborative editing

Challenge: Write conflicts when same data modified on different leaders.

Conflict resolution strategies:

• Last write wins (risk of data loss)
• Merge (application-specific logic)
• Keep all versions (let user resolve)

Leaderless

No designated leader; clients write to multiple nodes.

How it works:

• Write to W nodes
• Read from R nodes
• If W + R > N, reads overlap with writes

Example: With N=3, W=2, R=2:

• Write succeeds if 2 nodes confirm
• Read queries 2 nodes, takes most recent
• At least 1 node seen both read and write

Used by: Cassandra, Riak, Voldemort (Dynamo-style)

Synchronous vs Asynchronous Replication

Practical approach: Semi-synchronous — one synchronous replica, others async.

Replication Lag

Asynchronous replication means followers may be behind.

Problems:

• Read your writes: Write, then read from follower that hasn't caught up
• Monotonic reads: Read from one replica, then older replica
• Consistent prefix: See effect before cause

Mitigation:

• Read from leader for recently written data
• Session stickiness to same replica
• Include version/timestamp in reads

Failover

When leader fails, a follower must become the new leader.

Failover steps: 1. Detect leader failure (timeout-based) 2. Choose new leader (most up-to-date replica) 3. Reconfigure clients to use new leader 4. Handle old leader if it comes back

Risks:

• Split brain: Two nodes think they're leader
• Data loss: Async replica may be behind
• Coordination: External systems need updating

---

Partitioning (Sharding)

Partitioning splits data across nodes so each node holds a subset.

Why Partition?

• Dataset too large for one machine
• Query throughput exceeds one machine's capacity
• Write throughput exceeds one machine's capacity

Partitioning Strategies

By Key Range

Assign continuous ranges of keys to each partition.

Partition 0: A-F
Partition 1: G-L
Partition 2: M-R
Partition 3: S-Z

Advantages:

• Efficient range queries
• Keys sorted within partition
• Easy to understand

Disadvantages:

• Risk of hot spots (sequential keys)
• Timestamp-based keys → one hot partition

Example problem: If key is timestamp, all writes go to "today" partition.

Solution: Prefix key with something distributed (sensor_id, user_id).

By Hash of Key

Hash the key and assign hash ranges to partitions.

Advantages:

• Even distribution of keys
• No hot spots from sequential keys

Disadvantages:

• Lose range query efficiency
• Keys scattered across partitions

Cassandra's compromise: Hash first column, sort by remaining columns.

PRIMARY KEY ((user_id), timestamp)
-- Partitioned by hash(user_id)
-- Sorted by timestamp within partition

Partitioning and Secondary Indexes

Secondary indexes complicate partitioning.

Local Index (Document-Partitioned)

Each partition maintains its own index for its data.

Partition 0: Index of its own red cars
Partition 1: Index of its own red cars
...

Query: Must scatter to all partitions, gather results.

Used by: MongoDB, Cassandra, Elasticsearch

Global Index (Term-Partitioned)

Index entries distributed across partitions by term.

Index Partition 0: color:a-m (all cars with colors a-m)
Index Partition 1: color:n-z (all cars with colors n-z)

Query: Single partition for point queries. Write: May update multiple index partitions.

Trade-off: Faster reads, slower writes.

Hot Spots

Even with hash partitioning, hot spots can occur.

Example: Celebrity user — all writes to one partition.

Application-level solutions:

• Add random prefix to hot keys (e.g., user_123_01, user_123_02)
• Trade-off: Reads must combine results from all prefixed keys

Rebalancing

Moving data between partitions when nodes added/removed.

Goals:

• Even load distribution after rebalancing
• Minimize data movement
• Keep system available during rebalancing

Approaches:

Anti-pattern: hash(key) mod N — adding one node moves almost all data.

---

Combining Replication and Partitioning

Real systems use both:

• Data partitioned across nodes
• Each partition replicated for fault tolerance

                    ┌─────────────────────┐
                    │   Partition 1       │
           ┌───────►│ Leader: Node A      │
           │        │ Follower: Node B, C │
           │        └─────────────────────┘
   Data────┤
           │        ┌─────────────────────┐
           │        │   Partition 2       │
           └───────►│ Leader: Node B      │
                    │ Follower: Node A, C │
                    └─────────────────────┘

Each node is leader for some partitions, follower for others.

---

PostgreSQL Scaling Options

Built-in Replication

Streaming replication:

• Async or sync
• Read replicas for query scaling
• Automatic failover with tools like Patroni

Logical replication:

• Replicate selected tables
• Cross-version replication
• Replicate to different schemas

Partitioning (Native)

Declarative partitioning since PostgreSQL 10:

CREATE TABLE events (
    id SERIAL,
    created_at TIMESTAMP,
    data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2023 PARTITION OF events
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Benefits:

• Prune partitions in queries
• Faster VACUUM (per-partition)
• Easier archival (drop old partitions)

Extensions

Citus: Distributed PostgreSQL for horizontal scaling.

• Transparent sharding
• Distributed queries
• Reference tables (replicated everywhere)

TimescaleDB: Time-series optimizations.

• Automatic partitioning by time
• Compression
• Continuous aggregates

---

Consistency Trade-offs (CAP Theorem)

The CAP theorem states you can have at most 2 of 3:

• Consistency: All nodes see the same data
• Availability: Every request gets a response
• Partition tolerance: System works despite network partitions

Reality: Network partitions happen. You must choose between:

• CP: Sacrifice availability for consistency (wait for partition to heal)
• AP: Sacrifice consistency for availability (serve stale data)

Consistency Models

PostgreSQL: Strong consistency on single node; configurable on replicas.

---

Read Replicas Pattern

Common pattern for read-heavy workloads:

1. All writes to primary 2. Reads distributed across replicas 3. Application routes read-only queries to replicas

Implementation:

# Simple connection routing
def get_connection(read_only=False):
    if read_only:
        return replica_pool.get()
    return primary_pool.get()

Considerations:

• Replication lag: May read stale data
• Session consistency: Route user's reads to same replica
• Query routing: Which queries are safe on replicas?

---

Scaling Decision Framework

When to Add Read Replicas

• Read queries dominating CPU
• Read latency requirements in multiple regions
• Need redundancy for disaster recovery

When to Partition

• Data volume exceeds single-machine capacity
• Write throughput exceeds single-machine capacity
• Hot partitions after optimization

When to Stay Single-Node

• Data fits comfortably in memory
• Writes are not bottleneck
• Strong consistency required
• Team lacks distributed systems expertise

---

Common Mistakes

---

Key Takeaways

1. Replication and partitioning serve different purposes. Replication for fault tolerance and read scaling; partitioning for data volume and write scaling.

2. Single-leader replication is simplest. Use it unless you have specific needs for multi-leader or leaderless.

3. Replication lag is unavoidable in asynchronous systems. Design applications to handle it.

4. Partition key choice is critical. Wrong choice leads to hot spots or inefficient queries.

5. Range partitioning enables range queries; hash partitioning distributes evenly.

6. Combining partitioning + replication is normal and necessary.

7. CAP means real trade-offs. Know what your system sacrifices during partitions.

8. Start simple. Single-node PostgreSQL handles more than most people think.

---

References

• Kleppmann, M. Designing Data-Intensive Applications, Chapters 5-6
• PostgreSQL Documentation: High Availability, Load Balancing, Replication
• PostgreSQL Documentation: Table Partitioning

Transactions and Concurrency

Overview

Transactions are the fundamental mechanism for handling concurrent access to shared data. They provide guarantees about what happens when multiple operations execute simultaneously and when things go wrong.

Core insight: Concurrency bugs are among the hardest to detect because they're non-deterministic. Transactions provide a safety net, but only if you understand what guarantees your isolation level actually provides.

---

ACID Properties

The classic transaction guarantees:

Atomicity

If a transaction fails partway through, all changes are rolled back. You never see partial results.

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If this fails, the debit from account 1 is also rolled back
COMMIT;

Consistency

The database moves from one valid state to another. Constraints are enforced.

Note: Consistency depends on both database constraints AND application logic. The database can only enforce what you tell it to enforce.

Isolation

Concurrent transactions don't affect each other's execution. The isolation level determines exactly what guarantees are provided.

Durability

Once a transaction commits, its changes persist even if the system crashes immediately after.

Implementation: Write-ahead log (WAL) — changes written to durable log before commit confirmed.

---

Isolation Levels

Isolation levels trade off safety for performance. Weaker isolation allows more concurrency but permits more anomalies.

Read Uncommitted

Guarantee: Almost none. Allows: Dirty reads (seeing uncommitted changes). Use case: Rarely used; only for specific read-heavy scenarios where stale data is acceptable.

Read Committed

Guarantee: You only see committed data. Prevents: Dirty reads. Allows: Non-repeatable reads, phantom reads.

Implementation: Locks held only during read/write, not entire transaction.

PostgreSQL default: Yes, this is the default level.

Repeatable Read (Snapshot Isolation)

Guarantee: You see a consistent snapshot from transaction start. Prevents: Dirty reads, non-repeatable reads. Allows: Phantoms (in standard definition); PostgreSQL prevents most phantoms.

Implementation: MVCC — each transaction sees data as it existed at transaction start.

Naming confusion: PostgreSQL and MySQL call their snapshot isolation "repeatable read." Oracle calls it "serializable." The SQL standard definition is different. Nobody agrees on what repeatable read means.

Serializable

Guarantee: Transactions execute as if run one at a time. Prevents: All anomalies. Cost: Highest overhead; may abort more transactions.

---

Concurrency Anomalies

Dirty Read

Reading data written by an uncommitted transaction.

T1: UPDATE balance = 500  (not committed)
T2: SELECT balance → 500  (dirty read!)
T1: ROLLBACK
T2: Now has wrong data

Prevented by: Read Committed and above.

Non-Repeatable Read

Reading the same row twice yields different results because another transaction modified it.

T1: SELECT balance → 1000
T2: UPDATE balance = 500; COMMIT
T1: SELECT balance → 500  (different!)

Prevented by: Repeatable Read and above.

Phantom Read

A query returns different rows because another transaction inserted/deleted.

T1: SELECT count(*) WHERE dept = 'A' → 5
T2: INSERT INTO employees (dept = 'A'); COMMIT
T1: SELECT count(*) WHERE dept = 'A' → 6  (phantom!)

Prevented by: Serializable (fully); Repeatable Read (partially in PostgreSQL).

Lost Update

Two transactions read-modify-write, and one overwrites the other.

T1: SELECT balance → 100
T2: SELECT balance → 100
T1: UPDATE balance = 100 + 10 → 110
T2: UPDATE balance = 100 + 20 → 120
-- T1's update is lost!

Result: Balance should be 130, but it's 120.

Write Skew

Two transactions read overlapping data, then make decisions based on stale reads.

Example: Two doctors checking if they can go off-call:

-- Both check: 2 doctors on call
Alice: SELECT count(*) WHERE on_call = true → 2
Bob:   SELECT count(*) WHERE on_call = true → 2

-- Both decide it's safe
Alice: UPDATE SET on_call = false WHERE name = 'Alice'
Bob:   UPDATE SET on_call = false WHERE name = 'Bob'

-- Result: 0 doctors on call!

Prevented by: Serializable isolation only.

---

Multi-Version Concurrency Control (MVCC)

PostgreSQL uses MVCC to implement snapshot isolation.

How It Works

1. Each row has creation and deletion transaction IDs 2. Updates create new row versions (not in-place modification) 3. Transactions see rows based on visibility rules 4. Old versions garbage collected by VACUUM

Visibility Rules

A row is visible to a transaction if: 1. The creating transaction committed before the reader's snapshot 2. The row is not deleted, OR the deleting transaction hadn't committed when reader's snapshot was taken

Consequences

Dead tuples: Updated/deleted rows remain until VACUUM removes them.

Table bloat: Without regular VACUUM, tables grow with dead tuples.

Long transactions: Hold back VACUUM, increase bloat.

-- Check for long-running transactions
SELECT pid, age(clock_timestamp(), xact_start), query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY xact_start;

---

Preventing Lost Updates

Several strategies to prevent the read-modify-write problem:

1. Atomic Operations (Best)

Let the database do the modification atomically.

-- Instead of: SELECT → modify in app → UPDATE
UPDATE counters SET value = value + 1 WHERE id = 1;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE products SET stock = stock - 1
WHERE id = 42 AND stock > 0;  -- With check

2. Explicit Locking (SELECT FOR UPDATE)

Lock the rows before modifying.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now locked; other transactions must wait
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Lock modes:

• FOR UPDATE: Exclusive lock (blocks other FOR UPDATE)
• FOR SHARE: Shared lock (allows other FOR SHARE)
• FOR NO KEY UPDATE: Like FOR UPDATE, but allows foreign key checks
• NOWAIT: Error immediately if can't acquire lock
• SKIP LOCKED: Skip locked rows (for queue-like patterns)

3. Compare-and-Set

Check that value hasn't changed before updating.

UPDATE wiki_pages
SET content = 'new content', version = version + 1
WHERE id = 1 AND version = 5;  -- Expected version

-- Check rows affected
-- If 0, someone else modified it

4. Automatic Detection

PostgreSQL's Repeatable Read detects some lost updates and aborts the conflicting transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- If another transaction modified the same row,
-- this transaction will be aborted

Note: MySQL's Repeatable Read does NOT detect lost updates.

---

Preventing Write Skew

Write skew is harder to prevent because it involves multiple rows.

Use Serializable Isolation

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT count(*) FROM doctors WHERE on_call = true;
-- Decision made based on this count
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

PostgreSQL's Serializable Snapshot Isolation (SSI) will abort one transaction if write skew would occur.

Use FOR UPDATE on Read Rows

Lock the rows your decision depends on:

BEGIN;
SELECT * FROM doctors WHERE on_call = true FOR UPDATE;
-- Now locked; can safely check count and update
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

Use Constraints

Where possible, use database constraints:

-- Unique constraint prevents double-booking usernames
CREATE UNIQUE INDEX ON users (username);

-- Exclusion constraint prevents overlapping bookings
CREATE TABLE reservations (
    room_id INT,
    during TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

---

Locking Strategies

Row-Level Locking

PostgreSQL uses row-level locking, not table-level.

-- Only locks the specific rows
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Advisory Locks

Application-level locks for custom scenarios:

-- Obtain lock (blocks if already held)
SELECT pg_advisory_lock(12345);

-- Do work...

-- Release lock
SELECT pg_advisory_unlock(12345);

Useful for:

• Coordinating between multiple processes
• Preventing duplicate cron job execution
• Custom locking schemes

Deadlock Detection

PostgreSQL automatically detects deadlocks and aborts one transaction.

T1: Lock row A
T2: Lock row B
T1: Try to lock row B → waits for T2
T2: Try to lock row A → waits for T1
-- Deadlock detected! One transaction aborted.

Prevention:

• Always lock rows in consistent order
• Use short transactions
• Use NOWAIT to fail fast

---

PostgreSQL Isolation Levels

Read Committed (Default)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or just BEGIN; (default)

Behavior:

• Each statement sees a new snapshot
• No dirty reads
• Non-repeatable reads possible
• Lost updates NOT automatically detected

Repeatable Read (Snapshot Isolation)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Behavior:

• Transaction sees snapshot from its start
• No dirty or non-repeatable reads
• Some lost updates detected and aborted
• Write skew NOT prevented

Serializable (SSI)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Behavior:

• Full serializability
• Write skew detected and prevented
• May abort more transactions (must retry)
• Best for correctness

---

Transaction Patterns

Short Transactions

Keep transactions as short as possible:

-- BAD: Long transaction
BEGIN;
SELECT * FROM orders WHERE status = 'pending';
-- ... application processing for 30 seconds ...
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

-- GOOD: Short transaction
-- Do processing outside transaction
BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

Savepoints

Partial rollback within a transaction:

BEGIN;
INSERT INTO orders (id, amount) VALUES (1, 100);

SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
-- Oops, product 999 doesn't exist
ROLLBACK TO before_items;

-- Continue with valid data
INSERT INTO order_items (order_id, product_id) VALUES (1, 123);
COMMIT;

Retry Logic

Handle serialization failures:

def execute_with_retry(conn, fn, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                result = fn(cur)
                conn.commit()
                return result
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            if attempt == max_retries - 1:
                raise
            time.sleep(random.uniform(0.01, 0.1))

---

Queue Pattern with SELECT FOR UPDATE SKIP LOCKED

Process items from a queue without conflicts:

-- Worker gets next unprocessed item
BEGIN;
SELECT id, data FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Process the task...

UPDATE tasks SET status = 'complete' WHERE id = :id;
COMMIT;

Multiple workers can process different items concurrently without blocking.

---

Common Mistakes

---

Performance Considerations

Isolation Level Trade-offs

When to Use Each Level

Read Committed:

• High-throughput scenarios
• When application handles consistency
• Read-only analytics

Repeatable Read:

• Reports that need consistent snapshots
• When you need to read same data multiple times

Serializable:

• Financial transactions
• Inventory management
• Any place write skew would be catastrophic

---

Key Takeaways

1. ACID provides guarantees, but isolation levels trade off safety for performance.

2. Read Committed is the default but allows non-repeatable reads and lost updates.

3. Repeatable Read provides snapshot isolation but doesn't prevent write skew.

4. Serializable is safest but has higher abort rates; must retry.

5. Use atomic operations for read-modify-write when possible.

6. Use FOR UPDATE to lock rows when you need to read-then-write.

7. Keep transactions short to reduce contention and bloat.

8. Handle retries for serialization failures.

9. Understand what your isolation level actually guarantees — names are inconsistent across databases.

---

References

• Kleppmann, M. Designing Data-Intensive Applications, Chapter 7
• PostgreSQL Documentation: Transaction Isolation
• PostgreSQL Documentation: Explicit Locking

Application Integration

Overview

The interface between your application and database is where architecture decisions become reality. Poor integration patterns can negate all the benefits of good schema design and indexing. This chapter covers practical patterns for connecting applications to PostgreSQL effectively.

Core insight: Network round-trips are often the dominant factor in database performance. Reducing round-trips matters more than micro-optimizing individual queries.

---

The N+1 Query Problem

The most common performance anti-pattern in database-backed applications.

What Is N+1?

# BAD: N+1 queries
artists = db.query("SELECT * FROM artist LIMIT 10")  # 1 query
for artist in artists:
    # N additional queries (one per artist)
    albums = db.query(f"SELECT * FROM album WHERE artistid = {artist.id}")
    for album in albums:
        print(f"{artist.name}: {album.title}")

This executes 1 + N queries. With 100 artists, that's 101 queries.

Why It's Expensive

Example calculation:

• 100 artists, 2ms round-trip each
• N+1 approach: 101 * 2ms = 202ms
• Single query: 1 * 2ms = 2ms (100x faster)

The Solution: JOIN

-- GOOD: Single query with JOIN
SELECT artist.name, album.title
FROM artist
JOIN album USING (artistid)
ORDER BY artist.name, album.title;

Or with aggregation:

SELECT artist.name,
       array_agg(album.title ORDER BY album.title) as albums
FROM artist
JOIN album USING (artistid)
GROUP BY artist.artistid
ORDER BY artist.name;

Detecting N+1 in ORMs

Most ORMs default to lazy loading, which causes N+1:

# Django (lazy loading - N+1)
artists = Artist.objects.all()[:10]
for artist in artists:
    for album in artist.album_set.all():  # Triggers query per artist
        print(album.title)

# Django (eager loading - single query)
artists = Artist.objects.prefetch_related('album_set').all()[:10]

# Rails (lazy loading - N+1)
Artist.limit(10).each do |artist|
  artist.albums.each { |album| puts album.title }
end

# Rails (eager loading - single query)
Artist.includes(:albums).limit(10).each do |artist|
  artist.albums.each { |album| puts album.title }
end

Query Logging

Enable query logging to detect N+1:

# Django settings.py
LOGGING = {
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
        },
    },
}

Look for patterns of repeated similar queries.

---

ORM Pitfalls

ORMs are convenient but can hide performance problems.

Common ORM Anti-Patterns

SELECT * Is Expensive

# BAD: Fetches all columns including large text fields
user = User.objects.get(id=1)
print(user.name)  # Only needed name, but fetched bio, avatar, etc.

# GOOD: Fetch only what you need
user = User.objects.only('name').get(id=1)

# Or with values:
name = User.objects.filter(id=1).values_list('name', flat=True).first()

With TOAST columns (large values stored separately), SELECT * triggers extra I/O even for columns you don't use.

In-Memory vs Database Operations

# BAD: Filter in Python
active_users = [u for u in User.objects.all() if u.is_active]

# GOOD: Filter in database
active_users = User.objects.filter(is_active=True)

# BAD: Sort in Python
users = sorted(User.objects.all(), key=lambda u: u.created_at)

# GOOD: Sort in database
users = User.objects.order_by('created_at')

# BAD: Count in Python
count = len(User.objects.all())

# GOOD: Count in database
count = User.objects.count()

When to Use Raw SQL

ORMs struggle with:

• Complex aggregations
• Window functions
• Recursive queries
• Bulk operations
• Performance-critical queries

# Complex query - use raw SQL
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        WITH monthly_sales AS (
            SELECT date_trunc('month', created_at) as month,
                   SUM(amount) as total
            FROM orders
            GROUP BY 1
        )
        SELECT month,
               total,
               total - LAG(total) OVER (ORDER BY month) as change
        FROM monthly_sales
        ORDER BY month
    """)
    results = cursor.fetchall()

---

Where to Put Business Logic

Three options, each with trade-offs.

Option 1: Application Code Only

def transfer_money(from_id, to_id, amount):
    with transaction.atomic():
        from_account = Account.objects.select_for_update().get(id=from_id)
        to_account = Account.objects.select_for_update().get(id=to_id)

        if from_account.balance < amount:
            raise InsufficientFunds()

        from_account.balance -= amount
        to_account.balance += amount
        from_account.save()
        to_account.save()

Pros:

• Logic in familiar language
• Easy to test
• Version controlled with application

Cons:

• More network round-trips
• Logic scattered across codebase
• Concurrency handled in application

Option 2: Stored Procedures

CREATE OR REPLACE FUNCTION transfer_money(
    from_id INT,
    to_id INT,
    amount NUMERIC
) RETURNS void AS $$
DECLARE
    from_balance NUMERIC;
BEGIN
    -- Lock accounts
    SELECT balance INTO from_balance
    FROM accounts WHERE id = from_id FOR UPDATE;

    IF from_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;

Pros:

• Single network round-trip
• Logic close to data
• Consistent enforcement across all clients

Cons:

• Different language (PL/pgSQL)
• Harder to test
• Deployment coupled with schema

Option 3: Hybrid Approach (Recommended)

• Application code: Complex business rules, validation, orchestration
• Database: Data integrity constraints, simple transformations, aggregations

# Application handles orchestration
def process_order(order_data):
    validate_order(order_data)  # Application logic

    with transaction.atomic():
        # Database handles atomicity and constraints
        order = Order.objects.create(**order_data)

        # Use database for efficient bulk operations
        connection.cursor().execute("""
            INSERT INTO order_items (order_id, product_id, quantity, price)
            SELECT %s, product_id, quantity,
                   quantity * price_per_unit
            FROM unnest(%s::int[], %s::int[]) AS t(product_id, quantity)
            JOIN products USING (product_id)
        """, [order.id, product_ids, quantities])

---

Connection Management

Connection Pooling

Database connections are expensive to create. Use a pool.

Without pooling:

Request → Create connection → Execute query → Close connection
Request → Create connection → Execute query → Close connection
...

With pooling:

Request → Get connection from pool → Execute query → Return to pool
Request → Get connection from pool → Execute query → Return to pool
...

PgBouncer (External Pooler)

Most common PostgreSQL connection pooler.

# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Pool modes:

Application-Level Pooling

Most ORMs and drivers support pooling:

# Django
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'CONN_MAX_AGE': 600,  # Reuse connections for 10 minutes
    }
}

# SQLAlchemy
engine = create_engine(
    'postgresql://...',
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
)

Connection Sizing

Rule of thumb: connections = (cores * 2) + spindles

For SSD systems: connections ≈ cores * 2

More connections ≠ better performance. Too many connections:

• Increase memory usage
• Cause lock contention
• Reduce throughput

---

Prepared Statements

Parse query once, execute many times.

Benefits

PostgreSQL Prepared Statements

-- Prepare once
PREPARE get_user(int) AS
SELECT * FROM users WHERE id = $1;

-- Execute many times
EXECUTE get_user(1);
EXECUTE get_user(2);
EXECUTE get_user(3);

Driver-Level Preparation

Most drivers handle this automatically:

# psycopg2 - server-side prepared statements
cursor.execute("SELECT * FROM users WHERE id = %s", [user_id])

# With explicit preparation
cursor.execute("PREPARE get_user AS SELECT * FROM users WHERE id = $1")
cursor.execute("EXECUTE get_user(%s)", [user_id])

Prepared Statement Gotchas

1. Plan caching: First few executions use generic plan, then specialized 2. Parameter sniffing: Plan optimized for first parameter values 3. Session scope: Prepared statements live in session; lost on disconnect 4. PgBouncer transaction mode: Doesn't support server-side prepared statements

---

Batch Operations

Bulk Inserts

# BAD: One insert at a time
for item in items:
    cursor.execute("INSERT INTO items (name) VALUES (%s)", [item.name])

# GOOD: Batch insert
cursor.executemany(
    "INSERT INTO items (name) VALUES (%s)",
    [(item.name,) for item in items]
)

# BETTER: COPY (fastest for large datasets)
from io import StringIO
buffer = StringIO()
for item in items:
    buffer.write(f"{item.name}\n")
buffer.seek(0)
cursor.copy_from(buffer, 'items', columns=['name'])

Bulk Updates

-- Using UPDATE FROM VALUES
UPDATE items
SET price = v.price
FROM (VALUES
    (1, 10.00),
    (2, 20.00),
    (3, 30.00)
) AS v(id, price)
WHERE items.id = v.id;

-- Using unnest for arrays
UPDATE items
SET price = data.price
FROM unnest(
    ARRAY[1, 2, 3]::int[],
    ARRAY[10.00, 20.00, 30.00]::numeric[]
) AS data(id, price)
WHERE items.id = data.id;

UPSERT (INSERT ... ON CONFLICT)

INSERT INTO items (id, name, quantity)
VALUES (1, 'Widget', 10)
ON CONFLICT (id) DO UPDATE
SET quantity = items.quantity + EXCLUDED.quantity;

---

Query Patterns

Pagination

-- Offset pagination (simple, but slow for large offsets)
SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 1000;

-- Keyset pagination (fast, consistent)
SELECT * FROM items
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

Keyset pagination is O(1); offset pagination is O(offset).

Counting with Estimates

-- Exact count (slow for large tables)
SELECT count(*) FROM items WHERE active = true;

-- Estimate from statistics (fast, approximate)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'items';

-- Hybrid: exact for small, estimate for large
SELECT CASE
    WHEN c.reltuples < 10000 THEN
        (SELECT count(*) FROM items WHERE active = true)
    ELSE
        c.reltuples::bigint
END
FROM pg_class c WHERE c.relname = 'items';

EXISTS vs COUNT

-- BAD: Count when you only need existence
SELECT CASE WHEN count(*) > 0 THEN true ELSE false END
FROM orders WHERE user_id = 1;

-- GOOD: EXISTS stops at first match
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 1);

Returning Data After Insert

-- Instead of INSERT then SELECT
INSERT INTO users (name, email)
VALUES ('Jane', 'jane@example.com')
RETURNING id, created_at;

---

SQL File Management

Keep SQL in version-controlled files:

app/
  sql/
    users/
      get_by_id.sql
      search.sql
    orders/
      create.sql
      list_by_user.sql

Benefits:

• Version controlled
• Syntax highlighting
• Easy to test in psql
• Can be reviewed by DBAs

Loading SQL in Application

# Load SQL from files
def load_sql(name):
    with open(f'sql/{name}.sql') as f:
        return f.read()

GET_USER = load_sql('users/get_by_id')

# Use in queries
cursor.execute(GET_USER, {'id': user_id})

---

Monitoring Application Queries

pg_stat_statements

Track query performance across all sessions:

-- Enable extension
CREATE EXTENSION pg_stat_statements;

-- Find slowest queries
SELECT query,
       calls,
       mean_time,
       total_time / 1000 as total_seconds
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find most called queries
SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Application-Level Logging

Log slow queries in your application:

import time
import logging

def timed_query(cursor, sql, params=None):
    start = time.time()
    cursor.execute(sql, params)
    duration = time.time() - start

    if duration > 0.1:  # Log queries over 100ms
        logging.warning(f"Slow query ({duration:.3f}s): {sql}")

    return cursor

---

Common Mistakes

---

Key Takeaways

1. Network round-trips are expensive. Minimize them with JOINs, batch operations, and proper query design.

2. N+1 is the most common performance bug. Use eager loading or explicit JOINs.

3. ORMs hide important details. Understand the SQL they generate.

4. Use connection pooling. PgBouncer or driver-level pooling.

5. Put logic where it makes sense. Database for integrity, application for business rules.

6. Batch operations are faster. Use COPY, multi-value INSERT, bulk UPDATE.

7. Monitor your queries. pg_stat_statements and application-level logging.

8. SQL files are code. Version control them like any other code.

---

References

• Fontaine, D. The Art of PostgreSQL, Parts III-V
• Dombrovskaya, H. et al. PostgreSQL Query Optimization, Chapters 7-8
• PostgreSQL Documentation: libpq, psycopg2