PostgreSQL for the Oracle DBA, Part 3: Transaction Control, Locking, and Performance Insight

Now that we finished Part 2 on physical data structures, storage and processes, it’s time to work our way into transaction control, locking and just a smidgen of performance insight.

This post is for Oracle DBAs who want to understand PostgreSQL’s transaction and locking mechanics, as well as how to monitor and tune performance without diving into execution plans just yet. Think of this as your quick-reference mental shift guide from Oracle to PostgreSQL.

Locking or What is MVCC Without Undo Segments

In Oracle, locks and concurrency are managed using undo segments, redo logs, and sophisticated multi-versioning. PostgreSQL also uses MVCC, but implements it differently.

Key PostgreSQL Locking Concepts:

• PostgreSQL doesn’t use undo segments. Instead, it leaves old row versions (“dead tuples”) in the table and cleans them up later via autovacuum.
• Oracle DBAs are used to “SELECT FOR UPDATE NOWAIT”, but in PostgreSQL, you’d use:

SELECT * FROM customers WHERE id = 10 FOR UPDATE NOWAIT;

• PostgreSQL locks at the row, table, or relation level using a different lock matrix than Oracle, as well.

Viewing Locks

 We need to shift a bit from using DBA_BLOCKERS, V$LOCK in Oracle and instead focus on pg_locks, joining to other views to gather a full understanding of the locking scenario which has evolved.

SELECT
  l.pid,
  a.datname,
  l.locktype,
  l.relation::regclass AS table_name,
  l.mode,
  l.granted,
  a.query,
  age(now(), a.query_start) AS age
FROM pg_locks l
JOIN pg_stat_activity a
  ON a.pid = l.pid
WHERE NOT l.granted
  AND a.datname = current_database()
ORDER BY age DESC;

This helps detect blocking sessions. You won’t find the same information as you would in Oracle’s V$SESSION_WAIT here, but the next section helps locate this next step in the troubleshooting process and that comes from pg_stat_activity.

Performance Monitoring and Tuning

In PostgreSQL, just as with Oracle, it’s all about views and waits. Where Oracle DBAs are used to a rich set of performance views: V$SQL, V$SESSION, V$SYSTEM_EVENT, etc. or paid views and objects that are part of the Automatic Workload Repository (AWR).

PostgreSQL gives you similar observability using catalog views, but more like performance values closer to Statspack vs. the power of AWR/ASH unless you add extensions like pg_stat_statements.

Monitor Active Queries

You also have the ability to monitor PostgreSQL with similar capabilities to queries using V$SESSION or V$ACTIVE_SESSION_HISTORY, It just requires the pg_stat_activity.

SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Understanding Table Hotspots

The way this is done in PostgreSQL is very similar to in Oracle using DBA_TAB_MODIFICATIONS.

SELECT relname,
       seq_scan,
       idx_scan,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_tup_hot_upd
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

This will provide the list of tables experiencing heavy concurrency, which might need autovacuum tuning or index optimization.

PostgreSQL’s Version of V$SESSION_WAIT

PostgreSQL categorizes waits into types like I/O, Client, Lock, and LWLock (lightweight locks), which aren’t much different than Oracle, but far less different distinct wait events.

SELECT pid,
       wait_event_type,
       wait_event,
       state,
       query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

This gives you direct insight into what a session is doing, similar to Oracle’s wait model, but more coarse-grained unless extended with pg_wait_sampling (if available, as this is another extension.)

Bloat and Vacuum, the Hidden Killer

Oracle DBAs know that undo and redo are recycled, and block cleaning is managed automatically. In PostgreSQL, dead tuples must be cleaned up via autovacuum.

SELECT relname,
       n_dead_tup,
       last_autovacuum,
       vacuum_count,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If n_dead_tup is high, you’re risking table bloat and slow sequential scans. You may need to adjust autovacuum thresholds (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor) for specific tables.

Key Differences

Here’s a table covering some of the differences between how Oracle vs. PostgreSQL covers locking and waits. 

Feature	Oracle	PostgreSQL
Transaction Start	Implicit on first DML	Explicit with BEGIN
Undo Handling	Undo segments + redo logs	MVCC with tuple versioning
Lock Views	V$LOCK, DBA_BLOCKERS	pg_locks, pg_stat_activity
Wait Views	V$SESSION_WAIT, ASH	pg_stat_activity
Vacuum / Cleanup	Automatic, background	Autovacuum must be tuned
Execution History	V$SQL, AWR	pg_stat_statements (extension)

Summary

As a seasoned Oracle DBA, embracing PostgreSQL means understanding a new MVCC model, proactive autovacuum tuning, and leveraging catalog views in place of Oracle’s deep v$ views. While PostgreSQL doesn’t ship with AWR-level diagnostics out-of-the-box, its transparency and community tooling still provide the observability needed for production-grade performance tuning.

Next up? We’ll walk through pg_stat_statements, query tuning, and how PostgreSQL’s planner compares to Oracle’s CBO.