PostgreSQL vs Oracle — In-Depth Comparison for DBAs

Overview Comparison Table

Aspect	PostgreSQL	Oracle Database
License	Open Source (PostgreSQL License) — free forever, no Per-Core fee	Commercial — Named User Plus or Processor License (starting in the millions of baht)
Architecture	Process-per-Connection (fork model)	Shared Memory + Thread-based (SGA/PGA)
MVCC	Tuple-level versioning + VACUUM	Undo Segment (Rollback Segment)
SQL Standard	Very high SQL:2023 compliance	SQL:2023 + proprietary extensions
Max DB Size	Unlimited (tested at petabyte scale)	Unlimited
Community	Open-source community + commercial backing (EDB, Crunchy Data, etc.)	Oracle Corporation + Partner ecosystem

Performance & Query Optimizer

Both PostgreSQL and Oracle use a Cost-Based Optimizer (CBO) to choose query execution plans, but there are key differences:

PostgreSQL Query Planner

• Uses Statistics Collector to store column distribution data via ANALYZE
• Supports Parallel Query since version 9.6 — current (18.x) supports Parallel Seq Scan, Index Scan, Hash Join, Merge Join, Aggregation
• JIT Compilation (LLVM) since version 11 — speeds up queries with complex expressions
• Tune work_mem, effective_cache_size, random_page_cost to directly adjust planner behavior
• pg_stat_statements makes it easy to identify slow queries

Oracle Query Optimizer

• Uses Adaptive Query Optimization that can change the plan mid-execution
• SQL Plan Management (SPM) prevents plan regression — locks in good plans
• Real Application Testing (RAT) tests real workloads before upgrading
• AWR (Automatic Workload Repository) automatically stores performance baselines
• More granular Parallel Query — set Degree of Parallelism (DOP) at table, index, or session level

Partitioning

Data partitioning is critical for ERP systems with tens of millions of records.

Feature	PostgreSQL 18	Oracle 23ai
Range Partition	Supported	Supported
List Partition	Supported	Supported
Hash Partition	Supported	Supported
Composite (Sub-partition)	Supported (multi-level)	Supported (Range-Hash, Range-List, etc.)
Automatic Partition	No built-in — requires pg_partman Extension	Interval Partition automatically creates new partitions when data exceeds a range
Partition Pruning	Supported (since PG 11)	Supported
Online Partition Management	ATTACH/DETACH without full table lock (PG 14+)	Online Move/Split/Merge Partition

Replication & High Availability

For ERP systems requiring Disaster Recovery and Zero Downtime, choosing the right replication method is critical.

PostgreSQL

• Streaming Replication — Physical WAL-level replication, built-in since PG 9.0
• Logical Replication — replicate only specific tables or schemas (PG 10+)
• Synchronous Replication — guarantees Zero Data Loss
• Patroni / repmgr — widely used open-source automatic failover solutions
• No built-in Active-Active Multi-Master — requires extensions like BDR (Bi-Directional Replication)

Oracle

• Data Guard — Physical/Logical Standby with automatic Switchover/Failover (requires option purchase)
• Active Data Guard — read from Standby while replicating (requires additional option purchase)
• GoldenGate — Logical Replication across platforms, supports Change Data Capture (CDC)
• RAC (Real Application Clusters) — Active-Active Clustering where all nodes read and write simultaneously (requires option purchase)

JSON & NoSQL Capabilities

Both systems support JSON but take different approaches:

Feature	PostgreSQL	Oracle
Data Type	jsonb — Binary format, indexable, fast query	JSON type + JSON Duality View (23ai)
Indexing	GIN Index on jsonb — full-text search within JSON	Function-based Index, JSON Search Index
SQL/JSON Path	Supports SQL/JSON standard (PG 12+)	Supports SQL/JSON standard
Unique Feature	jsonb operators (@>, ?, #>) — very easy to use	JSON Duality View — view relational tables as JSON documents

PL/pgSQL vs PL/SQL

For DBAs who need to migrate Stored Procedures from Oracle to PostgreSQL, these are the key areas to watch:

Feature	PL/pgSQL	PL/SQL (Oracle)
Syntax	Similar to PL/SQL but not 100% identical	Oracle's native language
Package	No Package — uses Schema + Extension instead	Supports Package (Header + Body)
Autonomous Transaction	No built-in — use dblink or pg_background instead	PRAGMA AUTONOMOUS_TRANSACTION
Exception Handling	EXCEPTION WHEN — similar to PL/SQL	EXCEPTION WHEN
Cursor	Supported — slightly different syntax	Supported — REF CURSOR, SYS_REFCURSOR
Bulk Operations	No FORALL — use INSERT ... SELECT or COPY instead	FORALL, BULK COLLECT

Security

Data security is critical for ERP systems that hold financial data, employee data, and customer data.

Feature	PostgreSQL	Oracle
Row-Level Security (RLS)	Built-in (PG 9.5+) — define policies per table	Virtual Private Database (VPD) — requires separate option purchase
Encryption at Rest (TDE)	No built-in TDE — use pgcrypto or OS-level encryption	Transparent Data Encryption — encrypts at tablespace level (requires Advanced Security Option purchase)
Audit	pgAudit Extension — logs every SQL statement	Unified Auditing — more granular, multi-level policy settings
SSL/TLS	Supported — enforce SSL per user/database	Supported — Oracle Net encryption
Data Masking	Achievable via View + RLS — no built-in masking	Data Redaction — real-time data masking (requires option purchase)

Monitoring & Tooling

DBAs need good tools to monitor systems — this is where Oracle has a clear advantage, but PostgreSQL's ecosystem is growing rapidly.

Aspect	PostgreSQL	Oracle
Performance Stats	pg_stat_statements + pg_stat_activity	AWR + ASH + ADDM (Automatic Database Diagnostic Monitor)
Log Analysis	pgBadger — analyzes logs and generates HTML reports	Oracle Enterprise Manager (OEM)
GUI Management	pgAdmin, DBeaver, DataGrip (free/low cost)	Oracle Enterprise Manager Cloud Control (requires Diagnostic Pack purchase)
Grafana Integration	Very easy — many ready-made dashboards available	Possible but more complex to set up

Total Cost of Ownership (TCO) — The Real Decision Point

For executives making decisions, the 5-year Total Cost of Ownership is the most important figure:

Items	PostgreSQL	Oracle Enterprise
License (5 years)	0 THB	5–50 million THB+ (depends on core count)
Support/Maintenance	0 (Community) or 0.5–2 million/year (Commercial Support)	22% of license fee per year
DBA Salary	50,000–120,000 THB/month	80,000–200,000 THB/month (Oracle DBAs are harder to find)
Options/Add-ons	All features included free — Partitioning, Replication, RLS, JSON all included	RAC, Data Guard, Advanced Security, Partitioning all require separate option purchase
Infrastructure	Works with any Linux distro, containers, and any cloud provider	Oracle Linux/RHEL recommended — cloud licensing may increase costs

When to Choose PostgreSQL / When You Still Need Oracle

Choose PostgreSQL When:

• You need to significantly reduce licensing costs
• You want all features at one price — Partitioning, Replication, RLS, JSON all free
• You need flexibility — works with any cloud provider, containers, Kubernetes
• Your workload is mid-to-large OLTP that does not require RAC
• Your DBA team has Linux/Open Source skills and is willing to learn
• You want to avoid vendor lock-in

Still Need Oracle When:

• You require RAC (Active-Active Clustering) — PostgreSQL has no built-in equivalent
• You have large volumes of PL/SQL legacy code that is too costly or difficult to migrate
• You need Adaptive Query Optimization + SQL Plan Management for highly complex workloads
• Your organization already has long-term Oracle support contracts
• You require TDE (Transparent Data Encryption) at the tablespace level

Global Organizations Using PostgreSQL

PostgreSQL won DBMS of the Year 2023 from DB-Engines (score up 22.5%) and continues to grow — currently ranked 4th worldwide behind Oracle, MySQL, and MS SQL Server, with a 16.85% market share. More than 48,000 organizations worldwide run PostgreSQL in production:

Organization	Use Case
Apple	Switched from MySQL in 2010 — default database on macOS Server
Instagram (Meta)	One of the largest PostgreSQL deployments in the world — serves hundreds of millions of users daily
Spotify	Uses PostgreSQL + Cassandra as core infrastructure for its music platform
Netflix	Uses PostgreSQL in backend systems supporting global streaming
Uber	Large-scale data management system for ride-sharing
Reddit	Primary database for a top-ranked global community platform
Discord	Chat system supporting millions of concurrent users
NASA	Scientific data storage and analysis system

Case Study — TGO Uses PostgreSQL with Saeree ERP for Over 14 Years

Thailand Greenhouse Gas Management Organization (TGO) is one of the government organizations that has used PostgreSQL as the primary database for Saeree ERP since 2011 — over 14 years of PostgreSQL proven in real production:

• Stability — zero downtime from database issues
• Performance — handles all organization users without issues
• Continuous upgrades — upgraded from PostgreSQL 9.x → 12 → 14 → 18 without issues
• License cost = 0 THB throughout 14 years

PostgreSQL is not "free but inferior" — it is an enterprise-grade database with no license fee. What differs from Oracle is the cost, not the quality.

— Saeree ERP Team

Summary

1. Oracle remains the "gold standard" — for organizations that need RAC, TDE, Adaptive Optimizer and are willing to pay for licensing
2. PostgreSQL has reached enterprise level — with comprehensive features and comparable performance
3. TCO is the deciding factor — 5-year Oracle licensing costs can fund a full DBA team + new hardware for PostgreSQL
4. Migration is feasible — tools like Ora2Pg significantly reduce migration time
5. Stay updated — follow the latest patches to maintain system stability

If your organization is considering migrating from Oracle to PostgreSQL, or needs consulting on database options for an ERP system, feel free to contact our consulting team — free of charge.

References

• PostgreSQL Official Documentation
• Oracle Database Documentation
• Ora2Pg — Oracle to PostgreSQL Migration Tool
• PostgreSQL Feature Matrix