Data & Databases
Oracle vs PostgreSQL: Architecture, SQL Dialect and Migration
Intermediate9 min readUpdated
Oracle and PostgreSQL solve the same problem with different machinery, and the differences matter long before you hit an edge case. This guide compares their process and storage architecture, their approach to MVCC, their SQL and procedural dialects, partitioning, availability and licensing, then covers what actually breaks during a migration and how to choose honestly between them.
What you will learn
- Describe the instance, database and schema model of each engine and how connections are served
- Explain undo-based read consistency versus heap versioning with vacuum, and the operational work each implies
- Translate common SQL and PL/SQL constructs into their PostgreSQL equivalents
- Compare partitioning, replication and high-availability options including their licensing cost
- Plan a migration and identify the constructs that need rewriting rather than converting
The Oracle-versus-PostgreSQL question is usually asked as if it were about speed. It very rarely is. Both are mature, cost-based, MVCC relational engines that will comfortably run workloads larger than most organisations produce. The differences that matter are architectural and operational: how each one keeps old row versions, how it is administered, what happens when you need high availability, and what the invoice looks like at the end of the year.
This guide compares the two engines as they stand in 2026 — PostgreSQL 17 and 18, and Oracle Database 19c and 23ai — and closes with the migration work that people consistently underestimate.
Instances, databases and schemas
Oracle draws a sharp line between the instance (the memory structures and background processes) and the database (the files on disk). The instance owns the System Global Area, which holds the buffer cache, shared pool and redo log buffer, and a set of named background processes: DBWn writes dirty blocks, LGWR flushes redo, CKPT advances checkpoints, SMON and PMON recover, ARCn archives redo logs. Since 12c the shape is multitenant: a container database (CDB) holds pluggable databases (PDBs), and a PDB can be unplugged from one container and plugged into another — the cleanest upgrade and consolidation story either engine offers. Within a database, a schema is a user: creating a user creates its schema, and objects are addressed as owner.object.
PostgreSQL has a cluster: one postgres supervisor process, a shared buffer pool, and helper processes for the WAL writer, checkpointer, background writer, autovacuum launcher and statistics. Every client connection is served by a separate backend process forked at connect time, which makes connection setup comparatively expensive and is why a pooler such as PgBouncer is standard in front of any application with churn. A cluster holds several databases, each containing schemas, which are namespaces rather than users; a session connects to exactly one database and cannot join across databases without postgres_fdw or dblink. Coming from Oracle, the mental mapping that works is: Oracle PDB ≈ PostgreSQL database, Oracle schema ≈ PostgreSQL schema, and Oracle's user/schema fusion simply does not exist.
Two answers to MVCC
Both engines give readers a consistent snapshot without blocking writers. They get there differently, and the difference drives most of the day-to-day operational contrast.
Oracle updates the row in place and writes the pre-change image into an undo tablespace. A query that needs an older snapshot reconstructs it by walking undo. The upside is that tables do not accumulate dead versions and space is reused automatically. The downsides are that undo is a finite, shared resource, and a long-running report can fail with the classic ORA-01555 snapshot too old when the undo it needs has been overwritten. Undo retention is a tuning parameter with a real cost.
PostgreSQL never overwrites a live row. An UPDATE writes a new tuple version into the heap and marks the old one dead; a DELETE only marks. Readers pick the version visible to their snapshot. Nothing can fail with "snapshot too old" by default, but dead tuples must be reclaimed, and that is vacuum's job. Autovacuum handles it automatically, and a healthy system needs no manual intervention — but three failure modes are worth knowing:
- Table bloat. If autovacuum cannot keep up with an update-heavy table, dead tuples accumulate, the table and its indexes grow, and scans read more pages than they should.
- Long transactions block cleanup. A session idle in transaction pins the oldest snapshot, so nothing newer can be vacuumed anywhere. Keeping transactions short matters more here than on Oracle; see JDBC and transactions.
- Transaction ID wraparound. Row visibility uses a 32-bit counter, and vacuum must periodically freeze old rows. Ignore the warnings long enough and the database refuses new writes to protect itself.
Recent releases have steadily reduced the pain: PostgreSQL 17 rewrote vacuum's dead-tuple storage to use far less memory and to stop needing multiple index passes, and PostgreSQL 18 added an asynchronous I/O subsystem that improves large sequential reads. HOT updates — where an update touching no indexed column reuses the same page — already avoid index churn for a large share of workloads.
SQL dialect
Standard SQL travels well; the details do not. The table below covers what breaks first when a query written for one engine is run on the other.
| Concern | Oracle | PostgreSQL |
|---|---|---|
| Numeric type | NUMBER(p,s), one type for all numbers | numeric, plus native integer, bigint, double precision |
| Strings | VARCHAR2(n); '' is stored as NULL | varchar(n), text; '' is an empty string, distinct from NULL |
| Dates | DATE includes a time; TIMESTAMP WITH TIME ZONE for offsets | date has no time; use timestamptz |
| Row limiting | FETCH FIRST n ROWS ONLY, legacy ROWNUM | LIMIT n OFFSET m, also supports FETCH FIRST |
| Dummy table | SELECT sysdate FROM dual | SELECT now() — no FROM needed |
| Null fallback | NVL, NVL2, DECODE | COALESCE, CASE |
| Identifier folding | Unquoted names fold to UPPER CASE | Unquoted names fold to lower case |
| Hierarchies | CONNECT BY PRIOR | WITH RECURSIVE (also supported by Oracle) |
| Optimizer hints | First-class /*+ ... */ hints | No statement hints by design; planner settings or the pg_hint_plan extension |
The identifier-folding row causes more migration bugs than it deserves to. A tool that emits CREATE TABLE "SESSION" in Oracle style leaves you needing double quotes and upper case forever in PostgreSQL. Convert identifiers to lower case and leave them unquoted.
Procedurally, PL/SQL and PL/pgSQL look alike — DECLARE/BEGIN/EXCEPTION/END, cursors, %ROWTYPE, RAISE — and simple routines convert almost mechanically. The structural gaps are what cost time:
- Packages have no equivalent. A package becomes a schema of functions, and package-level state becomes a table, a temporary table or a custom GUC.
- Autonomous transactions do not exist. Audit-logging patterns that relied on them are rebuilt with a background worker,
dblinkback into the same database, or by writing the log outside the transaction. DBMS_packages map to different things case by case:DBMS_OUTPUTtoRAISE NOTICE,DBMS_SCHEDULERtopg_cronor an external scheduler,UTL_FILEto server-sideCOPYor an extension.- Procedures versus functions. PostgreSQL functions always run inside the caller's transaction; only
CREATE PROCEDURE(11+) mayCOMMIT.
PostgreSQL answers back with something Oracle has no equivalent for: any function can be written in another language — plpython3u, plv8, Rust or C — and installed as an extension.
Partitioning, availability and extensions
Partitioning. PostgreSQL has had declarative partitioning since version 10, with range, list and hash strategies, partition pruning at plan and execution time, partitionwise joins and aggregates, and attach/detach of partitions as a metadata operation. Its one significant limitation is that indexes are per-partition: there is no global index, so a unique constraint must include the partition key. Oracle's partitioning is older, broader (interval, reference, system, composite) and supports global indexes — and on Enterprise Edition it is a separately licensed option, which surprises teams who assumed it was included.
High availability. Oracle's Real Application Clusters run several instances against shared storage for active-active scale-out and instance failover; Data Guard maintains physical or logical standbys with optional zero-data-loss modes. PostgreSQL has no shared-disk clustering. It provides streaming physical replication with synchronous or asynchronous commit and hot-standby read replicas, plus logical replication for selective, cross-version and cross-platform copies. Automatic failover comes from Patroni, repmgr or a managed cloud service rather than the core product. Both models reach the same availability targets; only one of them is a check box you buy.
Extensions are PostgreSQL's real differentiator. pg_stat_statements for query fingerprinting, PostGIS for geospatial work, pgvector for embeddings, TimescaleDB for time series, postgres_fdw for federation, pg_partman for partition maintenance — all installed with one CREATE EXTENSION. Oracle's equivalents are built in (Spatial, Text, In-Memory column store, Advanced Compression), which is convenient, and separately licensed, which is not.
Diagnostics deserve a mention because habits do not transfer. Oracle's AWR and ASH reports and V$ views are unmatched for retrospective analysis — and require the Diagnostics Pack. PostgreSQL exposes pg_stat_activity, pg_stat_user_tables, pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS); retrospection means sampling into a monitoring stack yourself. Reading either engine's plans is the subject of the indexes and execution plans guide.
Licensing and cost
Oracle Database Enterprise Edition is licensed per processor core (with a core factor) or per named user, with a support subscription of roughly 22% of the licence each year, and most interesting features — partitioning, In-Memory, Active Data Guard, the diagnostics and tuning packs, RAC — priced on top. Standard Edition 2 is cheaper but capped in socket and thread count and excludes most options. Express Edition is free with hard limits.
PostgreSQL is released under the PostgreSQL Licence, a permissive BSD-style licence with no per-core cost, no audit and no feature gating. Money still gets spent — on managed services, on commercial support, on the engineering time that Oracle's packaged features would have covered — but it scales with what you use rather than with how many cores you own.
Choosing, and migrating
Choose Oracle when you need RAC-style active-active clustering on shared storage, when regulated workloads depend on Flashback Database or fine-grained auditing, when a vendor application is certified only on Oracle, or when a large PL/SQL estate makes migration cost more than licences. Choose PostgreSQL for new development by default: it is standards-compliant, cheap to run at any scale, extensible, and portable across every cloud. Most new systems in 2026 have no requirement that Oracle uniquely satisfies.
If you migrate, plan for four workstreams and be honest that the last two dominate:
- Schema conversion. Largely automated by
ora2pg. WatchNUMBERwithout precision (do not map everything tonumeric; usebigintorintegerwhere the data allows),DATEtotimestamp, and identifier case. - Data movement. Bulk export and
COPYfor a one-off cutover; logical replication or change-data-capture for a near-zero-downtime one. - Code rewrite. Packages, autonomous transactions, hints,
CONNECT BY,ROWNUMtricks and anything touchingDBMS_. This is genuine redesign, not conversion. - Behavioural testing. Empty string versus NULL is the classic silent data change; implicit conversions that Oracle tolerated become errors; sequence caching and
RETURNINGchange how generated keys come back. Run the same workload against both and compare results before you compare timings.
Schema quality survives the move either way — a well-normalised design, as covered in database design and normalization, and the query habits in the SQL best practices guide apply equally to both engines.
Key takeaways
- Oracle separates instance from database with named background processes and multitenant PDBs; PostgreSQL forks one backend per connection inside a cluster of databases, so a connection pooler is essential.
- Undo-based read consistency gives Oracle automatic space reuse at the risk of
ORA-01555; PostgreSQL's heap versioning needs autovacuum, and long transactions are what break it. - SQL differences that bite are empty string versus NULL,
DATEsemantics, identifier case folding and row limiting; PL/SQL packages and autonomous transactions have no PostgreSQL equivalent. - PostgreSQL's declarative partitioning is capable but has no global indexes; Oracle's is richer and separately licensed.
- Oracle bundles HA and diagnostics as priced options; PostgreSQL relies on streaming replication plus Patroni and on an extension ecosystem that costs nothing to adopt.
- Pick PostgreSQL by default for new systems, Oracle for specific capabilities you can name, and budget a migration around code rewriting and behavioural testing rather than schema conversion.
Sources and further reading
Frequently asked questions
- Is PostgreSQL a realistic replacement for Oracle in an enterprise?
- For most transactional applications, yes, and that migration has become routine. Where it gets hard is not raw capability but the surrounding investment: thousands of lines of packages, jobs written against DBMS_SCHEDULER, tooling built on AWR, or a hard dependency on Real Application Clusters. Assess the estate, not the engine, before deciding — the database itself is rarely the blocker.
- Why does PostgreSQL need VACUUM when Oracle does not?
- PostgreSQL keeps every version of a row in the table itself, so an updated or deleted row leaves a dead version behind that only vacuum can reclaim. Oracle writes the previous image to undo segments and overwrites the row in place, so space reuse is automatic but long queries can fail when the undo they need has been overwritten. Different trade-off, not a missing feature.
- How different are PL/SQL and PL/pgSQL in practice?
- The block structure, cursors, exception handling and variable declarations are close enough that simple procedures convert nearly line for line. The gaps are structural: PostgreSQL has no packages, no autonomous transactions and no built-in DBMS_ library, so those need redesign as schemas, background workers or extension functions rather than mechanical translation.
- Which engine is faster?
- Neither, in any way that generalises. Both use cost-based optimizers, MVCC and B-tree indexes, and both scale to workloads far larger than most applications generate. Performance differences in real systems come from schema design, indexing, query shape and hardware. Choose on operational fit, ecosystem and cost, then measure your own workload with your own data.