Why Oracle DBAs Must Rethink PostgreSQL’s work_mem and Cursor Sharing

Many Oracle DBAs, both in China and abroad, are learning PostgreSQL as database diversification and open‑source adoption become trends. Although PostgreSQL shares some architectural and functional similarities with Oracle, the two systems differ significantly, and misconceptions can lead to performance problems.

One common misunderstanding is treating PostgreSQL’s work_mem like Oracle’s manually managed PGA area. While they appear similar, using work_mem exactly as you would set Oracle’s *_area_size often creates pitfalls. PostgreSQL lacks a HASH ANTI‑JOIN operator, so NOT IN subqueries are usually executed with a Filter operator that builds a hash table from the subquery result and probes it from the outer table.

When the inner table’s result set requires a hash table larger than the configured work_mem, the execution plan changes dramatically. Without a hash table, the same query took 75,146 ms on PostgreSQL 14. PostgreSQL does not automatically use multiple work_mem allocations or a two‑pass hash table like Oracle; the only remedy is to increase work_mem at the session level.

Increasing work_mem to 128 MB restores hash‑table usage and improves performance. However, setting it too high can exhaust physical memory, while setting it too low may prevent certain queries from executing efficiently. For problematic queries, a session‑level work_mem adjustment is often the best solution.

The second source of confusion for Oracle DBAs is PostgreSQL’s cursor‑sharing mechanism. Oracle’s global cursor sharing, driven by the shared pool, helps avoid repeated parsing and plan generation. PostgreSQL lacks a global shared pool; its plan caching is session‑level. In a session, the first five executions of a statement each generate a plan, and if a common plan is found, it is reused for subsequent executions.

This session‑level approach makes management harder. Differences in bind variable values can cause different sessions to generate distinct plans, leading to performance variance. In Oracle you can purge a problematic cursor from the shared pool; in PostgreSQL you must either issue DDL on the related tables (which invalidates all sessions’ plans) or terminate the offending session.

Running DDL on a heavily concurrent PostgreSQL instance can trigger a “SQL parsing storm,” overwhelming CPU and causing performance failures. Therefore, DBAs should provision ample CPU and physical memory, especially in high‑load environments, to mitigate the impact of these issues.

In summary, Oracle DBAs moving to PostgreSQL should be aware that work_mem settings directly influence execution plans and query latency, and that PostgreSQL’s session‑level cursor sharing lacks the global management features of Oracle, requiring careful resource planning and occasional session‑level tuning.