Scaling

Snowflake for Developers/Postgres/Scaling

On this page

Scaling

Postgres

Elizabeth Christensen

Understanding Your Workload

Before you reach for replicas, sharding, or any distributed setup, figure out what your database is actually doing. We see a lot of teams jump to a scaling fix that doesn'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

Start with 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 can take real load off the primary. 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, add connection pooling. Don't just bump the limit higher.

Vertical Scaling and Configuration

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

Key Parameters to Tune

Parameter	Starting Point	Purpose
`shared_buffers`	25% of RAM	Postgres shared memory cache
`effective_cache_size`	50 to 75% of RAM	Planner hint for OS page cache
`work_mem`	16 to 64 MB, more per session	Per-operation sort/hash memory
`maintenance_work_mem`	512 MB to 1 GB	VACUUM, CREATE INDEX operations
`max_parallel_workers_per_gather`	2 to 4	Parallel 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 16-64MB, raise per session 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 uses several MB of memory even when idle, and more once it has run queries and built up caches. 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 is to keep max_connections modest (50 to 200 for most workloads) and put a connection pooler like PgBouncer in front of Postgres. A pooler lets thousands of application connections share a small number of real database backends. The memory you'd have spent on idle connections goes to shared_buffers and the OS page cache instead, where it actually helps.

For the number of active backends the pooler should keep open, a common starting point is:

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

For most cloud instances with SSD/NVMe storage, that usually works out to 20 to 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. Start at about 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, check your cache hit ratio. For OLTP workloads you want 98 to 99%:

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. That means extra IOPS and slower queries.

-- Check current setting
SHOW work_mem;

-- Raise the global default modestly
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- Go bigger only in sessions running heavy sorts, hash joins, aggregations
SET work_mem = '256MB';

Be careful here: work_mem is allocated per operation, per connection. A query with 5 sort/hash nodes running on 20 concurrent connections at 128 MB could use 5 × 20 × 128 MB = 12.8 GB. Start conservative and raise it based on the temp file usage you actually see.

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. 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.

Things that use 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 mean you're going to disk a lot.

To see which tables are doing the most disk reads:

-- 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;

Reducing IOPS

In rough order of how often they help:

Add the right indexes so large tables aren't sequentially scanned on hot paths.
Increase shared_buffers so more data stays in cache.
Increase work_mem (carefully) so fewer sorts and hashes spill to temp files.
Spread checkpoint writes out with a larger max_wal_size and checkpoint_completion_target = 0.9.
Provision faster storage. Some cloud providers tie IOPS capacity to volume size, so a bigger disk can be a faster disk.
Add read replicas to spread read I/O across servers.

Postgres 18's asynchronous I/O (io_method) also helps sequential scans, bitmap heap scans, and vacuum keep the disk busier with fewer stalls, so it's one more reason to be on a current version.

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 cover the most common scaling problems we see: too many reads for one server, and too many connections for Postgres to handle well.

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 usually run well under a second behind, though heavy write bursts or long queries on the replica can push lag higher.

-- 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;

If your app writes and then immediately reads from a replica, that lag can bite you. Postgres 19 (in release candidate as of October 2026) adds a WAIT FOR command so a session on a replica can wait until it has replayed up to a given LSN before reading.

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 keep two connection pools, one for writes (read-write) and one for reads (any), with no external proxy. If you want reads to stay off the primary, use target_session_attrs=prefer-standby (Postgres 14+) instead of any.

Connection Pooling

Postgres uses a process-per-connection model. Each connection gets its own backend process with its own 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.

Pooling helps in a few ways. Fewer backends means more RAM for shared_buffers and the OS page cache, which cuts disk I/O. The kernel schedules fewer processes, so more CPU goes to running queries. Traffic spikes queue at the pooler instead of hitting Postgres with a wave of new process forks and authentication. And your app servers can hold 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 to 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.

If you're on Snowflake Postgres, check the Snowflake Postgres docs for the connection pooling options available on your instance before deploying your own PgBouncer.

Conclusion

Scaling Postgres happens in steps. Start by understanding your workload: the read/write ratio, query patterns, and connection pressure. Tune the single node. Add pooling to handle connection volume. Add read replicas when reads outgrow one server. Partition large tables. If you really do hit write-throughput limits, then look at sharding. Along the way, moving analytical and historical data out to Iceberg (for example with pg_lake) keeps Postgres focused on fast transactional work.

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)

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)

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 Oct 27, 2026

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