Performance Optimization

Snowflake for Developers/Postgres/Performance Optimization

On this page

Performance Optimization

Postgres

Elizabeth Christensen

Reading EXPLAIN Plans

Performance tuning in Postgres starts in the same place every time: the query plan. EXPLAIN is how Postgres tells you what it's doing with your query, and usually why it's slow.

There are two forms, and the difference matters:

-- Estimated plan only (does NOT execute the query)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Actually executes and shows real timing (use this!)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN shows what the planner thinks will happen. EXPLAIN ANALYZE shows what actually happened. When they disagree, that's your clue. The planner is working with bad estimates.

Here's an annotated plan from a real query:

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1204.52..5765.83 rows=1248 width=164) (actual time=8.412..42.891 rows=1195 loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..4230.00 rows=50000 width=96) (actual time=0.015..18.234 rows=50000 loops=1)
         Filter: (created_at > '2025-01-01'::date)
         Rows Removed by Filter: 150000
   ->  Hash  (cost=1102.00..1102.00 rows=8202 width=68) (actual time=8.102..8.102 rows=8200 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 892kB
         ->  Seq Scan on customers c  (cost=0.00..1102.00 rows=8202 width=68) (actual time=0.012..4.521 rows=8200 loops=1)
               Filter: (region = 'us-west')
               Rows Removed by Filter: 41800
 Planning Time: 0.285 ms
 Execution Time: 43.124 ms

The key fields to understand:

cost (e.g., 0.00..4230.00) is startup cost and total cost in arbitrary planner units. The first number is the cost to return the first row, the second is the cost for all rows.
rows appears twice: the estimate in the first parenthetical and the actual count in the second. Big gaps usually mean stale statistics.
actual time is real milliseconds, time to first row and then total time.
loops is how many times the node ran. Multiply actual time × loops to get the real cost of that node.
Rows Removed by Filter counts rows Postgres read and then threw away. A high number means wasted work, and often a missing index.

Join Types in Plans

Postgres chooses between three join strategies:

-- Nested Loop: best for small inner tables or index-driven lookups
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 12345;

 Nested Loop  (cost=0.71..16.75 rows=1 width=42) (actual time=0.038..0.041 rows=1 loops=1)
   ->  Index Scan using orders_pkey on orders o  (cost=0.42..8.44 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=1)
         Index Cond: (id = 12345)
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.31 rows=1 width=42) (actual time=0.012..0.013 rows=1 loops=1)
         Index Cond: (id = o.customer_id)
 Planning Time: 0.195 ms
 Execution Time: 0.068 ms

A Nested Loop walks the outer rows and looks each one up in the inner table. It's fast when the inner side has an index or only a few rows.

A Hash Join builds a hash table from the smaller input and probes it with the larger one. It's the usual choice for joining big sets without a helpful index.

A Merge Join sorts both inputs and merges them. It shines when both sides are already sorted (say, from an index) or the join is very large.

Spotting Planner Misestimates

When "rows" (estimated) and "actual rows" differ by 10x or more, the planner is working with bad data:

 Index Scan using idx_orders_status on orders  (cost=0.42..85.10 rows=12 width=96) (actual time=0.025..145.882 rows=48500 loops=1)
   Index Cond: (status = 'pending')

The planner expected 12 rows but got 48,500. This usually means:

Statistics are stale. Run ANALYZE orders;
Columns are correlated in a way the planner can't model (extended statistics with CREATE STATISTICS can help)
A recently loaded batch that changed the data distribution

Understanding Scan Types

The scan type in your EXPLAIN output tells you how Postgres is reading the table. Each type has a sweet spot, and knowing when Postgres picks each one tells you whether you need to step in.

Sequential Scan (Seq Scan)

Reads the entire table from start to finish. This is the baseline: no index needed, no random I/O, just read everything.

EXPLAIN ANALYZE SELECT count(*) FROM events WHERE event_type = 'page_view';

 Aggregate  (cost=28450.00..28450.01 rows=1 width=8) (actual time=187.234..187.235 rows=1 loops=1)
   ->  Seq Scan on events  (cost=0.00..26200.00 rows=900000 width=0) (actual time=0.021..142.891 rows=892410 loops=1)
         Filter: (event_type = 'page_view')
         Rows Removed by Filter: 107590
 Execution Time: 187.301 ms

Seq Scan is the correct choice when:

The table is small (a few thousand rows)
You're reading most of the table anyway (low selectivity)
No useful index exists

Seq Scan is a problem when:

You're selecting a tiny fraction of a large table
It's in a hot path that runs thousands of times per second

Index Scan

Uses an index to find matching rows, then fetches full row data from the heap (table) pages.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 8842;

 Index Scan using idx_orders_customer_id on orders  (cost=0.42..52.18 rows=45 width=96) (actual time=0.028..0.185 rows=42 loops=1)
   Index Cond: (customer_id = 8842)
 Planning Time: 0.082 ms
 Execution Time: 0.214 ms

Index Scan involves two steps: traverse the index B-tree, then jump to heap pages. It's fast for highly selective queries but involves random I/O to the heap.

Index Only Scan

The best case. Postgres answers the query entirely from the index without touching the heap table at all.

-- With a covering index: CREATE INDEX idx_orders_cust_covering ON orders(customer_id) INCLUDE (status, total);
EXPLAIN ANALYZE SELECT status, total FROM orders WHERE customer_id = 8842;

 Index Only Scan using idx_orders_cust_covering on orders  (cost=0.42..48.12 rows=45 width=18) (actual time=0.022..0.078 rows=42 loops=1)
   Index Cond: (customer_id = 8842)
   Heap Fetches: 0
 Planning Time: 0.075 ms
 Execution Time: 0.102 ms

"Heap Fetches: 0" means zero trips to the table. This only works when:

Every column the query needs is in the index, either as a key column or via INCLUDE
The visibility map marks the pages as all-visible, which means VACUUM has run recently. If you see a high Heap Fetches count, check on vacuum.

Bitmap Index Scan + Bitmap Heap Scan

Bitmap scans sit between a seq scan and an index scan. Postgres picks them for medium selectivity: too many rows for an index scan (the random I/O adds up), but too few to justify reading the whole table.

EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31';

 Bitmap Heap Scan on orders  (cost=285.42..8924.15 rows=15200 width=96) (actual time=2.841..28.412 rows=14892 loops=1)
   Recheck Cond: ((created_at >= '2025-03-01') AND (created_at <= '2025-03-31'))
   Heap Blocks: exact=4218
   ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..281.62 rows=15200 width=0) (actual time=2.124..2.124 rows=14892 loops=1)
         Index Cond: ((created_at >= '2025-03-01') AND (created_at <= '2025-03-31'))
 Planning Time: 0.108 ms
 Execution Time: 29.891 ms

The two-phase approach:

The Bitmap Index Scan walks the index and builds an in-memory bitmap of which heap pages contain matching rows.
The Bitmap Heap Scan reads those pages in physical order, which turns random I/O into something much closer to sequential I/O.

Bitmap scans also let Postgres combine several single-column indexes with BitmapAnd/BitmapOr:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND region = 'eu-west';

 Bitmap Heap Scan on orders  (cost=412.85..2180.42 rows=820 width=96) (actual time=1.892..5.421 rows=795 loops=1)
   Recheck Cond: ((status = 'pending') AND (region = 'eu-west'))
   ->  BitmapAnd
         ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..205.12 rows=12500 width=0) (actual time=0.845..0.845 rows=12500 loops=1)
         ->  Bitmap Index Scan on idx_orders_region  (cost=0.00..198.42 rows=8200 width=0) (actual time=0.712..0.712 rows=8200 loops=1)
 Execution Time: 5.612 ms

When Does Postgres Choose Each?

The decision comes down to selectivity, meaning what fraction of the table you're reading:

Selectivity	Typical Scan	Reasoning
< 1 to 2% of rows	Index Scan	Few rows; random I/O is acceptable
2 to 15% of rows	Bitmap Scan	Too many for index, too few for seq scan
> 15 to 20% of rows	Seq Scan	Sequential I/O wins for large fractions
All indexed columns available	Index Only Scan	Avoids heap entirely

Treat these as rough numbers. They shift with table size, physical correlation, random_page_cost, and how much of the data is cached.

Memory and I/O Configuration

Postgres ships with very conservative defaults (shared_buffers is 128 MB out of the box) so it can start on almost any machine. If you run your own Postgres and haven't touched these settings, you're probably leaving a lot of performance on the table. A handful of parameters cover most of what matters.

shared_buffers

This is Postgres's own cache, the pool of 8KB pages kept in shared memory. Every read checks here first before going to the OS page cache or disk.

Start at 25% of total RAM. Going much higher rarely helps, since the OS page cache is doing useful work with the rest.

-- Check current setting
SHOW shared_buffers;

-- Recommended: 25% of RAM
-- 16 GB server → set shared_buffers = '4GB'
-- 64 GB server → set shared_buffers = '16GB'

effective_cache_size

This doesn't allocate anything. It tells the planner how much data is likely cached between shared_buffers and the OS page cache. A higher value makes the planner more willing to choose index scans (since it expects the data is already in memory).

Set it to 50 to 75% of total RAM.

SHOW effective_cache_size;
-- 64 GB server → set effective_cache_size = '48GB'

work_mem

Memory available for each sort, hash, or merge operation within a query. A single complex query might use work_mem multiple times (once per sort node, once per hash join). Set this too high and a burst of concurrent queries can exhaust RAM.

Start at 16 to 64 MB globally and raise it per session for analytical queries.

-- Check current setting
SHOW work_mem;

-- For an analytical session that needs large sorts:
SET work_mem = '256MB';
-- Run your big query...
RESET work_mem;

Signs that work_mem is too low: you see "Sort Method: external merge Disk" or "Batches: 4" (multiple batches) in hash joins in your EXPLAIN ANALYZE output.

maintenance_work_mem

Used by VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Far fewer of these run at once than regular queries, so you can set it much higher than work_mem. Keep in mind each autovacuum worker can use this much too, unless you set autovacuum_work_mem separately.

SHOW maintenance_work_mem;
-- Set to 1 to 2 GB for faster index builds and vacuums
-- ALTER SYSTEM SET maintenance_work_mem = '1GB';

random_page_cost

The planner's estimate of how expensive a random page read is relative to a sequential read. The default of 4.0 assumes spinning disks. On SSDs, random reads are nearly as fast as sequential reads.

On SSDs, set it to 1.1 to 1.5. This makes the planner more willing to use index scans, which is usually what you want.

Asynchronous I/O in Postgres 18

Postgres 18 added asynchronous I/O, controlled by io_method. The default is worker, which uses background I/O workers to issue reads ahead of time for sequential scans, bitmap heap scans, and vacuum. On Linux you can also try io_uring. Most of the time the default is fine. Set it and move on.

SHOW io_method;
SHOW io_workers;

SHOW random_page_cost;
-- ALTER SYSTEM SET random_page_cost = 1.1;

Monitoring Your Cache Hit Ratio

This is the first number we look at for memory tuning. For a typical application workload you want the cache hit ratio above 99%:

SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit) AS heap_hit,
  round(
    sum(heap_blks_hit)::numeric /
    nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2
  ) AS cache_hit_ratio
FROM pg_statio_user_tables;

 heap_read | heap_hit   | cache_hit_ratio
-----------+------------+-----------------
    285412 | 89421058   |           99.68

If your cache hit ratio drops below 99%, you're hitting disk too often. Either increase shared_buffers or reduce working set size (better indexes, partitioning, archiving old data).

Checkpoint Tuning

Checkpoints flush dirty pages from shared_buffers to disk. Poorly configured checkpoints cause I/O spikes that stall queries.

-- Check current checkpoint settings
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('max_wal_size', 'checkpoint_completion_target', 'checkpoint_timeout');

           name            | setting | unit
---------------------------+---------+------
 checkpoint_completion_target | 0.9    |
 checkpoint_timeout           | 300    | s
 max_wal_size                 | 2048   | MB

Key principles: max_wal_size controls how much WAL can pile up before Postgres forces a checkpoint. Raising it means fewer checkpoints and smoother I/O. For write-heavy workloads, 4 to 16 GB is common.

checkpoint_completion_target spreads checkpoint writes over that fraction of the interval. The default of 0.9 is good. Leave it alone.

Understanding Your Workload Profile

Before tuning, figure out whether you're read-heavy or write-heavy, because the strategies differ:

SELECT
  datname,
  tup_returned + tup_fetched AS reads,
  tup_inserted + tup_updated + tup_deleted AS writes,
  round(
    (tup_returned + tup_fetched)::numeric /
    nullif(tup_inserted + tup_updated + tup_deleted, 1), 1
  ) AS read_write_ratio
FROM pg_stat_database
WHERE datname = current_database();

  datname  |   reads    |  writes  | read_write_ratio
-----------+------------+----------+------------------
 myapp     | 284521000  | 28120000 |             10.1

A 10:1 ratio is starting to get write-heavy. For context:

Read-heavy (100:1 and up): focus on caching, covering indexes, and read replicas
Balanced (10:1 to 100:1): focus on shared_buffers, indexes, and connection pooling
Write-heavy (under 10:1): focus on WAL and checkpoint tuning, fillfactor, and HOT updates

HOT Updates and Fill Factor

When Postgres updates a row, it normally must update every index on the table (because the new row version lives at a new heap location). HOT (Heap Only Tuple) updates skip that by putting the new version on the same heap page. Two conditions: there has to be free space on the page, and the update can't change any indexed column.

-- Reserve 20% of each page for HOT updates on a frequently-updated table
ALTER TABLE sessions SET (fillfactor = 80);

-- Fillfactor only applies to newly written pages. To apply it to existing data,
-- rewrite the table (VACUUM FULL takes an exclusive lock; pg_repack avoids that)
-- or just wait for natural turnover.
VACUUM FULL sessions;

Monitor HOT update effectiveness:

SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  round(n_tup_hot_upd::numeric / nullif(n_tup_upd, 0) * 100, 1) AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC;

   relname   | n_tup_upd | n_tup_hot_upd | hot_update_pct
-------------+-----------+---------------+----------------
 sessions    |    892410 |        845100 |           94.7
 orders      |    245000 |         42100 |           17.2
 user_prefs  |     85200 |         81400 |           95.6

The orders table at 17.2% HOT updates is worth a look. Either the updates touch an indexed column (fillfactor won't help there, so check whether that index is really needed) or the pages are full and a lower fillfactor will help.

On Snowflake Postgres, memory settings like shared_buffers are sized for your instance, so you can spend your time on queries and indexes instead. Check the Snowflake Postgres docs for which parameters you can change yourself.

pg_stat_statements and Query Analysis

If you only install one Postgres extension for performance, make it pg_stat_statements. It tracks execution statistics for every normalized query: call count, total and mean time, rows returned, and buffer usage. This is how you find the queries that actually matter.

Enabling pg_stat_statements

-- Requires adding to shared_preload_libraries (needs restart).
-- Careful: this overwrites the list, so include anything already in it.
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- After restart:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Finding Your Most Expensive Queries

The queries that consume the most total execution time are your highest-impact optimization targets:

SELECT
  substring(query, 1, 80) AS short_query,
  calls,
  round(total_exec_time::numeric, 1) AS total_time_ms,
  round(mean_exec_time::numeric, 1) AS mean_time_ms,
  rows,
  round((shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0)) * 100, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

                    short_query                     |  calls  | total_time_ms | mean_time_ms |   rows   | cache_hit_pct
----------------------------------------------------+---------+---------------+--------------+----------+---------------
 SELECT o.*, c.name FROM orders o JOIN customers c  |  284521 |     892451.2  |         3.1  | 14218050 |          99.8
 UPDATE sessions SET last_active = $1 WHERE id = $2 | 1842100 |     645210.8  |         0.4  |  1842100 |          99.9
 SELECT * FROM events WHERE user_id = $1 AND crea   |   42810 |     428100.5  |        10.0  |  4185200 |          87.2
 INSERT INTO audit_log (action, user_id, details)   |  892410 |     215420.1  |         0.2  |   892410 |          99.9
 SELECT count(*) FROM orders WHERE status = $1      |   28450 |     185210.4  |         6.5  |    28450 |          92.1

Look at that UPDATE sessions query. It's cheap on its own (0.4ms) but runs 1.8 million times, which makes it the second most expensive query overall. The events query, with an 87.2% cache hit rate, is pulling a lot from disk.

Total Time vs Mean Time

Don't just look at mean time. A query running 0.5ms × 2 million calls consumes more resources than one running 500ms × 100 calls:

-- Frequent but individually fast (death by a thousand cuts)
SELECT
  substring(query, 1, 60) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric / 1000, 1) AS total_seconds
FROM pg_stat_statements
WHERE calls > 10000
ORDER BY total_exec_time DESC
LIMIT 5;

-- Infrequent but individually slow (long-running reports)
SELECT
  substring(query, 1, 60) AS query,
  calls,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  round(total_exec_time::numeric / 1000, 1) AS total_seconds
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC
LIMIT 5;

Both need attention, just different kinds. High-frequency queries benefit from micro-optimizations (covering indexes, connection pooling), while slow queries need plan analysis.

Buffer Hit Ratio Per Query

The per-query buffer hit ratio reveals which specific queries are causing disk reads:

SELECT
  substring(query, 1, 60) AS query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(
    shared_blks_hit::numeric /
    nullif(shared_blks_hit + shared_blks_read, 0) * 100, 1
  ) AS buffer_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 5;

Queries with buffer hit rates below 95% are candidates for:

Better indexes to reduce data scanned
Increased shared_buffers if the working set doesn't fit
Query rewrites to access less data

Resetting Statistics

Stats are cumulative since the last reset, so reset now and then to get a fresh picture:

-- Reset all pg_stat_statements data
SELECT pg_stat_statements_reset();

-- Check when pg_stat_statements was last reset (Postgres 14+)
SELECT stats_reset FROM pg_stat_statements_info;

We like to reset after deployments or configuration changes so the impact is easy to see.

Parallel Queries

Postgres can split expensive operations across multiple CPU cores. When a single query needs to read millions of rows, parallel execution can cut wall-clock time a lot. It's not magic, though, and plenty of queries don't benefit.

When Postgres Goes Parallel

The planner considers parallel execution for:

Large sequential scans, plus parallel index and bitmap heap scans
Hash, merge, and nested loop joins over large inputs
Aggregates like COUNT, SUM, and AVG over big tables
Some index builds (CREATE INDEX on B-tree, and BRIN/GIN in newer versions)

EXPLAIN ANALYZE SELECT count(*), avg(total) FROM orders WHERE created_at > '2025-01-01';

 Finalize Aggregate  (cost=15842.52..15842.53 rows=1 width=40) (actual time=48.125..50.412 rows=1 loops=1)
   ->  Gather  (cost=15842.10..15842.51 rows=2 width=40) (actual time=45.812..50.385 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=14842.10..14842.11 rows=1 width=40) (actual time=42.125..42.126 rows=1 loops=3)
               ->  Parallel Seq Scan on orders  (cost=0.00..13592.10 rows=250000 width=8) (actual time=0.024..28.412 rows=166420 loops=3)
                     Filter: (created_at > '2025-01-01'::date)
                     Rows Removed by Filter: 33580
 Planning Time: 0.125 ms
 Execution Time: 50.512 ms

Key indicators: "Workers Planned: 2", "Workers Launched: 2", and "loops=3" (the leader process plus 2 workers). The "Parallel Seq Scan" distributes the table scan across all workers.

Configuration Parameters

-- Maximum parallel workers available system-wide
SHOW max_parallel_workers;           -- Default: 8

-- Maximum workers per single query operation (Gather node)
SHOW max_parallel_workers_per_gather; -- Default: 2

-- Minimum table size before parallel is considered
SHOW min_parallel_table_scan_size;   -- Default: 8MB

-- Cost thresholds that affect planner decisions
SHOW parallel_setup_cost;            -- Default: 1000
SHOW parallel_tuple_cost;            -- Default: 0.1

To allow more parallelism for analytical workloads:

-- Allow up to 4 workers per query for heavy analytical work
SET max_parallel_workers_per_gather = 4;

EXPLAIN ANALYZE
SELECT region, count(*), avg(total)
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY region;

 Finalize GroupAggregate  (cost=18425.12..18512.84 rows=12 width=48) (actual time=32.412..32.845 rows=12 loops=1)
   Group Key: region
   ->  Gather Merge  (cost=18425.12..18510.84 rows=48 width=48) (actual time=32.125..32.712 rows=60 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Sort  (cost=17425.06..17425.09 rows=12 width=48) (actual time=28.412..28.415 rows=12 loops=5)
               Sort Key: region
               ->  Partial HashAggregate  (cost=17424.50..17424.62 rows=12 width=48) (actual time=28.125..28.132 rows=12 loops=5)
                     ->  Parallel Seq Scan on orders  (cost=0.00..14925.00 rows=499900 width=16) (actual time=0.018..15.421 rows=399920 loops=5)
 Execution Time: 33.012 ms

With 4 workers (plus the leader) splitting the scan, this 2-million-row aggregation finishes in 33ms. Single-threaded it would take several times longer.

When Parallel Doesn't Help

Not everything benefits from parallelism:

-- Index lookups on a single row are already fast; parallel doesn't help
EXPLAIN SELECT * FROM orders WHERE id = 12345;
-- Result: plain Index Scan, no parallelism (correct!)

-- Small tables don't trigger parallel (below min_parallel_table_scan_size)
EXPLAIN SELECT count(*) FROM status_codes;
-- Result: plain Seq Scan, no parallelism (table is tiny)

Parallel execution is also avoided when:

The query calls a function marked PARALLEL UNSAFE
The query writes data or locks rows (FOR UPDATE/FOR SHARE). CREATE TABLE AS and REFRESH MATERIALIZED VIEW can still use parallelism for the read side.
The query runs inside a cursor
The table is below min_parallel_table_scan_size
Workers aren't available (all max_parallel_workers slots are in use)

Parallel-Safe vs Parallel-Unsafe Functions

Custom functions must be marked as parallel-safe for the planner to parallelize queries that use them:

-- This function CAN run in parallel workers
CREATE FUNCTION calculate_discount(price numeric, tier text)
RETURNS numeric
LANGUAGE sql
PARALLEL SAFE
AS $$
  SELECT CASE tier
    WHEN 'gold' THEN price * 0.20
    WHEN 'silver' THEN price * 0.10
    ELSE price * 0.05
  END;
$$;

-- This function CANNOT (it writes data)
CREATE FUNCTION log_access(user_id int)
RETURNS void
LANGUAGE sql
PARALLEL UNSAFE
AS $$
  INSERT INTO access_log(user_id, accessed_at) VALUES (user_id, now());
$$;

If a query uses a PARALLEL UNSAFE function, the whole query runs single-threaded, even the parts that don't touch that function. Functions default to PARALLEL UNSAFE when you don't specify, so it's worth marking the safe ones.

Conclusion

Performance work is a loop. Measure with EXPLAIN ANALYZE and pg_stat_statements, find the bottleneck (a bad plan, a missing index, not enough memory, or I/O), fix it, and measure again. Don't try to tune everything at once. Start with the top query in pg_stat_statements, read its plan, and work from there.

Related Resources

Crunchy Data Blog: Postgres Scan Types in EXPLAIN Plans

Visual guide to understanding scan types and when they appear.

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

Understand your workload profile to guide tuning decisions.

Snowflake Engineering Blog: Postgres JSONB and TOAST Performance

Performance implications of large JSONB columns and TOAST.

Crunchy Data Blog: HOT Updates and Fill Factor

Reduce index maintenance overhead with HOT updates.

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