RAJASEKHAR AMUDALA · Database Terminology
PostgreSQL
↔ Oracle
Terminology
A complete cross-reference of every major concept — from VACUUM to ANALYZE, tablespaces to segments, WAL to redo logs — mapped side-by-side with context on key differences.
90+
Terms mapped
12
Categories
PG 17
Latest version
Category
PostgreSQL
Oracle
Key Difference / Notes
Storage Architecture
tablespace
TABLESPACE
Symlink in pg_tblspc/ pointing to an OS directory. One directory per tablespace.
TABLESPACE
Logical volume containing one or more physical datafiles (.dbf). Objects stored as extents inside datafiles.
PG: directory symlink — no datafile layer.
Oracle: manages its own block allocation inside .dbf files.
Oracle: manages its own block allocation inside .dbf files.
data file
Relation filedifferent
Each table/index = one OS file named by relfilenode OID. Splits into 1 GB segments.
Datafile (.dbf)
One or more large files per tablespace. Many objects share a single datafile. Managed by Oracle's space layer.
PG: 1 table = 1 file (transparent). Oracle: many tables share datafiles (opaque). PG files inspectable with ls/hexdump.
storage unit
Page / Block
Fixed 8 KB (compile-time). Uniform cluster-wide. Contains: PageHeader, ItemId array, free space, tuples.
Oracle Block
2–32 KB, configurable per tablespace. Contains: cache layer header, table directory, row directory, free space, row data.
PG block size is fixed for the whole cluster. Oracle allows different block sizes per tablespace.
extent
Segment file / Forkdifferent
No extent concept. File grows continuously. Segment files split at 1 GB (relfilenode, relfilenode.1…).
Extent
Contiguous set of Oracle blocks allocated as a unit. A segment grows by adding extents. LMT uses uniform or autoallocate.
PG has no extent layer — OS allocates blocks freely. Oracle's LMT (Locally Managed Tablespace) tracks extents in tablespace header bitmaps.
segment
Relation
In PG, a "relation" (table, index, sequence, view) is the equivalent unit. Its physical backing = one relfilenode file.
Segment
A named storage structure for one database object (table segment, index segment, undo segment, temp segment).
Conceptually equivalent — one object, one storage unit. But PG maps directly to a file; Oracle maps to extents inside datafiles.
fork files
Fork filesPG only
Each relation has up to 4 files:
main (heap), _fsm (free space map), _vm (visibility map), _init (unlogged tables only).
main (heap), _fsm (free space map), _vm (visibility map), _init (unlogged tables only).
Segment header block
Space management metadata embedded within the segment's first few blocks. No separate companion files.
PG externalises space/visibility metadata into separate fork files. Oracle embeds it in the segment header blocks.
free space map
FSM (_fsm fork)PG only
3-level page tree recording available bytes per heap page. Created on first VACUUM. Used by INSERT to pick target page.
FREELIST / ASSM
Freelist groups (old) or Automatic Segment Space Management bitmaps in segment header blocks track free space.
PG FSM is a dedicated companion file. Oracle ASSM uses L1/L2 bitmap blocks within the segment itself.
temp storage
temp_tablespaces
GUC parameter pointing to tablespace(s) for sort/hash spill temp files. Per-session or cluster-wide.
TEMP Tablespace
Dedicated tablespace (usually TEMP) for sort segments and temporary LOBs. Assigned per user or default.
Both serve as spill space for large sorts/hashes. PG temp files are plain OS files, not persisted between sessions.
MVCC, Cleanup & Maintenance
MVCC mechanism
Heap-based MVCC
Old row versions (dead tuples) stored in-place in the heap file alongside live rows. xmin/xmax transaction IDs on each tuple.
Undo-based MVCC
Current version in the table block. Old versions reconstructed by applying undo records from the UNDO tablespace.
PG dead tuples bloat the table file; VACUUM reclaims them. Oracle reconstructs old versions on-the-fly from undo — table file stays clean but undo retention must be tuned.
cleanup
VACUUMPG only
Reclaims dead tuples, updates FSM/VM, advances oldest XID to prevent wraparound. Does NOT compact to OS unless VACUUM FULL.
Automatic Undo Mgmt
No equivalent needed — undo segments automatically overwritten once no active query needs them. Table blocks stay compact.
VACUUM is unique to PG's heap-MVCC model. Oracle users often struggle with this concept — it has no direct Oracle equivalent.
aggressive cleanup
VACUUM FULL
Rewrites the entire table to a new file, compacting it. Requires ACCESS EXCLUSIVE lock. Slow on large tables. Like SHRINK in Oracle.
ALTER TABLE … SHRINK SPACE
Compacts a segment in place, releases space to tablespace. Row movement must be enabled. Online operation possible.
PG VACUUM FULL = full table rewrite + lock. Oracle SHRINK is more granular and can be done online in phases.
auto maintenance
autovacuumPG only
Background daemon that triggers VACUUM and ANALYZE automatically based on dead-tuple thresholds. Tunable per table.
Automatic Undo Retention / SMON
SMON coalesces free extents and cleans temp segments. UNDO tablespace managed automatically. No autovacuum equivalent.
autovacuum is critical in PG — disabling it leads to table bloat and XID wraparound risk. Oracle has no equivalent requirement.
stats collection
ANALYZE
Collects column statistics (histogram, MCV, correlation) into pg_statistic. Used by the query planner to choose optimal plans.
DBMS_STATS.GATHER_TABLE_STATS
PL/SQL package call that gathers optimizer statistics. Also done automatically by auto-stats job (11g+).
ANALYZE in PG is often run automatically by autovacuum. Both serve the same purpose: giving the planner accurate row count / distribution data.
freeze / XID
FREEZE / XID wraparoundPG only
Transaction IDs are 32-bit. After 2 billion XIDs, PG must freeze old tuples (mark them as always-visible) to prevent wraparound catastrophe.
SCN (System Change Number)
48-bit monotonically increasing counter. No wraparound concern in practice. SCN used for read consistency and flashback.
XID wraparound is PG-specific. Monitor with age(datfrozenxid). VACUUM FREEZE advances the frozen XID horizon. Oracle SCN has no wraparound risk.
visibility map
Visibility Map (_vm fork)PG only
2 bits per page: all-visible (VACUUM can skip dead-tuple scan) and all-frozen (freeze VACUUM can skip). Speeds up Index-Only Scans.
ITL (Interested Transaction List)
Per-block list of in-progress transactions. Used to check row-level visibility without consulting UNDO for committed rows.
PG VM is a separate file; Oracle ITL is embedded in each block header. Both serve row visibility tracking but at different granularities.
bloat
Table / Index bloatcommon issue
Dead tuples accumulate in heap pages if VACUUM is delayed. Index entries pointing to dead tuples also accumulate (index bloat).
Row chaining / migration
Row migration occurs when an UPDATE cannot fit the new row in the same block and moves it (leaving a forwarding address). Different from PG bloat.
PG bloat is caused by MVCC dead tuples — VACUUM fixes it. Oracle migration is caused by pctfree misconfiguration. Fundamentally different root causes.
undo
Dead tuples in heap
Old row versions stay in the heap page alongside new versions. No separate undo storage. ROLLBACK uses WAL + in-memory state.
UNDO tablespace / Rollback segments
Before-images written to UNDO tablespace on every DML. Used for ROLLBACK, read consistency, and Flashback features.
PG has no UNDO tablespace. Old versions live in the table file until VACUUM. Oracle must size UNDO retention to support long-running queries.
Transactions & Recovery
write-ahead log
WAL (pg_wal/)
Write-Ahead Log. Sequential log of all changes. Used for crash recovery, streaming replication, and logical decoding.
Redo Log / Online Redo Log
Circular set of redo log files. Written by LGWR. Archived to archive log destination for media recovery.
Same concept: changes written to log before data files. PG WAL = Oracle redo log. PG archive_mode = Oracle archive log mode.
WAL archiving
archive_mode / archive_command
When enabled, completed WAL segments are copied to an archive location via a shell command or archive library.
ARCHIVELOG mode / Archive logs
Archived redo log files written to one or more archive destinations. Required for media recovery and Data Guard.
Conceptually identical. Both required for point-in-time recovery. PG archive is more flexible (any command/script).
checkpoint
CHECKPOINT
Flushes all dirty shared_buffers pages to disk and writes a checkpoint record to WAL. Limits recovery time after crash.
Checkpoint (CKPT process)
CKPT process periodically flushes dirty blocks from buffer cache to datafiles and updates datafile headers with checkpoint SCN.
Same concept. PG checkpoint_completion_target spreads I/O. Oracle incremental checkpointing writes dirty blocks continuously.
PITR / backup
pg_basebackup + WAL
Physical base backup + WAL streaming for PITR. pg_dump/pg_restore for logical. No proprietary backup format.
RMAN (Recovery Manager)
Oracle's proprietary backup/recovery tool. Manages backup sets, image copies, incremental backups. Required for most recovery operations.
PG uses standard filesystem + WAL replay. Oracle requires RMAN for most recovery. PG backup is more script-friendly.
isolation level
READ COMMITTED / REPEATABLE READ / SERIALIZABLE
True Serializable Snapshot Isolation (SSI) in PG 9.1+. REPEATABLE READ is snapshot isolation.
READ COMMITTED / SERIALIZABLE
Default is READ COMMITTED. Oracle's SERIALIZABLE is snapshot isolation (not strict serializable). No REPEATABLE READ level.
PG has 4 levels; Oracle has 2. PG SERIALIZABLE uses SSI (provably correct). Oracle SERIALIZABLE = snapshot isolation (allows some anomalies).
savepoint
SAVEPOINT / ROLLBACK TO SAVEPOINT
Standard SQL savepoints within a transaction. Sub-transactions tracked in memory.
SAVEPOINT / ROLLBACK TO SAVEPOINT
Same SQL syntax. Oracle savepoints recorded in UNDO.
Functionally identical syntax. Implementation differs: PG uses subtransaction records in WAL; Oracle uses UNDO.
autonomous txn
No native equivalentgap
No autonomous transaction support in PL/pgSQL. Workarounds: dblink to self, or use separate connection.
PRAGMA AUTONOMOUS_TRANSACTION
Declares a sub-program that runs in its own independent transaction. Commits/rolls back independently of the caller.
Notable gap. PG lacks autonomous transactions natively — a common migration pain point for Oracle PL/SQL code.
Locking & Concurrency
row locking
Tuple-level lock (xmax)
Lock state stored directly in tuple header (xmax + infomask bits). No separate lock table for row locks in most cases.
TX lock / Row lock byte (ITL)
Lock byte in each row's header. ITL (Interested Transaction List) in each block header tracks active transactions per block.
Both are embedded in row headers. PG uses xmax; Oracle uses a lock byte + ITL. Neither requires a central lock manager for row-level locks.
table lock
LOCK TABLE / lock modes
8 lock modes: ACCESS SHARE → ACCESS EXCLUSIVE. DDL takes ACCESS EXCLUSIVE. Stored in pg_locks.
DML lock (TM) / DDL lock
TM lock for DML operations. DDL locks prevent structural changes during DML. Row-exclusive is default DML mode.
PG has more explicit lock mode names. Both use a lock compatibility matrix. DDL in PG is transactional (can be rolled back); Oracle DDL auto-commits.
advisory lock
Advisory locksPG only
pg_advisory_lock(key) — application-level cooperative locks on arbitrary integer keys. Session or transaction scoped.
DBMS_LOCK
PL/SQL package to request, convert, and release named locks. More complex API but same concept.
PG advisory locks are simpler to use. Oracle DBMS_LOCK has more modes (share, exclusive, sub-share, etc.).
deadlock
Deadlock detection
Background deadlock checker runs after deadlock_timeout (default 1s). Aborts one transaction in the cycle.
Deadlock detection
Oracle detects deadlocks almost instantly. Raises ORA-00060 and rolls back the statement (not the transaction).
Oracle rolls back only the deadlocked statement; PG rolls back the entire transaction. Important difference for retry logic.
SKIP LOCKED
SELECT … FOR UPDATE SKIP LOCKED
Skips rows that are locked by another transaction. Available since PG 9.5. Useful for queue-style workloads.
SELECT … FOR UPDATE SKIP LOCKED
Same syntax. Available since Oracle 10g. Commonly used in job queue patterns.
Identical syntax and semantics. Both useful for implementing lock-free job queues.
Database Objects
schema
SCHEMA (namespace)
Purely logical namespace inside a database. Multiple schemas in one database. No storage meaning. search_path controls resolution.
Schema = User
Schema and user are the same object in Oracle. Creating a user creates a schema. Closely tied to storage quotas on tablespaces.
Critical conceptual difference. PG schemas are decoupled from users and from storage. Oracle schema = user = storage owner.
database
DATABASE
Fully isolated storage container. Each database has its own pg_class, pg_namespace, etc. Cross-database queries require dblink/fdw.
DATABASE / PDB
In CDB/PDB architecture: one container DB (CDB) with pluggable databases (PDBs). Legacy: one database per instance.
PG database ≈ Oracle PDB. PG cluster ≈ Oracle CDB. Cross-database access in PG requires extensions; Oracle PDBs can share via dblinks or CDB views.
sequence
SEQUENCE / SERIAL / BIGSERIAL / IDENTITY
Standalone sequence objects. SERIAL/BIGSERIAL are legacy shorthand. GENERATED AS IDENTITY is SQL-standard (PG 10+).
SEQUENCE / IDENTITY column
Standalone sequences since Oracle 2. GENERATED AS IDENTITY added in Oracle 12c. Sequences can be cached at session level.
Modern syntax is nearly identical. PG SERIAL is a macro that creates a sequence + default — Oracle 12c+ uses IDENTITY columns similarly.
index types
B-tree, Hash, GiST, SP-GiST, GIN, BRIN
Rich set of index access methods. GIN for full-text/arrays. BRIN for time-series. Partial and expression indexes supported.
B-tree, Bitmap, Function-based, Domain, Cluster
Bitmap indexes for low-cardinality columns (DW). Domain indexes for extensible types. IOT (Index-Organised Table).
PG lacks bitmap indexes (but has BRIN for ranges). Oracle lacks GIN/GiST. PG partial indexes = Oracle function-based partial equivalent.
partitioning
Declarative partitioning (PG 10+)
RANGE, LIST, HASH. Partition pruning, partition-wise joins. Each partition is an independent table with its own relfilenode.
Table partitioning
RANGE, LIST, HASH, INTERVAL, COMPOSITE. Very mature. Partition Exchange, Online operations, Global/Local indexes.
Oracle partitioning is more mature (INTERVAL, composite, exchange). PG partitioning is simpler to manage but lacks some advanced Oracle features.
materialised view
MATERIALIZED VIEW
Manual or scheduled refresh (REFRESH MATERIALIZED VIEW). CONCURRENTLY option avoids lock but requires unique index.
MATERIALIZED VIEW / MView
Fast refresh (incremental via MView logs), complete refresh, on-commit refresh. MView rewrite for query rewriting.
Oracle MVs have fast (incremental) refresh and automatic query rewrite. PG MViews always do full refresh; no automatic query rewrite.
trigger
TRIGGER (PL/pgSQL / any language)
ROW or STATEMENT level. BEFORE, AFTER, INSTEAD OF. Can be written in PL/pgSQL, PL/Python, PL/Perl, C, etc.
TRIGGER (PL/SQL)
ROW or STATEMENT level. BEFORE, AFTER, INSTEAD OF. Compound triggers (12c) group all timing points. Only PL/SQL.
PG triggers support multiple languages; Oracle is PL/SQL only. Both support transition tables (REFERENCING). PG lacks compound triggers.
stored proc
FUNCTION / PROCEDURE
Functions return values. Procedures (PG 11+) can call COMMIT/ROLLBACK. Written in PL/pgSQL, SQL, Python, Perl, C, etc.
FUNCTION / PROCEDURE / PACKAGE
PL/SQL only. PACKAGE groups related subprograms + private state. Packages have a header (spec) + body.
Oracle PACKAGE has no direct PG equivalent — PG uses schemas for grouping. PL/pgSQL is inspired by PL/SQL but has syntax differences.
FDW / dblink
Foreign Data Wrapper (FDW)
SQL/MED standard. postgres_fdw for remote PG. oracle_fdw, file_fdw, etc. Full SQL pushdown support.
Database Link (DBLINK)
Connect to another Oracle DB or heterogeneous sources via Oracle Transparent Gateway / HS (Heterogeneous Services).
PG FDWs are more extensible (any source). Oracle DBLINKs are simpler for Oracle-to-Oracle. Both allow cross-database queries.
Performance & Query Execution
query plan
EXPLAIN / EXPLAIN ANALYZE
Shows estimated and actual row counts, costs, timing, buffers. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) for full detail.
EXPLAIN PLAN / DBMS_XPLAN
EXPLAIN PLAN stores plan in plan_table. DBMS_XPLAN.DISPLAY formats it. SQL Monitor for real-time execution.
PG EXPLAIN ANALYZE shows actual runtime metrics inline. Oracle needs DBMS_XPLAN + SQL Monitor for equivalent detail.
planner stats
pg_statistic / pg_stats
Per-column histograms, MCVs, correlation. ANALYZE updates. default_statistics_target (default 100) controls detail level.
DBA_TAB_STATISTICS / DBA_COL_STATISTICS
DBMS_STATS gathers histograms (frequency, height-balanced, hybrid, top-frequency). Auto stats job runs nightly.
Both use histograms and MCVs. PG statistics_target analogous to Oracle's method_opt degree. Both support manual stats locking.
shared memory
shared_buffers
Shared memory pool for caching data pages. Typically 25% of RAM. Uses clock-sweep replacement algorithm.
Buffer Cache (DB_CACHE_SIZE)
Part of SGA. LRU-based. Separate buffer pools (KEEP, RECYCLE, DEFAULT) for different workloads.
PG also relies heavily on the OS page cache (double buffering). Oracle manages its own buffer pools more explicitly.
work memory
work_mem
Per-sort/hash operation memory limit. One query can use multiple work_mem chunks. Spills to temp files if exceeded.
PGA_AGGREGATE_TARGET / sort_area_size
PGA (Program Global Area) per session. Auto PGA management (AMM) since Oracle 10g dynamically adjusts per-op memory.
PG work_mem is per-operation, not per-session — a complex query can use many multiples. Oracle AMM is more dynamic.
parallel query
Parallel query (PG 9.6+)
max_parallel_workers_per_gather controls parallelism. Parallel seq scan, index scan, hash join, aggregation.
Parallel Query / PX
Very mature. PARALLEL hint or table degree attribute. Coordinator + slave processes. Parallel DML, DDL, recovery.
Oracle parallel query is more mature and featureful. PG parallel query is improving rapidly but lacks parallel DML and some scan types.
hint
No native hintsgap
PG relies on GUC parameters (enable_seqscan, enable_hashjoin) or pg_hint_plan extension (third-party).
SQL Hints (/*+ HINT */)
Rich hint system: INDEX, FULL, USE_NL, LEADING, PARALLEL, PUSH_PRED, etc. Embedded in SQL comments.
Notable gap. PG has no native hint system. Oracle DBAs frequently rely on hints — a common migration challenge.
SQL plan mgmt
pg_hint_plan (extension)
Third-party extension. Attach hints to queries via a hint table. Not built-in. plan_baseline not available.
SQL Plan Management (SPM)
SQL Plan Baselines capture accepted plans. Evolves plans only when new plan is verified better. Stored in SYSAUX.
Oracle SPM is a significant enterprise feature with no PG equivalent. PG relies on planner improvements across versions.
wait events
pg_stat_activity.wait_event
wait_event_type + wait_event columns. Categories: Lock, LWLock, IO, IPC, BufferPin, etc.
V$SESSION / V$SESSION_WAIT
Very rich wait interface. Hundreds of wait events categorised by class. AWR/ASH record historical waits.
Oracle's wait interface is far more granular. PG wait events are useful but less detailed. PG extensions like pg_wait_sampling add sampling.
Administration
config file
postgresql.conf / pg_hba.conf
postgresql.conf: all GUC parameters. pg_hba.conf: client authentication rules (host-based). pg_ident.conf: OS user mapping.
SPFILE / PFILE / sqlnet.ora
SPFILE (binary, server-side), PFILE (text init.ora). sqlnet.ora + listener.ora for network. ALTER SYSTEM modifies SPFILE.
PG config is all text files — easy to version control. Oracle SPFILE is binary (modified only via ALTER SYSTEM).
config reload
pg_reload_conf() / SIGHUP
Most GUC changes take effect on reload without restart. Some require restart (shared_buffers, max_connections).
ALTER SYSTEM … SCOPE=BOTH/MEMORY/SPFILE
Dynamic parameters change in memory immediately (SCOPE=MEMORY). Static parameters require instance restart.
PG's SIGHUP reload is simpler but covers fewer parameters dynamically. Oracle's dynamic parameter system is more granular.
process model
Process-per-connection (postmaster)
postmaster forks a new OS process per client connection. Background processes: bgwriter, autovacuum, WAL sender, etc.
Dedicated / Shared Server + background processes
Dedicated: one server process per session. Shared Server: pooled dispatchers. Background: DBWn, LGWR, CKPT, SMON, PMON, etc.
PG forks on each connection (expensive at scale — use pgBouncer). Oracle dedicated server is similar. Oracle shared server is a built-in connection pool.
connection pool
pgBouncer / pgpool-II
External tools (not built-in). pgBouncer is lightweight; pgpool-II adds load balancing + failover. PG 14+ has connection slot improvements.
Shared Server / DRCP / JDBC UCP
Built-in Shared Server or Database Resident Connection Pooling (DRCP). Client-side: UCP (Universal Connection Pool).
Oracle has built-in pooling options. PG relies on external tools — pgBouncer is the standard choice for production deployments.
monitoring views
pg_stat_* views
pg_stat_activity, pg_stat_user_tables, pg_stat_bgwriter, pg_stat_replication, pg_locks, pg_stat_statements (extension).
V$ / GV$ / DBA_* / AWR / ASH
V$SESSION, V$SQL, V$SYSSTAT, DBA_SEGMENTS, AWR (Automatic Workload Repository), ASH (Active Session History).
Oracle's V$ views + AWR/ASH are much more comprehensive. PG pg_stat views are simpler. pg_stat_statements extension adds SQL-level stats.
DDL transactions
Transactional DDLPG only
CREATE, DROP, ALTER, TRUNCATE inside transactions. Can be rolled back. Enables atomic schema migrations.
Non-transactional DDL
DDL auto-commits before and after execution. Cannot be rolled back. Schema changes are always permanent immediately.
PG transactional DDL is a major advantage for safe deployments. Oracle DBAs must plan DDL rollback manually (re-run inverse DDL).
dump / export
pg_dump / pg_restore / COPY
pg_dump: logical backup (SQL or custom format). pg_basebackup: physical. COPY: fast bulk load/unload to files.
Data Pump (expdp/impdp) / exp/imp / SQL*Loader
Data Pump is the modern export/import tool. SQL*Loader for bulk data loading. exp/imp is legacy.
pg_dump is simpler and portable. Oracle Data Pump is faster for large databases and supports parallel export/import.
Replication & HA
physical replication
Streaming replication
WAL-based physical replication. Primary → standby. Synchronous or asynchronous. pg_basebackup sets up standby.
Data Guard (physical standby)
Oracle Data Guard applies redo to physical standby. Active Data Guard allows read queries on standby (extra licence).
PG streaming replication is built-in and free. Oracle Data Guard is a licensed enterprise option. Both support synchronous and async modes.
logical replication
Logical replication (PG 10+)
Publication/Subscription model. Row-level changes (INSERT/UPDATE/DELETE) replicated to subscribers. Selective tables.
GoldenGate / Streams / XStream
Oracle GoldenGate for heterogeneous logical replication. Oracle Streams (deprecated). XStream for Java integration.
PG logical replication is built-in for PG-to-PG. Oracle GoldenGate is a full product for heterogeneous replication.
replication slot
Replication slotPG only
Prevents WAL deletion until a subscriber has consumed it. Physical or logical slots. Danger: can cause WAL accumulation if subscriber lags.
Archived redo retention
Archive log retention managed by RMAN retention policy or Data Guard FAL. Redo not deleted until RMAN certifies backup.
PG replication slots are unique — monitor pg_replication_slots for lag. Unlimited lag can fill the disk with WAL.
failover
pg_ctl promote / Patroni / Repmgr
Manual promotion or tools like Patroni (etcd-based) or repmgr for automated failover. No built-in HA orchestration.
Data Guard Fast-Start Failover (FSFO)
Automatic failover with Data Guard Broker + observer. Very mature. Can failover in seconds.
Oracle FSFO is built-in and mature. PG relies on external orchestration tools. Patroni is the most widely used PG HA solution.
Security & Access Control
user / role
ROLE (user = role with LOGIN)
Unified ROLE concept. CREATE USER is syntactic sugar for CREATE ROLE … LOGIN. Roles can be nested (group roles).
USER + ROLE (separate objects)
Users and roles are distinct. Roles granted to users. Schema = user. Predefined roles: CONNECT, RESOURCE, DBA.
PG unified role model is simpler. Oracle separates users from roles; a PG user is just a role that can log in.
row security
Row Level Security (RLS)
ALTER TABLE … ENABLE ROW LEVEL SECURITY + CREATE POLICY. Policies use SQL expressions. Per-role policies.
Virtual Private Database (VPD) / RLS
DBMS_RLS package adds WHERE clauses dynamically. Context-sensitive. Part of Oracle Advanced Security.
PG RLS is built-in and SQL-based. Oracle VPD uses PL/SQL policy functions — more flexible but more complex to set up.
encryption
pgcrypto extension / TLS
pgcrypto for column-level encryption functions. TLS for connections. No built-in TDE (Transparent Data Encryption).
TDE (Transparent Data Encryption)
Encrypts tablespaces, columns, or entire database transparently. Part of Advanced Security option. Key stored in wallet.
Oracle TDE is built-in and transparent. PG lacks native TDE — filesystem encryption (dm-crypt/LUKS) is the common workaround. PG 16+ has some TDE progress.
audit
pgaudit extension / log_statement
pgaudit adds detailed session/object audit logging. log_statement = all logs every SQL. No built-in audit trail.
Unified Auditing (Oracle 12c+)
Comprehensive audit policies. Audit by user, object, privilege. Writes to AUDSYS.AUD$UNIFIED. Very granular.
Oracle Unified Auditing is significantly more comprehensive. PG requires pgaudit extension for production-grade audit requirements.
Network & Connectivity
wire protocol
libpq / PostgreSQL wire protocol
Open, well-documented TCP protocol. Port 5432. Used by psql, JDBC, ODBC, libpq. Protocol v3 since PG 7.4.
Oracle Net (SQL*Net) / TNS
Oracle proprietary network layer. tnsnames.ora or LDAP for name resolution. Port 1521. OCI / JDBC Thin driver.
PG protocol is open and reimplemented by many drivers. Oracle Net/TNS is proprietary; tnsnames.ora configuration is a common pain point.
auth config
pg_hba.conf
Host-Based Authentication config. Rules: host, database, user, IP range, auth method (md5, scram-sha-256, ldap, cert, etc.).
sqlnet.ora / Oracle Internet Directory
sqlnet.ora controls authentication methods. LDAP via OID or AD. OS authentication for local connections.
PG pg_hba.conf is simple and powerful. Oracle auth configuration is spread across multiple files and registry settings.
client tool
psql
Interactive terminal with \d meta-commands, \copy, \timing, \watch. Scriptable. Excellent for automation.
SQL*Plus / SQL Developer / SQLcl
SQL*Plus: text CLI. SQL Developer: GUI IDE. SQLcl: modern text client with scripting. All Oracle-specific.
psql is more scriptable and consistent. Oracle SQL*Plus has many quirks. SQLcl is the modern replacement. DBeaver works well for both.
System Catalog & Metadata
system catalog
pg_catalog schema
Regular tables in pg_catalog schema: pg_class, pg_attribute, pg_namespace, pg_type, pg_proc, etc. Queryable with plain SQL.
Data Dictionary (SYS.* / DBA_* / ALL_* / USER_*)
Views like DBA_TABLES, DBA_COLUMNS, DBA_INDEXES. Underlying tables in SYS schema. Standard INFORMATION_SCHEMA views too.
PG catalog tables are regular heap tables — you can query them directly. Oracle data dictionary is views over internal SYS tables.
object ID
OID (Object Identifier)
32-bit integer. Every pg_class row has an OID. relfilenode may differ from OID after TRUNCATE/VACUUM FULL.
OBJECT_ID / DATA_OBJECT_ID
OBJECT_ID from DBA_OBJECTS. DATA_OBJECT_ID changes on TRUNCATE (like PG relfilenode). Both are in USER_OBJECTS view.
PG OID ≈ Oracle OBJECT_ID. PG relfilenode ≈ Oracle DATA_OBJECT_ID. Both diverge from logical ID after certain DDL operations.
info schema
information_schemastandard
SQL standard INFORMATION_SCHEMA views. Less complete than pg_catalog but portable across databases.
information_schemastandard
Available since Oracle 12c. Less used by Oracle DBAs who prefer DBA_* views. Limited coverage.
Both implement SQL standard INFORMATION_SCHEMA. Use pg_catalog (PG) or DBA_* (Oracle) for full feature access.
extensions
EXTENSION (CREATE EXTENSION)PG only
Packaged add-ons: PostGIS, pgvector, pg_trgm, uuid-ossp, pageinspect, pg_stat_statements, timescaledb, etc.
Oracle options / Java stored procs / External libs
Functionality shipped as licensed options (Spatial, Text, XML DB). No equivalent community extension ecosystem.
PG's extension ecosystem is a major strength — especially PostGIS and pgvector. Oracle extensions are mostly proprietary licensed add-ons.
No matching terms found.
VACUUM deep dive
VACUUM is unique to PostgreSQL's heap-based MVCC. It reclaims dead tuples left by UPDATE/DELETE, updates the FSM and VM fork files, and advances the XID freeze horizon to prevent wraparound. autovacuum runs it automatically.
XID wraparound risk
PostgreSQL uses 32-bit transaction IDs. After ~2 billion transactions, the XID counter wraps around. Tuples that haven't been frozen appear to be "in the future." Monitor with: SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
Tablespace = symlink
A PostgreSQL tablespace is literally a symlink inside $PGDATA/pg_tblspc/. Each table is a plain OS file. You can inspect it with ls, du, or hexdump. Oracle's tablespace contains proprietary .dbf datafiles.
Schema ≠ User in PG
This is the most common conceptual shock for Oracle DBAs. In PG, schemas are just namespaces — creating a schema doesn't create a user, and vice versa. In Oracle, schema = user = storage owner.
DDL is transactional in PG
You can wrap CREATE TABLE, DROP INDEX, ALTER COLUMN inside a BEGIN…COMMIT block and roll it back if something goes wrong. Oracle DDL auto-commits — there's no rollback for DDL operations.
No SQL hints in PG
PG has no built-in hint syntax. Use GUC parameters (SET enable_seqscan = off) or the third-party pg_hint_plan extension for plan control. Oracle DBAs who rely heavily on hints must rethink their approach.