Migrating Oracle Read-Heavy Workloads to Postgres: Connection and Cache Architecture

The Performance Profile of a Read-Heavy Oracle System

Read-heavy Oracle workloads — reporting databases, API backends serving product catalogs, analytics-adjacent OLTP — typically exhibit a recognizable profile: high buffer cache hit ratios (98%+), extensive use of the shared SQL area for cursor reuse, often an Oracle Result Cache for frequently-executed reference data queries, and connection counts that are manageable because Oracle's dedicated server model assigns one server process per connection and the workload sustains reasonable concurrency on that model.

None of these characteristics translate directly to PostgreSQL. The architectural differences require deliberate redesign decisions before cutover — not tuning after the fact.

Connection Architecture: Dedicated Server vs Process-per-Connection

Oracle's default dedicated server model dedicates one Oracle server process to each client connection for the lifetime of that connection. The server process holds the session's PGA (Program Global Area) — sort memory, cursor state, session variables — entirely within that process's private memory. Oracle also offers shared server (formerly MTS) for connection multiplexing, but most OLTP and read-heavy deployments use dedicated server.

PostgreSQL uses a similar process-per-connection model: each client connection spawns a backend process. Where Oracle and PostgreSQL diverge is in what happens at scale. Oracle on a beefy server can sustain 500-1000 dedicated connections with manageable overhead because Oracle's SGA (shared global area) is separate from the per-process PGA. PostgreSQL backends share memory via shared_buffers but also each carry per-process overhead — typically 5-10 MB RSS per backend at idle, plus work_mem and per-query allocation.

On a read-heavy Oracle system running 400 concurrent connections, the direct port to PostgreSQL at 400 connections will show degraded performance — not because PostgreSQL cannot handle the load, but because 400 PostgreSQL backends at 10 MB each is 4 GB of process overhead before a single query runs, and the process scheduler overhead at that concurrency affects tail latency.

The Connection Pooling Requirement

PostgreSQL read-heavy workloads require a connection pooler in front of the database. PgBouncer in session mode is the lowest-risk starting point for Oracle migrations: it provides connection multiplexing without breaking any session-dependent behavior (prepared statements, SET variables, advisory locks) that may be present in migrated code.

# PgBouncer configuration for Oracle migration — start with session mode
[databases]
prod = host=aurora-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com
       port=5432 dbname=prod

[pgbouncer]
pool_mode = session          # safest for migrated Oracle code
max_client_conn = 500        # match Oracle's connection ceiling
default_pool_size = 80       # actual Postgres backends
min_pool_size = 20           # keep warm connections ready
reserve_pool_size = 10       # burst headroom
reserve_pool_timeout = 3

server_idle_timeout = 600    # recycle idle backends after 10 min
server_lifetime = 3600       # recycle backends hourly

After migration stabilizes, assess whether transaction mode pooling is feasible. Transaction mode achieves higher multiplexing ratios but breaks named prepared statements, advisory locks, and SET search_path — all of which may be present in migrated Oracle application code. The assessment period should run at least 2-4 weeks of production traffic before attempting the switch.

Shared SQL Area vs PostgreSQL Plan Cache

Oracle's shared SQL area caches parsed query plans across sessions. When 100 connections execute the same SELECT statement, Oracle parses it once, caches the execution plan, and all 100 connections reuse the cached plan. Cursor sharing extends this to parameterized variants of the same query. The buffer pool hit rate for frequently-executed queries is excellent because the plan cache keeps hot queries in the shared pool.

PostgreSQL caches execution plans per backend process, not globally. Each new backend connection that executes a query for the first time incurs a parse/plan cost. For read-heavy workloads with short-lived connections (common in connection-pooled environments), this per-connection plan cost accumulates.

The mitigation is extended query protocol with prepared statements. When an application uses prepared statements (either explicitly or via the PostgreSQL binary protocol that most JDBC/pg drivers use by default), PostgreSQL caches the plan within the backend after the fifth execution:

-- PostgreSQL plan caching behavior with prepared statements
-- After 5 executions, PostgreSQL switches from generic to custom plan

-- Explicit prepared statement (plan cached after 5 uses)
PREPARE get_orders(int) AS
  SELECT order_id, status, total
  FROM orders
  WHERE customer_id = $1 AND status != 'cancelled';

EXECUTE get_orders(12345);

-- Check plan cache status
SELECT name, statement, prepare_time, calls, generic_plans, custom_plans
FROM pg_prepared_statements;

For Aurora PostgreSQL, the connection pooler (PgBouncer or RDS Proxy) handles prepared statement caching interactions. RDS Proxy specifically has built-in prepared statement pinning behavior — connections with active named prepared statements are pinned to a specific backend rather than being multiplexed. Understand this before choosing RDS Proxy over PgBouncer for a migration where the application uses prepared statements heavily.

Oracle Result Cache: The Equivalent Architecture

Oracle's Result Cache stores the actual result set of frequently-executed queries in the SGA, shared across all sessions. For read-heavy workloads where certain reference queries return the same results thousands of times per minute — product catalog lookups, configuration tables, exchange rate queries — the Result Cache eliminates repetitive I/O and CPU entirely. A query that would otherwise touch the buffer cache 500 times per second instead returns from the result cache.

PostgreSQL has no built-in equivalent of Oracle's Result Cache. The architectural options for migrated workloads:

Option 1: Application-Layer Caching

For reference data that changes infrequently (currency codes, product categories, configuration tables), move caching to the application layer using Redis or Memcached. This is the most scalable approach and the correct architectural decision for most production systems regardless of the underlying database.

# Python example: application-layer cache replacing Oracle Result Cache
import redis
import psycopg2
import json

r = redis.Redis(host='redis.internal', port=6379, db=0)

def get_product_category(category_id: int) -> dict:
    cache_key = f"product_category:{category_id}"

    # Check Redis first
    cached = r.get(cache_key)
    if cached:
        return json.loads(cached)

    # Cache miss: query PostgreSQL
    with psycopg2.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id, name, description, parent_id "
                "FROM product_categories WHERE id = %s",
                (category_id,)
            )
            row = cur.fetchone()
            if row:
                result = {
                    "id": row[0], "name": row[1],
                    "description": row[2], "parent_id": row[3]
                }
                # Cache for 5 minutes (reference data changes rarely)
                r.setex(cache_key, 300, json.dumps(result))
                return result
    return None

Option 2: Materialized Views

For complex aggregation queries that are read frequently and can tolerate periodic staleness, PostgreSQL materialized views provide database-layer caching without application code changes:

-- Materialized view replacing a frequently-computed Oracle result
CREATE MATERIALIZED VIEW daily_order_summary AS
SELECT
  date_trunc('day', order_date) AS order_day,
  status,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= now() - interval '90 days'
GROUP BY 1, 2
WITH DATA;

CREATE UNIQUE INDEX ON daily_order_summary (order_day, status);

-- Refresh on a schedule (pg_cron or external scheduler)
SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_summary');

Option 3: pg_prewarm for Buffer Cache Population

Oracle's buffer cache warming is automatic on instance startup — frequently-accessed blocks are paged back in quickly from the shared pool. PostgreSQL's shared_buffers start cold after a restart. For read-heavy workloads where a warm buffer cache is critical to meeting latency SLAs, pg_prewarm can preload key tables and indexes:

-- Load pg_prewarm extension
CREATE EXTENSION IF NOT EXISTS pg_prewarm;

-- Prewarm critical tables and their indexes after instance start
SELECT pg_prewarm('orders');
SELECT pg_prewarm('orders_customer_idx');
SELECT pg_prewarm('products');
SELECT pg_prewarm('products_category_idx');

-- Check buffer cache occupancy
SELECT
  c.relname,
  count(*) AS cached_blocks,
  round(count(*) * 8192 / 1024.0 / 1024, 2) AS cached_mb
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
GROUP BY c.relname
ORDER BY cached_blocks DESC
LIMIT 20;

Read Replica Architecture

Oracle read-heavy systems often use Active Data Guard read-only standbys for query offload. The Aurora equivalent is Aurora read replicas, which share the same underlying storage as the writer and reflect writes with sub-second replica lag.

The connection routing difference matters: Oracle applications that direct read traffic to the standby via a separate JDBC URL need to be updated to use Aurora's reader endpoint (which load-balances across all reader instances) or a more sophisticated routing layer:

# Aurora endpoints for read/write separation
Writer endpoint: cluster.cluster-xxxx.us-east-1.rds.amazonaws.com
Reader endpoint: cluster.cluster-ro-xxxx.us-east-1.rds.amazonaws.com

# PgBouncer configuration with separate read/write pools
[databases]
prod_write = host=cluster.cluster-xxxx.us-east-1.rds.amazonaws.com
             port=5432 dbname=prod
prod_read  = host=cluster.cluster-ro-xxxx.us-east-1.rds.amazonaws.com
             port=5432 dbname=prod

# Application connects to prod_write for writes, prod_read for reads
# No connection-level routing — application must explicitly choose

Unlike Oracle Active Data Guard's session-level read/write splitting, Aurora requires explicit application-level routing. Applications that use a single connection for all queries cannot automatically benefit from Aurora readers — the application must maintain separate connection pools and route queries appropriately.

The Pre-Cutover Checklist

For a read-heavy Oracle-to-Aurora PostgreSQL migration, the connection and cache architecture decisions to resolve before go-live:

• Deploy PgBouncer or RDS Proxy in session mode before cutover. Do not attempt transaction mode until the migrated application is validated.
• Identify all Oracle Result Cache queries (SELECT /*+ RESULT_CACHE */ hints or result_cache table-level settings) and document each with a replacement strategy: application cache, materialized view, or accept the performance difference.
• Set shared_buffers to 25% of instance RAM and effective_cache_size to 75% of instance RAM. These are starting points; monitor buffer cache hit rates post-migration.
• Configure pg_prewarm for the top-10 most accessed tables and indexes to reduce cold-start latency.
• Verify the application uses prepared statements (binary protocol) rather than text-protocol queries, to enable PostgreSQL plan caching.
• Document Aurora reader endpoints and update application connection configuration to route read-only queries to readers.

None of these require schema changes. They are operational and architectural decisions that must be made before cutover to avoid a post-migration performance regression that looks like a database problem but is actually a missing architectural layer.