De nombreuses entreprises qui migrent d'Oracle à PostgreSQL supposent que le principal défi sera de Différences de syntaxe SQL.

Mais en réalité, les changements les plus importants sont architectural.

Si vous venez d'un environnement Oracle (RAC, Exadata, Enterprise), il est essentiel de comprendre ces différences car elles ont une incidence directe :

• Optimisation des performances
• Dépannage
• Planification des capacités
• Stratégies de mise à l'échelle

Ci-dessous 8 différences architecturales clés entre Oracle et PostgreSQL qui ont le plus d'impact sur les performances.

1. Process Model

Oracle: Shared Memory Architecture

Oracle relies heavily on shared memory structures inside the SGA (System Global Area).

Typical architecture:

User process
   |
Server process
   |
SGA (shared memory)

Key SGA components include:

SGA
 ├ Buffer cache
 ├ Shared pool
 ├ Redo log buffer
 └ Large pool

Many sessions access the same memory structures simultaneously.

To control concurrency Oracle uses mechanisms like:

• Latches
• Mutexes
• Library cache locks

PostgreSQL: Process-Per-Connection

PostgreSQL uses a process-per-connection architecture.

Client
   |
PostgreSQL backend process

Each connection spawns a separate OS process.

Exemple :

postgres
 ├ backend process 1
 ├ backend process 2
 ├ backend process 3

Shared memory is smaller and simpler.

Main shared areas include:

shared_buffers
wal_buffers

Performance Impact

PostgreSQL cannot efficiently handle thousands of direct connections.

That’s why connection poolers like:

pgBouncer
pgpool

are commonly used in production.

2. Concurrency Control (MVCC)

Both Oracle and PostgreSQL use MVCC (Multi-Version Concurrency Control), but they implement it differently.

Oracle MVCC

Oracle stores previous row versions in UNDO tablespaces.

Example operation:

UPDATE table

Oracle writes the previous version into:

UNDO

When a query needs an older snapshot, Oracle reconstructs it using UNDO.

PostgreSQL MVCC

PostgreSQL stores row versions directly inside the table.

Example update:

UPDATE row

PostgreSQL creates a new row version.

old row (dead)
new row (visible)

Performance Impact

Because old row versions stay inside tables, PostgreSQL tables grow over time.

That is why PostgreSQL requires:

VACUUM

Without proper cleanup you get:

table bloat
index bloat

This is one of the biggest conceptual differences for Oracle DBAs.

3. VACUUM vs Oracle Automatic Cleanup

Oracle automatically manages old row versions using the UNDO system.

PostgreSQL requires periodic cleanup through VACUUM.

Two main types exist:

VACUUM
AUTOVACUUM

Autovacuum automatically removes dead tuples.

If autovacuum is not properly tuned you may experience:

table bloat
slow sequential scans
very large indexes

Performance Implication

In PostgreSQL environments:

autovacuum tuning is critical

Many production issues originate from:

autovacuum not aggressive enough

4. WAL vs Oracle Redo Logs

Both systems use write-ahead logging for crash recovery.

Oracle Logging

Oracle uses redo logs.

Changes flow through:

redo log buffer
→ redo log files

Background process responsible:

LGWR

PostgreSQL Logging

PostgreSQL uses WAL (Write Ahead Logging).

Data is written into:

WAL segments

Location:

pg_wal/

Example file:

00000001000000000000000A

Performance Implication

PostgreSQL performance often depends on:

WAL throughput
disk latency
checkpoint tuning

Important parameters include:

checkpoint_timeout
max_wal_size
wal_buffers

5. Index Behavior

Oracle indexes remain relatively stable even under heavy updates.

PostgreSQL indexes behave differently due to MVCC.

When a row is updated:

UPDATE row

PostgreSQL creates:

new row version
new index entry

The previous index entry remains until vacuum runs.

Performance Implication

PostgreSQL indexes can bloat significantly.

Periodic maintenance may require:

REINDEX
VACUUM FULL

Or monitoring views such as:

pg_stat_all_indexes

6. Query Optimizer Differences

Oracle has an extremely sophisticated optimizer with features like:

adaptive plans
parallel query
optimizer hints
statistics feedback

PostgreSQL's optimizer is simpler and mainly relies on:

statistics
cost model

Statistics are stored in:

pg_statistic

Updated through:

ANALYZE

Performance Implication

In PostgreSQL:

bad statistics = bad execution plans

This is why autovacuum analyze is important.

7. Parallel Execution

Oracle parallel query is highly advanced.

Exemple :

SELECT /*+ PARALLEL(8) */ ...

PostgreSQL added parallelism later.

Common operations include:

Parallel Seq Scan
Parallel Hash Join
Parallel Aggregate

Controlled by parameters like:

max_parallel_workers_per_gather
max_worker_processes

Performance Implication

Parallelism in PostgreSQL is less aggressive than Oracle, but still valuable for:

large analytical queries

8. Cluster Architecture

This is where Oracle and PostgreSQL differ the most.

Oracle RAC

Oracle RAC allows multiple nodes to write simultaneously.

Node1
Node2
Node3

All nodes access the same shared database.

PostgreSQL Replication

PostgreSQL uses a single-writer architecture.

Primary
   |
Standby replicas

Replication methods include:

streaming replication
logical replication

Performance Implication

Scaling strategies differ significantly.

Oracle RAC scaling:

add nodes → more write capacity

PostgreSQL scaling:

read scaling → replicas
write scaling → sharding

Oracle vs PostgreSQL Architecture Summary

The 3 Things That Surprise Oracle DBAs the Most

During real migrations, these three issues appear frequently.

1. Autovacuum Problems

Symptoms:

tables growing
indexes growing
slow queries

2. Too Many Connections

PostgreSQL does not scale well with huge connection counts.

1000+ connections

The typical solution is:

pgBouncer

3. WAL Bottlenecks

Write-heavy workloads can saturate:

WAL disk throughput

Réflexions finales

Migrating from Oracle to PostgreSQL is not just about translating SQL.

It requires understanding fundamental architectural differences that influence performance, scaling, and operational practices.

Once these differences are understood, PostgreSQL becomes an extremely powerful and flexible platform for modern data systems.