PostgreSQL for Oracle DBAs: The Survival Guide

It’s Not the SQL — It’s the Assumptions

You know databases cold, so the hard part of your first PostgreSQL month isn’t syntax. It’s a handful of architectural assumptions that are simply different from Oracle, and each one causes a specific class of surprise. Learn these five up front and week one stops being a series of small shocks.

1. Every connection is a process

Oracle can multiplex sessions (Shared Server, DRCP). PostgreSQL forks a full OS process per connection, so a realistic max_connections is in the low hundreds, not thousands. Under real load a connection pooler — PgBouncer in transaction mode — is not optional; it’s the difference between “fine” and a server that falls over at peak.

2. MVCC keeps old rows in the table — so you VACUUM

There is no undo/rollback segment. An UPDATE writes a new row version and leaves the old one in place until nothing can see it; VACUUM (and autovacuum) reclaims it. That means bloat and wraparound are things you monitor, and a long-running transaction hurts by holding old versions alive.ANALYZE — often triggered by the same autovacuum — is also what keeps the planner honest; run it after any bulk load.

3. Things that simply aren’t there

• No packages. Use a schema as the namespace and functions inside it. Package state (session variables) has no direct equivalent — refactor or use a temp table / custom GUC.
• No query hints. By design. You influence the planner through statistics, indexes and cost settings, not /*+ INDEX */.
• No SGA to tune the Oracle way. shared_buffers plus the OS page cache do the work; you don’t size a single giant cache.
• Autonomous transactions are emulated with dblink; there’s no PRAGMA AUTONOMOUS_TRANSACTION.

4. The quiet ones that cause real bugs

These don’t announce themselves — they show up as wrong results or “object does not exist”:

-- Oracle folds unquoted identifiers to UPPERCASE.
-- PostgreSQL folds them to lowercase.
CREATE TABLE Employees (Id int);   -- actually creates "employees" ("id")
SELECT "Id" FROM "Employees";      -- ERROR: relation "Employees" does not exist

-- A ROLE that can log in IS a user — one object, not two.
CREATE ROLE analyst LOGIN PASSWORD '…';

-- search_path is your CURRENT_SCHEMA (and resolution order).
SHOW search_path;   -- "$user", public

5. Your week-one checklist

• Put a pooler in front before you load-test anything.
• Confirm autovacuum is on and watch pg_stat_user_tables for dead tuples.
• Run ANALYZE after migrations; read plans with EXPLAIN (ANALYZE, BUFFERS).
• Learn psql (\d, \timing, \x) — it’s your SQL*Plus.
• Set shared_buffers ≈ 25% RAM, effective_cache_size ≈ 50–75%, and leave the rest to the OS.