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

These are short, actionable tips collected from the Crunchy Data team's years of running Postgres in production. Each one is something you can try in your database right now.

Prevent Lock Cascades with lock_timeout

A single DDL statement waiting on a lock can block every other query on that table. Set a system-wide lock_timeout so statements fail fast rather than pile up:

-- System-wide: give up waiting for a lock after 10 seconds
ALTER SYSTEM SET lock_timeout = '10s';
SELECT pg_reload_conf();

Override it per-session when you need more time for migrations:

-- For a specific migration session
SET lock_timeout = '5min';
ALTER TABLE large_table ADD COLUMN new_col TEXT;

This prevents the dreaded "one blocked DDL takes down the whole app" scenario.

Use EXCLUDE Constraints for Double-Booking Prevention

Postgres can enforce "no overlapping ranges" at the data level — preventing double-bookings for rooms, equipment, people, or any schedulable resource:

-- Enable the btree_gist extension (needed for mixing = and range operators)
CREATE EXTENSION btree_gist;

-- Create a table that prevents overlapping bookings per resource
CREATE TABLE booking (
  booking_id SERIAL PRIMARY KEY,
  resource_id INT NOT NULL,
  during TSTZRANGE NOT NULL,
  EXCLUDE USING gist (
    resource_id WITH =,
    during WITH &&
  )
);

Now any overlapping insert fails:

-- Book a room from 2pm-4pm
INSERT INTO booking (resource_id, during)
VALUES (1, '[2026-06-18 14:00, 2026-06-18 16:00)');

-- Try to double-book — fails automatically
INSERT INTO booking (resource_id, during)
VALUES (1, '[2026-06-18 15:00, 2026-06-18 17:00)');
-- ERROR: conflicting key value violates exclusion constraint

Push collision logic into the database rather than relying on application code across multiple services.

generate_series for Gap-Free Date Reports

If you GROUP BY date and some dates have no data, your chart has gaps. Use generate_series to create a continuous date spine and LEFT JOIN your data onto it:

-- Generate every day in a range, even if no events occurred
WITH date_spine AS (
  SELECT generate_series(
    '2026-01-01'::date,
    '2026-01-31'::date,
    '1 day'::interval
  )::date AS day
)
SELECT
  ds.day,
  COALESCE(count(s.id), 0) AS signups
FROM date_spine ds
LEFT JOIN signups s ON s.created_at::date = ds.day
GROUP BY ds.day
ORDER BY ds.day;

This works for any interval — weeks, months, hours:

-- Weekly intervals
SELECT generate_series(
  '2026-01-01'::date,
  '2026-03-31'::date,
  '1 week'::interval
) AS week_start;

When to Add Connection Pooling: The Rule of Thumb

Query your connection states. If you have more idle connections than active ones, it's time for a pooler:

SELECT state, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY connections DESC;

      state       | connections
------------------+-------------
 idle             |          69
 idle in transaction |      26
 active           |           7

If idle connections outnumber active ones (especially by 5x+), connection pooling will dramatically reduce your memory footprint and improve performance.

Never Set max_connections to an Absurd Number

It's tempting to just set max_connections = 10000 and never think about it again. But each connection is an OS process consuming 5-10 MB of memory and competing for CPU time. At 10,000 connections, that's 50-100 GB of overhead before you've done any actual work.

Instead:

-- Check your actual peak usage
SELECT max(count) FROM (
  SELECT count(*) FROM pg_stat_activity
) t;

Set max_connections to 2-3x your actual peak, then use PgBouncer for the application layer. A typical setup: PgBouncer handles 1000 app connections, multiplexing them down to 20-50 actual Postgres backends.

Reset pg_stat_statements Periodically

pg_stat_statements accumulates query statistics over time. If you deployed a fix for a slow query last month, the old stats still dominate the totals. Reset periodically to keep your performance picture current:

-- See when stats were last reset
SELECT stats_reset FROM pg_stat_statements_info;

-- Reset all statistics
SELECT pg_stat_statements_reset();

A good cadence: reset after major deployments, or weekly if you're actively tuning queries.

Check Transaction Volume at a Glance

Quick health check — are commits and rollbacks in a healthy ratio?

SELECT
  datname,
  xact_commit,
  xact_rollback,
  round(xact_rollback::numeric / NULLIF(xact_commit, 0) * 100, 2) AS rollback_pct
FROM pg_stat_database
WHERE datname NOT LIKE 'template%'
ORDER BY xact_commit DESC;

If rollback_pct is above 5%, investigate — it usually means application errors, constraint violations, or lock conflicts are causing retries.

Use TIMESTAMPTZ, Not TIMESTAMP

This is simple but catches people constantly. TIMESTAMP stores a date and time with no timezone context. TIMESTAMPTZ stores in UTC and converts on display based on the session timezone.

-- WRONG: ambiguous, breaks when servers are in different timezones
CREATE TABLE events (event_time TIMESTAMP);

-- RIGHT: always stores UTC, displays in session timezone
CREATE TABLE events (event_time TIMESTAMPTZ DEFAULT now());

-- See the difference
SET timezone = 'America/New_York';
SELECT now()::timestamp;   -- 2026-06-18 12:30:00 (no tz info!)
SELECT now()::timestamptz; -- 2026-06-18 12:30:00-04 (knows it's EDT)

Always use TIMESTAMPTZ. The only exception is when you're storing a "wall clock" time that has no timezone meaning (like "store opens at 9:00 AM" regardless of DST).

NUMERIC for Money, Never FLOAT or MONEY

-- WRONG: floating point loses precision
price FLOAT  -- 19.99 might become 19.989999999...

-- WRONG: locale-dependent, poor portability
price MONEY  -- Formats depend on lc_monetary setting

-- RIGHT: exact decimal arithmetic
price NUMERIC(10,2)  -- Exactly what you expect, always

NUMERIC gives you exact arithmetic with no rounding surprises. The performance difference is negligible for typical workloads.

Conclusion

These tips come from real production experience — the kind of knowledge that saves you a 3am page or a subtle bug that ships to production. Postgres is full of small decisions that compound over time; getting the basics right from the start pays dividends.

Related Resources

Crunchy Data on X

Follow for daily Postgres tips, announcements, and community content.

Craig Kerstiens on X

Deep dives on Postgres features, extensions, and the ecosystem.

Crunchy Data Blog

Long-form Postgres content from the team behind Snowflake Postgres.

Postgres Developer Hub — All Topics

The full topic guide for learning and operating Postgres.

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