Relational Databases: ACID Transactions and the Foundation of Data

Introduction

Relational databases organize information into tables and use keys to connect related records. Transactions make multi-step changes reliable, while constraints help keep stored data valid as applications read and write concurrently.

This guide covers core relational concepts, ACID behavior, normalization, indexes, query planning, and choices such as PostgreSQL versus MySQL. It also looks at scaling decisions, production failures, and when a relational model is a good fit.

Core Concepts

Relational databases provide guarantees around how transactions work. These guarantees have a memorable acronym: ACID.

Atomicity

Atomicity means a transaction happens completely or not at all. If you transfer money from account A to account B, either both the debit and credit happen, or neither does. You never end up with money deducted from A but not credited to B.

Database systems implement this through transaction logs. The database writes what it plans to do before doing it. If a crash happens mid-transaction, the log allows the system to either complete the operation or roll it back.

Consistency

Consistency ensures that every transaction moves the database from one valid state to another. Any constraints you define, like foreign keys or unique indexes, must hold after every transaction.

If you have a constraint that order.user_id must reference a valid users.id, the database rejects any transaction that tries to create an order with an invalid user_id. Consistency is your safety net for data integrity.

Isolation

Isolation determines how concurrent transactions interact. Multiple people querying the database at the same time should not see each other’s half-finished work.

If you transfer money from A to B while someone else reads your balance, isolation determines whether they see the old balance, the new balance, or something in between. Higher isolation levels prevent anomalies but reduce concurrency.

Durability

Durability means once a transaction commits, it survives system crashes. If you receive confirmation that a transaction succeeded, the data persists even if the database server loses power immediately after.

Most databases achieve this by writing to disk before reporting success. The write-ahead log technique ensures the database can replay incomplete transactions after a crash.

Transactions in Practice

Transactions group multiple operations into a single unit. PostgreSQL and MySQL both use the same basic syntax:

BEGIN;

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

COMMIT;

If the second UPDATE fails, the first one rolls back. Your balance stays at its original value.

The default isolation level in most databases is READ COMMITTED, which means you only see data that has been committed by other transactions. You can set stricter isolation:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SERIALIZABLE prevents most concurrency anomalies but can cause more lock contention and rolled-back transactions under heavy load.

Understanding Joins

Joins combine data from multiple tables. Understanding how they work is fundamental to writing efficient queries.

Inner Join

An inner join returns only rows that have matches in both tables. If you join orders to users, you only see orders that have a valid user_id.

SELECT users.email, orders.total, orders.created_at
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';

Left Join

A left join returns all rows from the left table, even if they have no matching rows in the right table. The right side columns show NULL for non-matching rows.

SELECT users.email, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

This query shows every user, even those who have never placed an order. Their order columns will be NULL.

Right Join and Full Outer Join

Right joins work the opposite way, returning all rows from the right table. Full outer joins return all rows from both tables, filling in NULLs where there is no match. These are less commonly used but important to understand.

Join Performance

Joins can get expensive. A naive join algorithm scans both tables and compares every row. With proper indexing and query planning, the database can do much better.

The query optimizer decides how to execute your join based on table statistics, available indexes, and estimated row counts. Sometimes the optimizer makes poor choices, which is when you need to understand EXPLAIN output.

EXPLAIN SELECT users.email, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Query Optimization Guide

The EXPLAIN family of commands is the main diagnostic tool for slow queries. Here is how to use it effectively.

-- Basic plan
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- With actual runtime stats (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders WHERE user_id = 42;

-- Costs and actual rows
EXPLAIN (ANALYZE, COSTS, VERBOSE) SELECT * FROM orders WHERE user_id = 42;

Common plan nodes to recognize:

Diagnosing slow queries:

1. Seq Scan on a large table returning few rows — You need an index. A query returning 100 rows from a 10M-row table doing a Seq Scan is a clear signal.

2. Estimated rows far from actual rows — Statistics are stale. Run ANALYZE table_name; to update them.

3. Hash Join spilling to disk — The query is running out of work_mem. Either increase it or restructure the query.

4. Filter placement and join plans — The planner can often treat equivalent INNER JOIN predicates in ON and WHERE the same way. Check EXPLAIN before rewriting; do not assume one form filters earlier.

-- Tables with more seq scans than index scans (missing indexes)
SELECT schemaname, tablename, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan * 10
ORDER BY seq_scan DESC;

-- Tables with high dead tuple percentage
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / (n_dead_tup + n_live_tup + 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Top queries by total time
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

Index patterns worth knowing:

-- Partial index for a common filter pattern
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Expression index when you filter on a function
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Include columns to enable index-only scans (PostgreSQL 11+)
CREATE INDEX idx_orders_user_include ON orders(user_id) INCLUDE (total, created_at);
-- This covers: SELECT total, created_at FROM orders WHERE user_id = ?

Query rewriting patterns:

-- Equivalent EXISTS form; compare plans before changing query shape
SELECT o.* FROM orders o
WHERE EXISTS (
    SELECT 1 FROM items i
    WHERE i.order_id = o.id AND i.product_id = 123
);

-- Multiple queries fetching one row each (N+1 pattern) — batch them
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
-- Becomes:
SELECT * FROM orders WHERE user_id IN (1, 2) ORDER BY user_id;

-- Aggregation by category — let PostgreSQL do it in one scan
SELECT status, count(*) FROM orders GROUP BY status;

Indexing for Performance

Indexes speed up data retrieval. Without indexes, the database must scan every row to find what you are looking for. With the right index, it jumps directly to the relevant pages.

How Indexes Work

Most database indexes use a structure called B-tree, which keeps data sorted and allows efficient range queries. When you create an index on a column, the database maintains a separate sorted structure pointing to the actual data rows.

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

These indexes let the database quickly find orders by user_id, filter by status, or range-query by date.

When Indexes Hurt

Indexes are not free. Every INSERT, UPDATE, or DELETE must update all relevant indexes. A table with many indexes writes slower than one with few indexes.

For columns with low cardinality (few unique values), indexes provide minimal benefit. An index on a boolean column rarely helps since half the rows match any query.

Composite Indexes

A composite index covers multiple columns. The order matters. A composite index on (user_id, created_at) helps queries that filter by user_id alone, or user_id plus created_at. It does not help queries that only filter by created_at.

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

Normalization and Its Trade-offs

Database normalization structures tables to reduce redundancy. Normal forms are stages of normalization, from 1NF (first normal form) to 3NF (third normal form) and beyond.

First Normal Form

1NF requires atomic values and no repeating groups. Each cell contains a single value, not a list or array. Each row is unique.

What “no repeating groups” looks like in practice: a phone_numbers column that tries to store multiple numbers as "555-1234, 555-5678" violates 1NF because it holds a collection inside one cell. The fix is a separate phone_numbers table with one row per number, each referencing the parent user row by foreign key.

Atomicity also rules out storing structured data as a string — a date of birth stored as "1990-05-15" is fine (one value), but "May 15, 1990" is not, because it requires parsing to extract the components. Your application should not need to parse what the database should already know.

Row uniqueness usually comes from a primary key. If you have no natural key, a surrogate key (auto-increment integer, UUID) ensures every row is distinguishable even when all other column values could theoretically repeat.

Second Normal Form

2NF removes partial dependencies. Non-key columns must depend on the entire primary key, not just part of it. This only becomes relevant when you have a composite primary key — a key made from two or more columns.

Consider an exam_results table with a composite primary key of (student_id, exam_id). If the table also stores student_email and student_name, those columns depend only on student_id — half the key. That is a partial dependency. The email and name will be duplicated for every exam a student takes, and if a student’s email changes you must update every row for that student.

The fix is to split the data: a students table holds student_id, name, and email, while exam_results only holds (student_id, exam_id, score). The exam_results table references students via a foreign key. Student information lives in one place, updated once, correct everywhere.

If your primary key is a single column (surrogate or natural), you are already at 2NF by definition — there is no “part” of a single-column key to depend on. Most OLTP tables use single-column surrogate keys and are automatically 2NF compliant.

Third Normal Form

3NF removes transitive dependencies. Non-key columns must not depend on other non-key columns — they should only depend on the primary key directly. If column A determines column B, and B determines column C, then C transitively depends on A through B. This is the transitive dependency 3NF eliminates.

A common example: an orders table with (order_id, customer_id, customer_city, customer_zip). Customer_city and customer_zip depend on customer_id, not on order_id. If the customer’s city changes, you must update every order row for that customer. The fix is a customers table with (customer_id, city, zip), and orders references customers by customer_id.

In practice, most 3NF violations look like this: storing derived or lookup data directly on the transaction table. Customer city, region, discount tier, and sales rep all commonly appear on an orders table when they should live in their own tables. When you find yourself writing “WHERE customer_region = ‘northeast’” as a filter on the orders table and also asking for customer_region from the orders table in SELECTs, that is a smell — customer data is transitively dependent on order_id through customer_id.

3NF and 2NF together mean every non-key column describes the primary key and nothing else. If a column describes something else in your table, it belongs elsewhere.

-- Not normalized: total depends on quantity and price
CREATE TABLE order_items_bad (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price DECIMAL(10, 2),
    total DECIMAL(10, 2)  -- This depends on quantity * price
);

-- Normalized: total is calculated, not stored
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price DECIMAL(10, 2)
);

Normalization reduces data duplication. Denormalization (deliberately introducing redundancy) can improve read performance. Both have their place.

PostgreSQL vs MySQL

Both PostgreSQL and MySQL are excellent relational databases. They share most SQL syntax but differ in implementation details.

PostgreSQL offers more advanced features: better support for JSON operations within a relational context, full-text search, window functions, and more sophisticated indexing options. It follows SQL standards more strictly.

MySQL has historically been faster for simple read-heavy workloads. Its default storage engine, InnoDB, provides ACID transactions and row-level locking. MySQL syntax sometimes diverges from SQL standards.

Both handle replication, backups, and high availability. PostgreSQL has better support for complex data types and operations. MySQL has a larger ecosystem of managed services and tooling.

For most applications, the difference matters less than you might think. Pick one and learn it well. Switching between them is rarely necessary and comes with significant migration costs.

Sharding and Replication Deep Dive

Horizontal scaling for relational databases takes two forms: sharding and replication. They solve different problems.

Read Replicas and Replication Topologies

Replication streams data from a primary to one or more replicas. Reads go to replicas, reducing load on the primary. Writes always go to the primary, which pushes them to replicas after the fact.

-- PostgreSQL streaming replication setup (primary)
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
-- Restart PostgreSQL after changing these server-start settings.

-- Create replication slot on primary
SELECT pg_create_physical_replication_slot('replica1_slot');

-- On the replica, configure primary_conninfo and create standby.signal
-- primary_conninfo = 'host=primary_ip port=5432 application_name=replica1'
-- slot_name = 'replica1_slot'
-- recovery_target_timeline = 'latest'

Replication topologies:

Sharding Patterns

Sharding partitions data across multiple databases. Each shard holds a subset, selected by a shard key.

-- Application-level sharding by user_id hash
-- Shard key = hash(user_id) % num_shards

CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    shard_key INTEGER GENERATED ALWAYS AS (user_id % 4) STORED,
    total DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Shard 0, 1, 2, 3 are separate physical databases
-- Query router directs traffic based on shard_key

Sharding strategies:

When to shard:

• Data no longer fits on one server
• Write throughput outpaces a single primary
• Compliance demands specific geographic data residency

When NOT to shard:

• A single server handles your data fine
• You are optimizing prematurely
• Better indexing or caching would solve the problem first

Distributed Consistency Trade-offs

Relational databases trade horizontal write scalability for strong consistency. NoSQL systems make the opposite trade. Most applications never hit the ceiling on a well-tuned relational setup — but engineers often jump to NoSQL before exhausting what relational databases can do.

ACID vs BASE Trade-offs

ACID and BASE are two different philosophies on how to handle consistency in distributed systems. ACID is what relational databases do. BASE showed up with NoSQL.

The ACID Model

ACID gives you four guarantees. Here is why each one matters for reasoning about failure modes:

• Atomicity prevents partial writes. If you are transferring funds between accounts and the database crashes after debiting the source but before crediting the destination, atomicity ensures the debit is rolled back. Without it, money disappears. PostgreSQL implements this via the transaction log: every change is written to the WAL before it is applied. If a crash occurs, the recovery process reads the log and undoes uncommitted changes.

• Consistency is not a property of the database alone — it depends on the constraints you define. A foreign key constraint, a unique index, a check constraint like price > 0 — these are the consistency rules you write. The database enforces them on every transaction. If a transaction would leave a constraint violated, the database rejects it. This is why defining the right constraints matters as much as the transaction logic itself.

• Isolation controls what concurrent transactions see. Without it, a transaction reading the same row as another writer might see half-finished changes. Different isolation levels offer different trade-offs between what you see and what performance you get. At READ COMMITTED (the default in most databases), each query sees only rows committed before the query started. At SERIALIZABLE, the database effectively runs every transaction as if it were the only one running — no concurrent anomalies are possible, but lock contention rises.

• Durability means once the database reports success, the data survives power loss, kernel panics, or hard crashes. The mechanism is the same WAL used for atomicity: the database does not report commit until the log record is flushed to disk. fsync calls are the bottleneck here — write-heavy OLTP workloads are sensitive to disk latency, which is why battery-backed write cache on RAID controllers is a common production configuration.

The BASE Model

BASE is an acronym that describes what you get when you prioritize availability and write scaling over immediate consistency. It is a reaction to ACID’s coordination overhead — in exchange for giving up strong consistency, you get systems that stay available under network partitions and scale horizontally more easily.

• Basically Available means the system continues serving reads (and possibly writes) even when some nodes are down or unreachable. This is not the same as “always available” — it means the subset of nodes that are reachable will handle requests. A partition that splits your cluster in half means one half can keep serving writes while the other half is unavailable.

• Soft state means the system intentionally allows its view of data to diverge between nodes during normal operation. Unlike ACID systems where every node agrees on the current state, BASE systems accept that at any given moment, different nodes may have different values for the same row. The row is not “wrong” — it is simply at different stages of propagation across the cluster.

• Eventually consistent means that given enough time without new writes, all nodes will converge to the same value. “Enough time” is intentionally unspecified — it could be milliseconds or minutes depending on network latency, cluster load, and the conflict resolution strategy used.

The practical implication of soft state is that your application must handle read inconsistencies directly. If you write a row and immediately read it from a different node, you might get the old value. This is why many BASE systems offer “read your own writes” consistency only within a single session or client — the guarantee does not extend across nodes. Applications using Cassandra, DynamoDB, or similar systems commonly implement read-repair logic or accept stale reads for the performance gain.

graph LR
    A[Write to Node A] --> B{Quorum?<br/>W + R > N}
    B -->|Yes| C[Strong Consistency]
    B -->|No| D[Eventual Consistency]
    D --> E[Read from Node B<br/>Possible stale data]
    C --> F[All nodes converge]

ACID vs BASE Comparison

When to Pick Each

Go with ACID/relational when data integrity matters more than anything else — financial records, inventory, medical data. When you need complex transactions across multiple tables, or when your queries involve joins that would be a nightmare in application code.

Go with BASE/NoSQL when write throughput is the hard problem and you can tolerate reads that might be slightly behind. Document-oriented or key-value workloads that do not map naturally to tables fit here. So do use cases that need automatic sharding across geographic regions.

Hybrid Systems

A few modern databases try to split the difference:

• Google Spanner: TrueTime hardware (specialized GPS + atomic clocks) enables strongly consistent distributed transactions with geographic replication. The hardware is the key.
• CockroachDB: Distributed SQL without special hardware — uses Hybrid Logical Clocks instead. Operationally simpler than Spanner, slightly more latency on distributed transactions.
• YugabyteDB: PostgreSQL-compatible, uses Raft consensus for distributed consistency.
• Aurora: MySQL/PostgreSQL-compatible with a distributed storage layer that replicates across availability zones. Not truly distributed writes — still a single primary — but the storage layer handles replication and durability.

These systems give you relational semantics with distributed scale. The cost is operational complexity — running a distributed SQL cluster is harder than running a single PostgreSQL instance.

Trade-off Analysis

Use this table to guide architectural decisions based on workload characteristics:

When to Use / When Not to Use

When to Use Relational Databases:

• Your data has clear entity relationships that map naturally to tables
• You need ACID transactions for financial data, inventory, or orders
• Your queries involve complex joins across multiple tables
• You need powerful ad-hoc querying with SQL for analytics
• Your workload is balanced between reads and writes
• You need mature tooling, backups, and operational procedures

When Not to Use Relational Databases:

• Your data structure varies dramatically between records (document storage fits better)
• You need to store and query massive semi-structured data like JSON logs
• Your primary access pattern is single-key lookups at extreme scale
• Your writes massively outnumber reads and you need horizontal write scaling
• You are building a graph-based application with complex relationship traversals

Production Failure Scenarios

Common Pitfalls / Anti-Patterns

1. Missing indexes on foreign keys: Always index foreign key columns. Without indexes, parent-child joins cause full table scans.

2. N+1 query problems: Fetching related data in a loop instead of using joins. A single query returning 100 users with their orders is better than 101 queries.

3. Over-indexing: Creating indexes “just in case” hurts write performance. Every index slows down INSERT/UPDATE/DELETE.

4. Using triggers for business logic: Triggers hide side effects and make debugging harder. Handle logic in application code.

5. Ignoring NULL semantics: NULL is not “empty string” or “zero”. Queries behave differently with NULLs, and mixed NULL handling causes subtle bugs.

6. Storing serialized objects or arrays in text columns: While convenient, this bypasses indexing and query capabilities. Use proper data types or a document store.

7. Ignoring connection pooling: Each connection consumes memory. Use PgBouncer or similar to pool connections and avoid connection exhaustion.

8. Not using prepared statements: Parsing overhead adds up. Use prepared statements for repeated queries.

9. Mismatched types in joins: Joining VARCHAR to INTEGER causes implicit casts and prevents index usage. Always match types.

Quick Recap Checklist

Key Takeaways

• Relational databases provide ACID guarantees that keep your data consistent and durable
• Indexes are essential for read performance but slow down writes
• Normalization reduces redundancy; denormalization improves read performance
• Transactions group operations into atomic units that commit or rollback together
• Choose PostgreSQL for advanced features and standards compliance, MySQL for simple read-heavy workloads
• Monitor query latency, connection counts, and replication lag in production

Copy/Paste Checklist:

-- Essential performance monitoring queries
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT * FROM pg_stat_replication;
SELECT relname, n_dead_tup, last_vacuum FROM pg_stat_user_tables;
EXPLAIN (ANALYZE, BUFFERS) your_query_here;

-- Essential security settings
-- Set shared_preload_libraries = 'pg_stat_statements' in postgresql.conf, then restart
ALTER ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Essential backup verification
-- Restore a configured base backup to an isolated instance and verify it starts.

Observability Checklist

Metrics to Monitor:

• Query latency (p50, p95, p99)
• Active connections and connection saturation
• Transaction throughput (commits/rollbacks per second)
• Lock wait time and lock contention rates
• Cache hit ratio (shared_buffers)
• Disk I/O utilization and queue depth
• Replication lag in seconds
• Autovacuum activity and table bloat percentage
• Index usage and missing indexes

Logs to Capture:

• Slow query log (queries exceeding threshold, e.g., 100ms)
• Error logs with full context including query parameters
• Connection logs for authentication failures
• Lock deadlocks and lock timeout events
• Checkpoint and bgwriter activity
• Autovacuum execution details

Traces to Capture:

• Propagate request trace IDs from the application to database client spans; capture pool wait, query duration, rows processed, lock wait, and retry count.
• Use normalized query or operation names, and keep bind values and result rows out of spans to avoid exposing sensitive data.

Alerts to Set:

• Connection count > 80% of max_connections
• Replication lag > 30 seconds
• Disk usage > 85% on data volume
• Long-running queries (> 60 seconds)
• Cache hit ratio < 90%
• Lock wait time spike
• Autovacuum failing repeatedly
• Replication slot age exceeding retention

-- Quick visibility queries
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT * FROM pg_stat_replication;
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum FROM pg_stat_user_tables;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

Security Checklist

• Use strong authentication (scram-sha-256 or certificate-based, never md5)
• Implement least-privilege access (users get only needed permissions)
• Encrypt connections with SSL/TLS (set ssl = on and enforce in pg_hba.conf)
• Encrypt data at rest (use filesystem encryption or database-level encryption)
• Enable and review audit logging for sensitive operations
• Configure shared_preload_libraries = 'pg_stat_statements' at server startup, then create the extension in each database where it is needed
• Restrict pg_hba.conf to specific IP ranges, deny public access
• Regularly apply security patches and update minor versions
• Use network segmentation (database not directly internet-accessible)
• Implement row-level security for multi-tenant scenarios
• Backup encryption keys separately from encrypted backups
• Test restore procedures in isolation from production

Real-World Case Studies

Netflix: From Oracle to PostgreSQL at Massive Scale

Netflix ran on Oracle for years — billions of rows, thousands of tables, serious hardware. In 2012 they migrated to PostgreSQL running on AWS RDS. The migration took two years of preparation, and the main motivation was cost: Oracle licensing was pricing them out at their scale.

The migration was incremental. They did not do a big-bang switch. They ran dual-write for months — writing to both Oracle and PostgreSQL simultaneously while comparing results. They built custom validation tools to catch any divergence. When they cut over, the decision was purely operational: flip a routing switch and watch the metrics.

The lesson: database migrations at scale are infrastructure problems more than database problems. The schema transfer was straightforward. The hard part was the two years of tooling, validation, and operational runbook development that made the switch boring.

The Netflix migration had several phases. They started by instrumenting Oracle to capture every query pattern, then ran regression tests against PostgreSQL to surface incompatibilities before anything touched production. Next came a data validation pipeline that compared row counts, checksums, and query results between Oracle and PostgreSQL in real time. Finally, they used traffic shadowing: production load got replayed against PostgreSQL in parallel, with results compared but not used for serving. Only after shadow traffic validated the target did they flip the write path.

Patience was the real ingredient. Two years sounds extreme until you consider the blast radius: a mistake on a database serving 50 million subscribers means a long rollback with real user impact. The operational runbooks they built, covering validation failures, rollback triggers, and monitoring dashboards, mattered more than the database migration itself. The schema moved once. The infrastructure to validate and switch safely took the rest of the timeline.

Amazon: Why They Left Oracle for PostgreSQL and MySQL

Amazon’s consumer-facing databases migrated away from Oracle around 2013-2017. The stated reasons were licensing cost and control. At Amazon’s scale, Oracle’s per-core licensing model was a serious line item.

Amazon’s internal Postgres-compatible database, Aurora, launched in 2014. The architecture is not a vanilla Postgres — it separates the storage layer and replicates across three availability zones. This gives MySQL/PostgreSQL compatibility with durability characteristics no traditional RDS could match.

The takeaway: the database you pick shapes your operational constraints for years. Amazon built their own storage engine because off-the-shelf could not give them the durability they needed at their write volume. Most companies will not hit that ceiling, but it is worth knowing where the ceiling is.

Aurora’s core idea is straightforward: traditional RDS replicates the entire database instance, including the buffer cache and WAL replay logic. The primary must replay writes sequentially to all replicas, and storage I/O competes with compute I/O on the same disk. Aurora separates these by making the storage layer handle durability and replication while the compute layer processes queries without managing disk writes. The storage layer replicates across three AZs, acknowledges writes when quorum is reached, and handles crash recovery on its own. When a primary fails, the new primary does not replay WAL from scratch because the storage layer already holds all committed data.

The result is write throughput limited by the storage layer’s quorum write latency instead of traditional WAL replay overhead. For Amazon’s product catalog and transaction processing, this meant higher write rates than Oracle while maintaining six-nines durability. The cost is operational complexity: Aurora’s distributed storage layer behaves differently from vanilla PostgreSQL when things break, and debugging it requires different tooling than standard PostgreSQL troubleshooting.

Interview Questions

Further Reading

• PostgreSQL Documentation: The Query Planner — Deep dive into how PostgreSQL chooses execution plans
• MySQL 8.0 Reference Manual: Optimization — MySQL-specific optimization techniques
• “Designing Data-Intensive Applications” by Martin Kleppmann — Essential reading on distributed systems and data storage trade-offs
• PostgreSQL wiki: EXPLAIN plans — Community guide to reading EXPLAIN output
• CiteScale Paper on ACID vs BASE — Why distributed systems compromise consistency
• AWS Aurora Architecture — How Aurora implements distributed relational storage
• NoSQL Databases — Compare relational databases with other data models
• Database Scaling — Explore techniques for growing beyond one database instance

Database Engine Internals Deep Dive

PostgreSQL’s storage engine is where ACID guarantees actually live. Once you see how PostgreSQL lays out data on disk, how it decides which rows a transaction can see at any moment, and how it recovers from a crash, the database stops being a black box. Three things matter most in practice: readers and writers not blocking each other, data getting to disk safely, and space getting reclaimed when rows change.

These details sound abstract until something breaks. A query that suddenly slows down might be hitting lock contention you cannot see. A table that keeps growing for no obvious reason might have dead tuples piling up faster than autovacuum can clean. A crash recovery that takes longer than expected might be replaying WAL entries unnecessarily because checkpoints are not firing on schedule. Knowing the internals turns vague hunches into actual diagnoses.

MVCC, the write-ahead log, and autovacuum come up most often when you are debugging production issues. MVCC handles the concurrency trick that lets PostgreSQL do consistent reads without locking writers. The WAL is the reason your data survives crashes. Autovacuum keeps the system from choking on its own garbage. These three are the engine underneath everything else.

MVCC and Read Consistency

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to allow readers and writers to not block each other. When you UPDATE a row, PostgreSQL creates a new version while keeping the old version until no transaction needs it. Each transaction sees a snapshot based on its start time — this is why READ COMMITTED works as it does.

-- See the current transaction snapshot
SELECT txid_current_snapshot();

-- Check which rows are visible to current transaction
SELECT * FROM my_table WHERE ctid = '(0,1)';

-- Monitor tuple visibility and age
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

The visibility map tracks which pages contain only tuples visible to every current and future transaction, as well as which pages contain only frozen tuples. An index-only scan can skip a heap fetch when the page is marked all-visible. Vacuum updates these bits, so regular vacuuming helps keep index-only scans effective.

WAL and Crash Recovery

The Write-Ahead Log (WAL) is the foundation of PostgreSQL durability. Every change is written to the WAL before being applied to data pages. On crash, PostgreSQL replays the WAL to reach a consistent state.

Here is how the recovery process works: on startup after a crash, PostgreSQL reads the last checkpoint (a marker in the WAL that says “the data pages at this point are consistent”). It then replays all WAL entries after that checkpoint, reapplying committed changes and rolling back uncommitted ones. This brings the data pages to the state that would exist if every committed transaction had completed normally.

The rule is: the WAL record for a data change must reach disk before PostgreSQL writes the data page. If a crash happens after the WAL record is on disk but before the data page is written, recovery replays the change from WAL and gets the correct result. If the crash happens before the WAL record reaches disk, the data page never gets the change — recovery has nothing to replay, so you also end up consistent. Either way works.

Checkpoints are the anchor points that limit how far recovery must replay. A checkpoint writes enough information to the WAL that the recovery process knows: “from here forward, I can reconstruct the current state by replaying WAL entries.” Without checkpoints, recovery would have to replay from the beginning of the WAL — potentially days or weeks of entries on a busy system. Checkpoints happen automatically (controlled by checkpoint_timeout and checkpoint_completion_target) but you can also trigger them manually with pg_checkpoint.

-- Check WAL activity and size
SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');

-- Force a checkpoint (admin task, use carefully)
CHECKPOINT;

-- Check wal usage per database
SELECT datname, total_wal_bytes FROM pg_stat_database;

Set wal_level = logical when you need logical decoding for change data capture. This server-start setting includes the WAL information available at the replica level; configure archiving separately when you need point-in-time recovery.

Autovacuum Tuning

Autovacuum prevents table bloat by reclaiming dead tuples. Every UPDATE creates a new row version and marks the old one as dead. Every DELETE marks the row as dead. Without vacuum, dead tuples accumulate — PostgreSQL still scans them during index-only scans, query planners misestimate row counts, and performance degrades. Autovacuum runs in the background and reclaims these dead tuples.

The default autovacuum trigger is 20% dead tuples — a table only gets vacuumed when more than a fifth of its rows are dead. On a 10 million row table, that means 2 million dead rows can pile up before vacuum kicks in. High-write tables often need more aggressive settings. autovacuum_vacuum_scale_factor controls this threshold; setting it to 0.01 (1%) makes vacuum trigger at 1% dead tuples instead of 20%.

autovacuum_analyze_scale_factor controls when statistics are refreshed. PostgreSQL’s query planner relies on table statistics to estimate row counts and selectivity. With the default of 10%, statistics can become stale on fast-growing tables. Setting this to 0.005 (0.5%) keeps statistics fresher at a modest extra cost.

autovacuum_vacuum_cost_delay defaults to 20ms, which is a sleep between batches to avoid saturating I/O. For tables where vacuum speed matters more than I/O impact, setting this to 2 or 0 (in newer PostgreSQL versions) makes vacuum run much faster. The tradeoff is more I/O pressure during vacuum.

The most common bloat scenario: a long-running transaction holds a snapshot open. If a query starts a transaction and stays idle but open for hours, autovacuum cannot reclaim dead tuples that were created after the snapshot began — the vacuum sees them as still visible to that transaction. This is why long-running reporting queries or open database connections from connection pool leaks cause bloat on specific tables.

-- Table-specific autovacuum tuning for high-write tables
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- Trigger at 1% dead tuples vs default 20%
    autovacuum_analyze_scale_factor = 0.005, -- Analyze at 0.5% changes vs default 10%
    autovacuum_vacuum_cost_delay = 2         -- Lower cost delay for faster cleanup
);

-- Monitor autovacuum activity
SELECT relname, last_autovacuum, autovacuum_count,
       COALESCE(ROUND(n_dead_tup::numeric / (n_live_tup + n_dead_tup + 1) * 100, 2), 0) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Manual vacuum for emergency cleanup
VACUUM verbose orders;

Connection Pooling with PgBouncer

PgBouncer reduces connection overhead by pooling connections to PostgreSQL. It sits between your application and the database: your app connects to PgBouncer, and PgBouncer multiplexes those client connections across a smaller pool of actual PostgreSQL connections.

PostgreSQL’s architecture is connection-per-process — each connection spawns a worker process. A server with 8 CPU cores can handle perhaps 200-300 active connections efficiently. Your application, if it uses a connection per request (or worse, a connection per transaction), might need 1000 simultaneous connections. PgBouncer bridges this gap: 1000 clients share a pool of 20 PostgreSQL connections.

pool_mode = transaction is the most common setting for modern applications. In this mode, PgBouncer grabs a backend connection from the pool when your transaction begins and returns it to the pool when the transaction commits or rolls back. Your application never holds a backend connection between transactions. This means you cannot use prepared statements that persist across transactions (use pool_mode = session if you need that).

max_client_conn is the total number of client connections PgBouncer will accept. Set this based on your application’s connection pool size, not PostgreSQL’s limit. default_pool_size is how many PostgreSQL connections PgBouncer maintains per database/user combination. Set this to what PostgreSQL can handle under load — 20 is a reasonable starting point for a default, with more for read-heavy workloads using streaming replication.

reserve_pool_size and reserve_pool_timeout handle burst scenarios. If all 20 connections are in use and a new client connects, PgBouncer can bring up to reserve_pool_size extra connections temporarily. These extra connections are closed after reserve_pool_timeout seconds of inactivity.

PgBouncer cannot route a client to a different database than the one they connected to — a client connected to mydb uses connections to mydb in PostgreSQL, nothing else. For multi-database setups, configure each database as a separate entry in the [databases] section.

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

With pool_mode = transaction, PgBouncer disconnects the backend connection when the client transaction ends. Your application never holds a backend connection between transactions, allowing you to support many more concurrent application connections than PostgreSQL can handle.

Conclusion

Relational databases remain relevant because they solve real problems. ACID transactions give you confidence your data stays consistent. Joins let you ask complex questions across related data. Indexes make queries fast. Normalization keeps your data from becoming a mess.

PostgreSQL and MySQL both handle these concerns well. The features overlap significantly. My preference leans toward PostgreSQL for its feature set and standards compliance, but MySQL is equally capable for most workloads.

For related reading, see my posts on NoSQL Databases to understand when alternative data models make sense, and Database Scaling to learn how to handle growth beyond a single database instance.