What is the difference between a logical database backup (the kind produced by tools such as pg_dump or mysqldump) and a physical backup (the kind produced by pg_basebackup or Percona XtraBackup), and when would you choose each?

middlemust knowfreq 64%

answer

Logical = SQL/rows, replayed; physical = files, byte-for-byte
Logical restore rebuilds every index — the slow part
Physical needs the log captured during the copy
Portability vs speed
Table-level restore only from logical

basics

~20 s

A logical backup exports data as SQL statements or rows that must be re-executed to restore. A physical backup copies the database's data files byte for byte. Logical is portable and selective but slow to restore; physical is fast but version- and platform-bound.

solid answer

~60 s

A logical backup asks the engine to describe the database: DDL plus row data. Restoring means running that output — parsing, inserting, and rebuilding every index and constraint from scratch. That makes it portable across major versions, operating systems and CPU architectures, lets you restore a single table or schema, and produces human-readable, diffable output. The cost is speed: on a large database the restore is dominated by index builds and can take many times longer than the dump.

A physical backup copies the actual data files plus the transaction log written while the copy runs, so restore is essentially "put the files back and let crash recovery finish them". Indexes come back already built, it scales to terabytes, and it is what block-level incremental backups and replica cloning are built on. The cost: it is tied to the same major version and usually the same platform and page layout, and it is whole-instance rather than per-table.

Default to physical for routine backups of anything large; use logical for migrations, major-version upgrades, and selective restores.

A **logical backup** asks the engine to describe the database: DDL plus row data. Restoring means running that output — parsing, inserting, and rebuilding every index and constraint from scratch. That makes it portable across major versions, operating systems and CPU architectures, lets you restore a single table or schema, and produces human-readable, diffable output. The cost is speed: on a large database the restore is dominated by index builds and can take many times longer than the dump. A **physical backup** copies the actual data files plus the transaction log written while the copy runs, so restore is essentially "put the files back and let crash recovery finish them". Indexes come back already built, it scales to terabytes, and it is what block-level incremental backups and replica cloning are built on. The cost: it is tied to the same major version and usually the same platform and page layout, and it is whole-instance rather than per-table. Default to physical for routine backups of anything large; use logical for migrations, major-version upgrades, and selective restores.

code

text · 11 lines

text

# logical: emits DDL + rows, restore re-executes them
pg_dump -Fc -d appdb -f appdb.dump
pg_restore -d appdb_new -j 8 appdb.dump

mysqldump --single-transaction appdb > appdb.sql
mysql appdb_new < appdb.sql

# physical: copies data files + WAL/redo written during the copy
pg_basebackup -D /backup/base -X stream -c fast
xtrabackup --backup --target-dir=/backup/base
xtrabackup --prepare --target-dir=/backup/base   # applies captured redo

go deeper

for a junior

Know the definitions and one tool on each side, plus the headline tradeoff: logical is portable and slow, physical is fast and version-locked.

for a middle

Explain why logical restore is slow (index rebuilds), why physical needs the transaction log captured during the copy, and give a concrete case for each.

for a senior

Discuss impact on the live system, incremental support, replica seeding, and the hybrid strategy of physical primary path plus periodic logical dumps for portability and table-level recovery.

for a principal

Frame it as which recovery scenarios the organisation must serve — whole-instance disaster, single-object mistake, version migration — and let that set the artifact mix, retention, and where the restore path is exercised.

Two ways to describe the same data

A relational database exists as logical content (tables, rows, constraints) and as physical structure (fixed-size pages inside files, B-tree index pages, free-space maps, a transaction log). A backup can capture either layer.

Logical backups

The tool connects as an ordinary client, reads the catalog, and emits a reconstruction script: CREATE TABLE, row data (as INSERT statements or a bulk-copy stream), then indexes, constraints, sequences, and grants. The output is text or an archive of such text.

Restore replays that script. That means the engine re-parses every statement, re-validates constraints, and rebuilds every index from scratch — usually the dominant cost. A dump that takes an hour can take four to restore.

What you get in exchange:

Portability. The output is (mostly) just SQL. It survives a major-version upgrade, a move from x86 to ARM, a different OS, sometimes a different vendor.
Granularity. Restore one table, one schema, or just the DDL. This is the answer to "someone dropped a table an hour ago and the rest of the database must stay online".
Compaction. The dump contains rows, not pages, so it excludes index data, free space, and dead row versions. A bloated 500 GB instance may dump to 40 GB.
Inspectability. You can grep it, diff schemas, and review it.

Weaknesses: slow both directions, expensive in CPU and buffer cache on the source, and it captures a point in time with no simple way to roll forward past the dump.

Physical backups

The tool copies the data files at the byte level while the database keeps running, and simultaneously captures the transaction log (WAL/redo) generated during the copy. Because files are read while pages are being modified, the copy on its own is inconsistent — a smear across time. Replaying the captured log over it brings it to a consistent state, exactly as crash recovery would.

What you get:

Speed. It is bounded by sequential IO and network throughput, not by SQL execution. Restore is a file copy plus a short recovery, with indexes already built.
Scale. This is the only realistic option once a database is hundreds of gigabytes or larger.
Incrementals. Block-level change tracking makes cheap incremental backups possible; logical backups have no good equivalent.
It is also how replicas are seeded — the same artifact bootstraps a standby.

Weaknesses:

Not portable. Same major version, same page size, usually same architecture and often the same OS. You cannot use it to upgrade across major versions.
All or nothing. You restore the whole instance or cluster, not a single table (some enterprise tools do partial restores, but it is not the norm).
Opaque. You cannot read it, and it carries bloat, dead rows and index pages along with real data.
Version coupling in tooling. The backup tool must understand the storage engine's on-disk format, so it lags new releases.

Load on the running system

A logical dump runs as a client query workload: it holds a long-lived read snapshot, competes for CPU, and pulls the whole dataset through the buffer cache. A physical backup mostly reads files outside the buffer pool and is friendlier to the cache, though it saturates disk and network bandwidth and increases the volume of transaction log that must be retained while it runs.

Choosing

Routine nightly backup of a large OLTP database → physical, with incrementals.
Major-version upgrade, engine migration, or moving to different hardware → logical.
Recovering one accidentally dropped table → logical (or a restore of a physical backup into a scratch instance, then a logical extract from there).
Small database (a few GB), or one where you want reviewable, greppable artifacts → logical is perfectly adequate.

Mature setups run both: physical as the primary recovery path, plus a periodic logical dump for portability and table-level extraction.

## Two ways to describe the same data A relational database exists as *logical content* (tables, rows, constraints) and as *physical structure* (fixed-size pages inside files, B-tree index pages, free-space maps, a transaction log). A backup can capture either layer. ## Logical backups The tool connects as an ordinary client, reads the catalog, and emits a reconstruction script: `CREATE TABLE`, row data (as `INSERT` statements or a bulk-copy stream), then indexes, constraints, sequences, and grants. The output is text or an archive of such text. Restore replays that script. That means the engine re-parses every statement, re-validates constraints, and **rebuilds every index from scratch** — usually the dominant cost. A dump that takes an hour can take four to restore. What you get in exchange: - **Portability.** The output is (mostly) just SQL. It survives a major-version upgrade, a move from x86 to ARM, a different OS, sometimes a different vendor. - **Granularity.** Restore one table, one schema, or just the DDL. This is the answer to "someone dropped a table an hour ago and the rest of the database must stay online". - **Compaction.** The dump contains rows, not pages, so it excludes index data, free space, and dead row versions. A bloated 500 GB instance may dump to 40 GB. - **Inspectability.** You can grep it, diff schemas, and review it. Weaknesses: slow both directions, expensive in CPU and buffer cache on the source, and it captures a point in time with no simple way to roll forward past the dump. ## Physical backups The tool copies the data files at the byte level while the database keeps running, and simultaneously captures the transaction log (WAL/redo) generated during the copy. Because files are read while pages are being modified, the copy on its own is inconsistent — a smear across time. Replaying the captured log over it brings it to a consistent state, exactly as crash recovery would. What you get: - **Speed.** It is bounded by sequential IO and network throughput, not by SQL execution. Restore is a file copy plus a short recovery, with indexes already built. - **Scale.** This is the only realistic option once a database is hundreds of gigabytes or larger. - **Incrementals.** Block-level change tracking makes cheap incremental backups possible; logical backups have no good equivalent. - **It is also how replicas are seeded** — the same artifact bootstraps a standby. Weaknesses: - **Not portable.** Same major version, same page size, usually same architecture and often the same OS. You cannot use it to upgrade across major versions. - **All or nothing.** You restore the whole instance or cluster, not a single table (some enterprise tools do partial restores, but it is not the norm). - **Opaque.** You cannot read it, and it carries bloat, dead rows and index pages along with real data. - **Version coupling in tooling.** The backup tool must understand the storage engine's on-disk format, so it lags new releases. ## Load on the running system A logical dump runs as a client query workload: it holds a long-lived read snapshot, competes for CPU, and pulls the whole dataset through the buffer cache. A physical backup mostly reads files outside the buffer pool and is friendlier to the cache, though it saturates disk and network bandwidth and increases the volume of transaction log that must be retained while it runs. ## Choosing - Routine nightly backup of a large OLTP database → **physical**, with incrementals. - Major-version upgrade, engine migration, or moving to different hardware → **logical**. - Recovering one accidentally dropped table → **logical** (or a restore of a physical backup into a scratch instance, then a logical extract from there). - Small database (a few GB), or one where you want reviewable, greppable artifacts → **logical** is perfectly adequate. Mature setups run both: physical as the primary recovery path, plus a periodic logical dump for portability and table-level extraction.

Why is restoring a logical dump of a large database often several times slower than taking it?
The dump is essentially a sequential read, but the restore re-executes SQL: parsing, inserting rows through the normal write path, writing transaction log for every insert, then building each index and validating each foreign key. Index construction usually dominates, since it means sorting the full column set per index. Parallel restore and deferring constraints until after the load help, but the asymmetry is inherent.
You need to move a 2 TB database from PostgreSQL 14 to PostgreSQL 17. Can you use a physical backup?
No. A physical backup is tied to the on-disk format of a major version, so its files are not readable by a different major version. The upgrade paths are a logical dump and reload, an in-place upgrade utility that rewrites catalog metadata, or logical replication between the two versions to keep downtime short. Physical backups remain the right tool within a version, including for seeding replicas of the same release.

saying these in an interview costs you the question

Claiming a physical backup can restore a single table like a dump can
Assuming a physical backup works across major versions or CPU architectures
Thinking a file copy alone is a valid physical backup without the transaction log captured during it
Saying logical dumps are always smaller — true for bloated instances, not a rule
Recommending nightly logical dumps for a multi-terabyte OLTP database

What is the difference between a logical database backup (the kind produced by tools such as pg_dump or mysqldump) and a physical backup (the kind produced by pg_basebackup or Percona XtraBackup), and when would you choose each?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Two ways to describe the same data

Logical backups

Physical backups

Load on the running system

Choosing

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions9 questions

▾related topics3 topics

Two ways to describe the same data

Logical backups

Physical backups

Load on the running system

Choosing

answer

go deeper

context