Cơ sở dữ liệu quan hệRelational Databases

Part of the Data Engineer Roadmap knowledge base.

Overview

Most data pipelines start their life at a relational database. Somewhere behind the application your company runs — the checkout service, the CRM, the billing system — sits a PostgreSQL, MySQL, or SQL Server instance quietly doing the thing relational databases are best at: recording individual business events, one row at a time, with strict correctness guarantees. As a data engineer, that database is not “your” database. You do not own its schema, you did not design its indexes, and you are, from its point of view, an uninvited guest. Your job is to get data out of it — reliably, incrementally, and without breaking the application that depends on it.

This note assumes you already know what a relational database is: tables, rows, SQL, ACID transactions, normal forms, indexes. That ground is covered in depth in Backend — Relational Databases, and this note leans on it rather than repeating it. What it adds is the data engineer’s angle on the same technology: why the database that is perfect for the application is hostile to the analytical queries you want to run against it, how transaction isolation and indexing change the way you write extraction queries, why replicas exist, and how the major RDBMS engines differ in the one capability data engineers care about most — how cheaply and reliably you can get a stream of changes out of them. Everything here feeds directly into Data Sources & Ingestion (how you actually pull the data) and Data Modeling & Warehousing (what shape the data takes once it lands).

Fundamentals

OLTP vs. OLAP: source vs. destination

The single most important mental model in this note is the split between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems. It is not just a technical distinction — it is the reason the entire discipline of data engineering exists. If one database could serve both workloads equally well, there would be no need to extract, transform, and load data anywhere; applications would just query production directly for their dashboards. They can’t, so we build pipelines.

An OLTP system is optimized for many small, fast, concurrent transactions: INSERT INTO orders ..., UPDATE inventory SET qty = qty - 1 WHERE sku = ..., one row (or a handful of related rows) at a time, thousands of times per second, from thousands of concurrent users. Its schema is normalized (see below) to make single-row writes cheap and safe. Its indexes are built to make point lookups (WHERE id = ?) and small range scans fast.

An OLAP system is optimized for the opposite workload: few, large, read-heavy queries that scan and aggregate millions or billions of rows — “total revenue by region by month for the last three years.” Its schema is denormalized (star/snowflake) to minimize joins, its storage is often columnar, and it is built to scan huge swaths of data efficiently rather than to update single rows quickly.

Why you don’t run analytics directly on production OLTP

Given this split, an obvious question is: why not just run your GROUP BY reporting query straight against the production Postgres database and skip the pipeline entirely? For a small app, people do exactly that for a while — and then they stop, for a few concrete reasons.

Lock contention and blocking. A long-running analytical SELECT that scans a large table can, depending on isolation level and engine, hold read locks or force the storage engine to keep old row versions around (see MVCC below) for the duration of the query. Meanwhile the application is trying to UPDATE and INSERT into that same table. In the worst case (older MySQL/InnoDB configurations, or databases without proper MVCC) you get genuine lock waits: the application’s write blocks behind your report. Even with MVCC engines that avoid classic reader/writer blocking, a long-running transaction can still block DDL, prevent vacuum/cleanup from reclaiming space, and hold back checkpoint or WAL truncation.

Resource contention. Even without locking, an analytical scan competes for the same finite resources as production traffic: CPU, disk I/O, the buffer cache/shared buffer pool, and network bandwidth. A SELECT SUM(total_cents) FROM orders over 50 million rows will happily evict the application’s hot working set out of the cache, causing every subsequent OLTP query to fall back to disk. Your checkout page gets slower not because of a bug, but because someone ran a report.

The optimizer picks the wrong plan for the job. An OLTP-tuned engine and its statistics are optimized for the queries the application runs — index lookups on a handful of rows. An ad-hoc analytical query with several joins and aggregations over most of the table can trigger a full table scan, or a bad join plan, that a warehouse engine (built and tuned for exactly that workload) would handle far more gracefully.

Blast radius and access control. Production databases hold PII, payment data, and other sensitive fields under strict access controls. Running arbitrary analytical queries against production forces you to either grant broad read access to analysts (a security/compliance problem — see Data Security & Compliance) or funnel every question through the data team as a bottleneck.

This is the core motivating argument for the entire ETL/ELT discipline (see ETL/ELT & Data Pipelines): copy the data, on your own schedule, using a method that minimizes impact on the source, into a system built for the analytical workload, and let people query that as hard as they want.

Transactions and isolation: a data engineer’s recap

Backend — Relational Databases covers ACID and the four SQL isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) in full; the short version for a data engineer is this: the isolation level of the transaction your extraction job runs under determines what “consistent” means for the snapshot you just pulled.

If your extraction query runs at READ COMMITTED (the default in PostgreSQL and SQL Server) and you extract from multiple tables in the same logical batch — say, orders and order_items — without wrapping the whole extraction in a single transaction, it is possible for a concurrent write to insert a new order and its items between your two SELECTs, or worse, for you to see the order but not yet its items (because the application’s transaction that inserts both hasn’t committed the items row yet, or committed it a moment after your read). The result is a set of extracted files that are individually valid rows but jointly inconsistent — an order with no items, or a foreign key in your extract that points to a row you haven’t extracted yet.

Two practical mitigations:

• Run the whole extraction as a single transaction at REPEATABLE READ (PostgreSQL calls this “repeatable read”; it gives you a consistent snapshot as of the transaction’s start via MVCC, at no extra locking cost for readers). This guarantees every table you read within that transaction reflects the same point in time.
• Prefer log-based/CDC extraction (binlog, WAL) over ad-hoc snapshot queries for anything that needs strict cross-table consistency, because the replication log preserves transaction boundaries — you can group changes by the source transaction ID and apply them atomically downstream.

The practical failure mode to remember: READ UNCOMMITTED (dirty reads) has no business anywhere near a production extraction job — you could pull rows from a transaction that later rolls back, silently corrupting your warehouse with data that never actually happened.

Indexing for extraction performance

The backend note explains what a B-tree index is and why it speeds up point lookups; the data engineering angle is narrower and very concrete: your extraction query’s WHERE and ORDER BY clauses must line up with an existing index, or you will full-table-scan a production table on every run.

The dominant extraction pattern is incremental extraction using a watermark column — typically an updated_at timestamp or a monotonically increasing id/version column that the application already maintains (often for its own auditing purposes, which is convenient). Each run of the pipeline records the maximum watermark value it saw, and the next run asks only for rows newer than that:

-- The watermark from the previous successful run is stored in your
-- orchestration metadata (e.g. an Airflow Variable, a control table, a file in S3).
SELECT id, customer_id, total_cents, status, updated_at
FROM   orders
WHERE  updated_at > :last_watermark
ORDER BY updated_at
LIMIT  10000;   -- paginate in batches; re-query with the new max(updated_at) as you go

This query is cheap only if updated_at is indexed:

CREATE INDEX idx_orders_updated_at ON orders (updated_at);

Without that index, WHERE updated_at > :last_watermark forces a sequential scan of the entire table on every extraction run — reading every page of orders from disk (or evicting the buffer cache) just to throw away all but a handful of recently changed rows. On a 200-million-row orders table running hourly, that is 24 full scans a day competing with production traffic for exactly the resource-contention reasons described above. With the index, the same query is an index range scan that touches roughly as many pages as there are new/changed rows since the last run — orders of magnitude cheaper, and its cost stays flat as the table grows.

A few practical wrinkles worth knowing:

• ORDER BY updated_at matters, not just the filter. You want deterministic pagination and a safe place to resume if the job fails mid-batch; sorting by an unindexed column defeats the whole point even if the filter itself uses an index, because the engine may still need to sort the full matching set in memory or on disk.
• Ties at the watermark boundary are a real bug source. If two rows share the exact same updated_at, a naive WHERE updated_at > :last_watermark LIMIT N can skip one of them if it falls outside the current page. Common fixes: use a composite watermark (updated_at, id) with WHERE (updated_at, id) > (:last_ts, :last_id), or accept a small overlap window and de-duplicate downstream on the primary key.
• updated_at must actually be maintained on every write path, including bulk updates, admin scripts, and migrations. A single UPDATE orders SET status = 'refunded' run by hand without touching updated_at is invisible to your pipeline forever. This is an application-schema contract, not something the data team controls alone — flag it during onboarding of a new source.
• Hard deletes are invisible to a watermark scan. If a row is physically deleted rather than soft-deleted (is_deleted flag) or tombstoned, an updated_at-based query will never tell you it’s gone. Log-based CDC (below) is the only reliable way to capture deletes.

Normalization vs. denormalization: the tension that creates ETL

Backend — Relational Databases explains 1NF/2NF/3NF in detail; the data engineering point is what normalization is for and why the warehouse deliberately throws it away.

OLTP schemas are normalized because normalization protects write integrity: a customer’s address lives in exactly one row, so updating it updates it everywhere at once, and no update can leave two copies of the same fact disagreeing with each other. The cost of that protection is that reading a “complete” business object — an order with its customer, its line items, its products, its shipping address — requires joining five or six tables. For a single order, that’s fine. For an aggregation over ten million orders, five or six joins across normalized tables is exactly the query pattern that struggles on an OLTP engine and that a warehouse is built to avoid.

Data warehouses invert the trade-off: they accept redundancy (the same customer name copied into every order-fact row) in exchange for read speed, using a deliberately denormalized star schema — one central fact table (e.g., fact_orders) surrounded by wide, flattened dimension tables (dim_customer, dim_product, dim_date) that require few or no joins for typical aggregation queries. The full mechanics of star/snowflake schema design live in Data Modeling & Warehousing; the point to internalize here is that this exact tension — normalized-for-writes vs. denormalized-for-reads — is why a transformation step has to exist between source and warehouse at all. You cannot point a BI tool at a normalized OLTP replica and get warehouse-grade aggregation performance; someone has to do the join-and-flatten work, once, ahead of time, which is what “T” (Transform) in ETL and the modeling layer in ELT (dbt, etc.) actually do.

Key Concepts

Extracting from a replica instead of the primary

The single most common production pattern for protecting OLTP performance from data engineering workloads is simple: never run extraction queries against the primary; run them against a read replica.

Most RDBMS engines support asynchronous (or, more rarely, synchronous) replication: a read replica continuously applies the primary’s write-ahead log (or binlog) and stays a few milliseconds to a few seconds behind. Pointing extraction jobs, CDC log readers, and ad-hoc analyst queries at a replica means:

• Query load, table scans, and long-running transactions on the replica have zero direct impact on the primary’s write throughput or latency.
• You can afford to be less careful about isolation and indexing on the replica (within reason) because the blast radius of a bad query is contained to a non-critical instance.
• If the replica falls too far behind (replication lag), you risk extracting stale data, so lag itself becomes something to monitor and alert on (see Monitoring & Observability) — a batch job that reads from a replica lagging by 40 minutes silently produces a warehouse that’s 40 minutes staler than you think.
• Some engines let you configure a dedicated analytics/reporting replica distinct from the replica used for failover/HA, so a runaway extraction query doesn’t threaten your disaster-recovery posture either.

The trade-off is read-after-write consistency: if the application writes a row and your extraction job reads from a lagging replica milliseconds later, that row may not be visible yet. For most analytical use cases (yesterday’s revenue, this week’s signups) this is irrelevant; for anything requiring strict “as of now” correctness, you either read from the primary for that specific query or account for the lag explicitly.

Log-based CDC as the alternative to querying at all

Watermark-based polling (the updated_at pattern above) is simple and works with any RDBMS, but it has real limits: it misses hard deletes, it can miss rapid intermediate updates between polling intervals (you only ever see the latest state, not the history of changes), and it still runs a SELECT against the database on every schedule tick, however cheap.

Change Data Capture (CDC) takes a different approach: instead of querying tables, you read the database’s internal replication log — the same stream a read replica consumes to stay in sync — and turn each logged row-level change (insert/update/delete) into an event. This is strictly more complete (deletes and every intermediate state are captured) and imposes near-zero query load on the source, because you’re tailing a log the database already writes for its own replication purposes, not running SELECTs against live tables. Tools like Debezium, AWS DMS, and Fivetran’s CDC connectors work this way. CDC mechanics and streaming delivery are covered in depth in Streaming & Real-Time Data; what matters here is which underlying RDBMS log format each engine exposes, since that dictates what CDC tooling can even connect to.

RDBMS engines compared for data engineering

The four mainstream engines you’ll meet as source systems differ meaningfully in exactly the capabilities that matter for extraction: what replication/logging mechanism they expose, how good that mechanism is for CDC, and how they behave under a long-running analytical query.

The practical takeaway when scoping a new source-system integration: ask early whether logical/binlog-based CDC is even switched on (many production databases run with it disabled by default for performance or licensing reasons), whether you’ll be granted a read replica, and what isolation level the extraction jobs are permitted to run at. Those three answers determine your entire extraction architecture far more than which specific ETL tool you pick.

Best Practices

Always extract against a replica when one exists, and treat “no replica available” as a red flag worth escalating rather than a reason to query the primary directly — even a single daily batch job against a large primary table can be enough to trip an alert on p99 application latency.

Index the watermark column before the first production run, not after you notice the pipeline is slow. CREATE INDEX ... CONCURRENTLY (PostgreSQL) or an online index build (SQL Server, MySQL 8+ with ALGORITHM=INPLACE) lets you add it without locking the table, but it should be part of the source-system onboarding checklist, not an afterthought.

Wrap multi-table extraction in a single transaction at an appropriate isolation level (REPEATABLE READ or the engine’s snapshot-isolation equivalent) whenever downstream logic depends on cross-table consistency — for example, extracting an order and its line items in the same run.

Prefer log-based CDC over polling once a source matures, especially for tables where deletes matter, updates happen many times between your polling interval, or the table is too large to full-scan-check for deletes some other way. Reserve updated_at-watermark polling for smaller tables, sources where you can’t get log access, or a fast first integration.

Paginate large extraction batches and make the job resumable. Never pull “the whole table” in one query when a table has millions of rows; keyset pagination on the indexed watermark (WHERE (updated_at, id) > (:last_ts, :last_id) ORDER BY updated_at, id LIMIT N) avoids the classic OFFSET-based pagination performance cliff and lets a failed job restart from its last committed page instead of from zero.

Set query timeouts and resource limits on extraction connections. A misbehaving extraction query should fail loudly and get killed, not run for six hours quietly starving the primary. Most engines let you cap statement runtime per role/session (statement_timeout in PostgreSQL, MAX_EXECUTION_TIME hint in MySQL, Resource Governor in SQL Server) — apply that limit to the credentials your pipeline uses.

Confirm updated_at (or an equivalent audit column) is actually maintained on every write path, including bulk jobs, admin tools, and migrations, before relying on it as a watermark — a single unmaintained code path silently produces gaps in every future extraction.

Treat hard deletes as a known gap in watermark-based extraction and either move to CDC, add soft-delete flags to the source schema (a conversation with the application team, not something you can do unilaterally), or run periodic full reconciliation scans during low-traffic windows to catch what watermark polling missed.

Use a dedicated, least-privilege database role for extraction jobs, scoped to SELECT on the specific schemas/tables needed, separate from any application service account — this limits blast radius and makes it trivial to audit or throttle data engineering traffic independently of application traffic.

References

• Backend — Relational Databases — SQL fundamentals, ACID, isolation levels, normalization, and indexing in depth
• PostgreSQL Documentation — Write-Ahead Logging
• PostgreSQL Documentation — Logical Replication
• MySQL Reference Manual — The Binary Log
• Microsoft Learn — Change Data Capture (SQL Server)
• Debezium Documentation
• Martin Kleppmann — Designing Data-Intensive Applications, Ch. 5 & 11 — replication and CDC/derived-data fundamentals
• Use The Index, Luke — SQL indexing guide