Skip to content

Snowflake World Tour hits your city

See how leading teams deploy agents at scale. Find a stop near you. Register free.

Register now

Understanding Your Workload

Before you reach for replicas, sharding, or any distributed architecture, start by understanding what your database is actually doing. Too many teams jump to scaling solutions that don't match their bottleneck. A write-heavy workload won't benefit from read replicas. A CPU-bound analytical query won't improve with connection pooling. Characterize the problem first.

Read-Heavy vs. Write-Heavy

The single most useful question is: what's the ratio of reads to writes? Postgres tracks this in pg_stat_database:

-- Workload profile: reads vs. writes
SELECT
  datname,
  tup_returned + tup_fetched AS total_reads,
  tup_inserted + tup_updated + tup_deleted AS total_writes,
  ROUND(
    (tup_returned + tup_fetched)::numeric /
    NULLIF(tup_inserted + tup_updated + tup_deleted, 0), 1
  ) AS read_write_ratio
FROM pg_stat_database
WHERE datname = current_database();

A read-to-write ratio of 10:1 or higher is common in web applications — and it means read replicas will give you immediate relief. A ratio closer to 1:1 means you need write-scaling strategies like partitioning or sharding.

Where Is Time Being Spent?

Use pg_stat_statements to find your most expensive queries by total execution time:

-- Top 10 queries by total time
SELECT
  LEFT(query, 80) AS query_preview,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This tells you whether your bottleneck is a few expensive queries (optimize them) or thousands of small queries competing for connections (pool them).

Connection Pressure

Check whether you're running into connection limits:

-- Current connections vs. limit
SELECT
  count(*) AS active_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
  ROUND(
    count(*)::numeric /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') * 100, 1
  ) AS pct_used
FROM pg_stat_activity
WHERE backend_type = 'client backend';

If you're consistently above 70% of max_connections, connection pooling should be your next step — not bumping the limit higher.

Vertical Scaling and Configuration

Vertical scaling is the simplest path and should be your first move. A well-tuned single Postgres instance on modern hardware can handle thousands of transactions per second. Don't introduce distributed complexity until you've exhausted what a single node can do.

Key Parameters to Tune

ParameterStarting PointPurpose
shared_buffers25% of RAMPostgres shared memory cache
effective_cache_size50–75% of RAMPlanner hint for OS page cache
work_mem64–256 MBPer-operation sort/hash memory
maintenance_work_mem512 MB–1 GBVACUUM, CREATE INDEX operations
max_parallel_workers_per_gather2–4Parallel query workers

Check Current Configuration

SELECT name, setting, unit,
  CASE
    WHEN name = 'shared_buffers' THEN 'Target: 25% of RAM'
    WHEN name = 'effective_cache_size' THEN 'Target: 50-75% of RAM'
    WHEN name = 'work_mem' THEN 'Start at 64MB, increase for complex queries'
    WHEN name = 'max_connections' THEN 'Keep low; use pooling instead'
  END AS guidance
FROM pg_settings
WHERE name IN (
  'shared_buffers', 'effective_cache_size', 'work_mem',
  'maintenance_work_mem', 'max_connections',
  'max_parallel_workers_per_gather', 'max_worker_processes'
);

Memory Per Connection

Each Postgres connection consumes 5–10 MB of memory even when idle. This is why max_connections = 1000 on a 16 GB server is a recipe for OOM kills:

-- Estimate memory pressure from connections
SELECT
  count(*) AS connections,
  pg_size_pretty(count(*) * 10 * 1024 * 1024) AS estimated_conn_memory,
  pg_size_pretty(
    (SELECT setting::bigint * 8192 FROM pg_settings WHERE name = 'shared_buffers')
  ) AS shared_buffers
FROM pg_stat_activity;

The better approach: keep max_connections low (50–200 for most workloads) and use a connection pooler like PgBouncer in front of Postgres. A pooler lets thousands of application connections share a small number of actual database backends. This frees memory that would otherwise be wasted on idle connections and redirects it toward shared_buffers and OS page cache — where it actually improves performance.

A good rule of thumb for setting max_connections:

max_connections = (number of CPU cores × 2) + effective_spindle_count

For most cloud instances with SSD/NVMe storage, that works out to 20–100 actual backends. Let the pooler handle concurrency above that. See the Connection Pooling section below for PgBouncer configuration.

Increasing shared_buffers

The shared_buffers parameter controls how much memory Postgres dedicates to its internal cache of table and index data. The default is only 128 MB, which is far too low for any production workload. Set it to 15–25% of your machine's total RAM:

-- Check your current setting
SHOW shared_buffers;

-- For an 8 GB machine, set to ~2 GB
ALTER SYSTEM SET shared_buffers = '2GB';

Changing shared_buffers requires a restart. After bumping it, verify that your cache hit ratio stays healthy — you want 98–99% for OLTP workloads:

SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit) AS heap_hit,
  ROUND(
    sum(heap_blks_hit)::numeric /
    (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2
  ) AS cache_hit_pct
FROM pg_statio_user_tables;

If your cache hit ratio is below 98%, you likely need more memory allocated to shared_buffers or a larger instance. Note that analytical/warehouse workloads will typically have a lower cache hit ratio — that's expected when queries scan large ranges of data.

Tuning work_mem

work_mem controls how much memory each sort or hash operation can use before spilling to disk temporary files. The default is only 4 MB, which forces Postgres to write temp files to disk for moderately complex queries — causing unnecessary IOPS and slower execution.

-- Check current setting
SHOW work_mem;

-- Increase for complex queries (sorts, hash joins, aggregations)
ALTER SYSTEM SET work_mem = '128MB';
SELECT pg_reload_conf();

Be cautious: work_mem is allocated per operation, per connection. A complex query with 5 sort/hash nodes on a server handling 20 concurrent connections could use 5 × 20 × 128 MB = 12.8 GB. Start conservative and increase based on observed temp file usage.

To identify queries spilling to disk, set log_temp_files to log anything above a threshold:

-- Log temp files larger than 10 MB
ALTER SYSTEM SET log_temp_files = '10240';  -- in kB
SELECT pg_reload_conf();

Then check your logs for entries like LOG: temporary file: path "base/pgsql_tmp/...", size 52428800. These are your candidates for work_mem increases or query optimization.

Understanding IOPS

Disk IOPS (Input/Output Operations Per Second) is often the hidden bottleneck in Postgres performance. Even when your data mostly fits in cache, Postgres still needs disk I/O for checkpoints, WAL writes, vacuum, index creation, and temporary files from queries that exceed work_mem.

Common operations that consume IOPS:

  • Checkpoints flushing dirty pages to disk
  • WAL (Write-Ahead Log) writes for every transaction
  • Autovacuum reading and modifying table data
  • Queries spilling sort/hash operations to temporary files
  • Backups and large sequential scans
  • Index creation and rebuilds

Detecting I/O pressure:

Enable track_io_timing to see how much time queries spend waiting on disk:

ALTER SYSTEM SET track_io_timing = 'on';
SELECT pg_reload_conf();

Then use EXPLAIN (ANALYZE, BUFFERS) to see whether queries are hitting disk:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM large_table;

In the output, look for the I/O Timings line and the ratio of shared read (disk) vs shared hit (cache). High read counts relative to hits indicate I/O pressure.

Monitoring I/O per table:

-- Tables with the highest disk read activity
SELECT
  schemaname || '.' || relname AS table,
  heap_blks_read AS disk_reads,
  heap_blks_hit AS cache_hits,
  ROUND(
    heap_blks_hit::numeric /
    NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 1
  ) AS hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

Strategies to reduce IOPS consumption:

  1. Increase shared_buffers — more data stays in cache, fewer disk reads
  2. Increase work_mem — fewer temp file writes from sort/hash operations
  3. Add appropriate indexes — avoid sequential scans on large tables
  4. Tune checkpoint settings — spread checkpoint writes over longer intervals with checkpoint_completion_target = 0.9
  5. Upgrade storage — provision higher IOPS volumes or use NVMe storage; some cloud providers tie IOPS capacity to volume size
  6. Add read replicas — distribute read I/O across multiple servers

aside negative Watch for burst IOPS limits. Many cloud storage volumes offer burstable IOPS — you accrue credits during low activity and spend them during spikes. If you exhaust your burst budget, performance drops to a baseline that can cause query timeouts and apparent outages. Monitor your IOPS usage against provider limits.

When Vertical Scaling Isn't Enough

Move to scale-out strategies when:

  • CPU is saturated across all cores during normal operations
  • Your working set (hot data) exceeds available RAM
  • Write throughput is limited by single-disk I/O, even with NVMe
  • You need sub-10ms latency for users in multiple geographic regions

Read Replicas and Connection Pooling

These two techniques address the most common scaling bottlenecks: too many read queries for one server, and too many connections for Postgres to manage efficiently.

Read Replicas with Streaming Replication

Postgres streaming replication ships WAL records continuously from a primary to one or more standbys. Replicas are read-only and typically have sub-second lag.

-- On the primary: check replication status
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Multi-Host Connection Strings

Postgres libpq supports multiple hosts in a single connection string with target session attributes to route reads and writes:

# Write connections go to primary
postgresql://primary.db:5432,replica1.db:5432,replica2.db:5432/myapp?target_session_attrs=read-write

# Read connections can use any server
postgresql://primary.db:5432,replica1.db:5432,replica2.db:5432/myapp?target_session_attrs=any

Your application can maintain two connection pools — one for writes (read-write) and one for reads (any) — with no external proxy required.

Connection Pooling

Postgres uses a process-per-connection model — each connection spawns a backend process that consumes 5–10 MB of memory. This limits most servers to a few hundred actual backends before you hit memory or CPU scheduling pressure. Connection pooling solves this by letting thousands of application connections share a small number of real database connections.

Why pooling helps you scale:

  • Frees memory for caching — fewer backends means more RAM available for shared_buffers and the OS page cache, which directly reduces disk I/O
  • Reduces context switching — the kernel schedules fewer processes, so CPU time goes to query execution instead of overhead
  • Handles traffic spikes — burst connections queue at the pooler instead of overwhelming Postgres with fork/auth overhead
  • Enables higher application concurrency — your app servers can maintain hundreds of connections each without the database caring

The typical setup: application servers connect to a pooler (like PgBouncer) on port 6432, which multiplexes those connections down to 20–50 actual Postgres backends. Most application connections are idle at any given moment — waiting for the next HTTP request — and the pooler takes advantage of that.

For full configuration details, pooling modes, and monitoring guidance, see the Connection Management page.

aside positive Snowflake Postgres includes a built-in connection pooler — no need to deploy and manage PgBouncer separately. The managed pooler handles connection multiplexing automatically with transaction-mode semantics.

Conclusion

Scaling Postgres is a progression, not a single decision. Start with understanding your workload — the read/write ratio, query patterns, and connection pressure. Tune the single node. Add pooling to handle connection volume. Introduce read replicas when reads outstrip a single server. Partition large tables. And when you genuinely hit write-throughput limits, look at sharding. Along the way, offload analytical and historical data to the lakehouse so Postgres can focus on what it does best: fast transactional processing.

Resources

Is Postgres Read Heavy or Write Heavy? — Crunchy Data Blog

Workload characterization techniques and profiling queries for production databases.

An Overview of Distributed PostgreSQL Architectures — Marco Slot

Comprehensive comparison of Citus, read replicas, and other scale-out patterns.

pg_lake: Bridging Postgres and the Data Lakehouse

Engineering deep-dive on offloading analytical data from Postgres to Iceberg.

Top 10 Postgres Management Tasks — Crunchy Data Blog

Practical checklist covering shared_buffers, cache hit ratio, statement timeouts, indexes, and connection tuning.

Understanding Postgres IOPS — Crunchy Data Blog

Deep dive on why IOPS matters even when data fits in cache, how to measure I/O pressure, and strategies for improvement.

Connection Management Guide

Detailed guide on pooling strategies, timeout configuration, and connection monitoring.

Updated 2026-07-08

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances