In the previous post, I covered some high-level areas around installation and architecture, but for this post, we’re going to go a little deeper.  For the seasoned Oracle DBA, this should feel like we’re stepping into a familiar landscape with just a few different rules. While both PostgreSQL and Oracle Database are robust, feature-rich systems, their physical architecture and internal mechanics diverge in key areas, especially around storage structures, memory architecture, and background processing.

In this post, we’ll break down these differences so Oracle DBAs can feel more comfortable with the shift when they transition between the two.

Physical Storage: Datafiles vs. File-per-Table

Although both databases require tier 1 storage to perform optimally, the actual physical and logical structure is quite different.  There are various nuances, but the basics are broken down along the following lines:

Oracle:

• Oracle uses tablespaces, which map to one or more datafiles.  There are a number of tablespaces that are delivered out-of-the-box to store system-owned objects.
• A tablespace can contain many tables, and the DBA manages space at the tablespace and datafile level.
• Most schemas have their own tablespace or an application-level created tablespace.  These tablespaces and datafiles don’t exist by default.
• Oracle uses block-level storage, where the size (typically 8KB) is fixed per database and crucial to performance tuning.
• Oracle may use its own storage volume manager, called Automatic Storage Management (ASM).  This removes some of the redundancy created by other volume managers and can increase performance as the storage management understands and aligns with the database platform. 

PostgreSQL:

• PostgreSQL stores each table and index in separate files under the base/ directory.
• Large tables are broken into 1GB segments by default. The physical files are named by the OID of the relation, not by table name.
• There’s no concept of tablespaces as default per schema (although PostgreSQL does support user-defined tablespaces for storage placement if desired.)

PostgreSQL’s file-per-object model is simpler but less abstracted than Oracle’s tablespace/datafile model. File-level operations like backup and forensic recovery can differ significantly (but this is a topic for another day.)

Memory Architecture: SGA vs. Shared Buffers

We touched on the WAL in the last post, but let’s dig into how this differs from Oracle’s caching and logging process vs. how PostgreSQL processes change.

Oracle:

• Oracle uses a well-defined memory architecture with the System Global Area (SGA) and Program Global Area (PGA).
• SGA includes shared pool, buffer cache, redo log buffer, etc.
• PGA is per-session and handles sort areas, session memory, etc.

PostgreSQL:

• PostgreSQL does not separate memory pools as rigidly.
• The most important shared memory component is **shared_buffers**, equivalent to Oracle’s buffer cache.
• Each backend process has its own memory (work_mem, maintenance_work_mem), similar in role to Oracle’s PGA.
• PostgreSQL writes transaction changes to WAL buffers before flushing them to disk, which is similar to Oracle’s log buffers.

Key Parameters you need to know:

• shared_buffers: Like Oracle’s buffer cache
• work_mem: Sort and hash operations (per operation, per session)
• maintenance_work_mem: Used for vacuum, create index, etc.

The most important factor to take away from the above information is that Oracle’s memory is more tightly managed at a component level, while PostgreSQL relies on simpler, OS-managed memory and per-process tuning.

Background Processes

Here’s where Oracle is most unlike other database platforms and Oracle DBAs should always be prepared for this difference.  Where Oracle can have 30+ background processes, each with their responsibility, most databases are either multi-threaded with only a few background processes or simply consolidate the whole of the tasks across a few processes.  This is no different for PostgreSQL.  The introduction of multenant for Oracle simplified much of this by moving the majority of background processes to be owned by the root container (CDB) and the pluggable database (PDB) no longer has dedicated background processes, changing the requirement that each Oracle database had dedicated background processes.

Oracle:

• Oracle has numerous background processes: DBWn, LGWR, ARCn, SMON, PMON, RECO and so on.
• Each process has a well-defined role, and tuning/control is centralized via the instance.

PostgreSQL:

• PostgreSQL has fewer but multi-functional background processes:
  • **checkpointer**: Writes dirty buffers to disk.
  • **bgwriter**: Handles background writes.
  • **walwriter**: Manages WAL flush.
  • **autovacuum launcher** and workers: Handle vacuuming and stats collection.
  • **logical replication** and **walsender**: Handle replication streams.
• Each user session is its own backend process. There is no shared process pool like Oracle’s shared server model (though there is a new PostgreSQL 16+ feature for pgbouncer-style connection pooling integration).

Oracle DBAs will be surprised at how lightweight PostgreSQL’s architecture is, but also how tuning is distributed across multiple autonomous processes.  This has been a challenge for many well-seasoned Oracle specialists.

Write Behavior and Transaction Logging

Oracle:

• Uses redo and undo logs. Undo tablespaces are used for consistent read and rollback.
• LGWR handles writes to the redo log buffer.
• DBWn writes dirty buffers to datafiles based on checkpoints or pressure.

PostgreSQL:

• Uses Write-Ahead Logging (WAL).
• No separate undo; MVCC (Multi-Version Concurrency Control) creates new row versions (tuple versions) for updates/deletes.
• WAL writes occur on commit or buffer flush, and checkpointing is crucial for performance.
• VACUUM (and autovacuum) reclaims space, and HOT (Heap Only Tuple) optimizations reduce index churn.

PostgreSQL’s WAL is conceptually like Oracle redo, but without separate undo management. Space reclamation depends on VACUUM rather than Oracle’s more complex UNDO scenario.

Checkpoints, Crash Recovery, and Startup

Oracle:

• On startup, SMON performs recovery from redo logs.
• Checkpoints are well-orchestrated and managed to control data consistency behavior.
• Flashback, RMAN, etc. provide strong recovery options.

PostgreSQL:

• On crash/startup, PostgreSQL replays WAL since the last checkpoint.
• Checkpoints are tunable via parameters like checkpoint_timeout, checkpoint_completion_target.
• No built-in snapshot-based flashback, but point-in-time recovery (PITR) is available via base backups and WAL archiving.

PostgreSQL is simpler but requires careful WAL and checkpoint configuration for large workloads or when expecting high availability.

Summary

PostgreSQL is a powerful and modern database system, but for Oracle DBAs, it can feel deceptively minimalist considering the robust nature of forty years of enterprise investment. The philosophical differences Oracle’s tightly managed instance-level abstraction vs. PostgreSQL’s Unix-like, process-driven simplicity inform nearly every aspect of administration and performance tuning.

As more hybrid and open-source workloads emerge, understanding these differences gives you the power to transition and choose the right database for the workload usage.

In future posts, I’ll dive into PostgreSQL transaction control, locking mechanisms, and performance tuning Oracle DBAs, you’ll want to pay close attention to the subtle (and not-so-subtle) differences in concurrency and query optimization.