Postgres Quick Tips
These are short tips we've collected from years of running Postgres in production at Crunchy Data and now on the Snowflake Postgres team. Each one is something you can try in your database today.
Prevent Lock Cascades with lock_timeout
A single DDL statement waiting on a lock can block every other query on that table. Set a lock_timeout so statements fail fast instead of piling up behind each other. You can set it system-wide:
-- System-wide: give up waiting for a lock after 10 seconds ALTER SYSTEM SET lock_timeout = '10s'; SELECT pg_reload_conf();
A system-wide value also applies to your regular queries, so pick something comfortably above any lock wait you'd expect in normal traffic. Many teams set it only for the role that runs migrations instead. Override it per session when a migration needs more time:
-- For a specific migration session SET lock_timeout = '5min'; ALTER TABLE large_table ADD COLUMN new_col TEXT;
That's how you avoid the "one blocked DDL takes down the whole app" outage. If the migration times out, retry it later.
Use EXCLUDE Constraints for Double-Booking Prevention
Postgres can enforce "no overlapping ranges" in the database itself, which prevents double-bookings for rooms, equipment, people, or anything else you schedule:
-- 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 to 4pm INSERT INTO booking (resource_id, during) VALUES (1, '[2026-06-18 14:00, 2026-06-18 16:00)'); -- Try to double-book: this fails 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
Put collision logic in the database instead of trusting every service to get it right in application code. On Postgres 18 you can also declare this kind of rule as a temporal primary key or unique constraint with WITHOUT OVERLAPS, which also uses a GiST index under the hood.
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, like weeks, months, or 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 or more), a pooler like PgBouncer will cut your memory footprint and usually improve performance. Watch the idle in transaction count too. Those sessions hold locks and block vacuum, so track them down in your app.
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 a separate OS process that uses memory (often several MB, more with big work_mem queries or lots of cached catalog data) and competes for CPU. At 10,000 connections, that overhead adds up to tens of GB before you've done any real work.
Instead:
-- Check how many connections you have right now SELECT max(count) FROM ( SELECT count(*) FROM pg_stat_activity ) t;
That's a point-in-time count, so run it during your busiest hours (or track it in your monitoring) to find the real peak. Set max_connections to 2 to 3x that peak, then put PgBouncer in front of the application. A typical setup: PgBouncer accepts 1,000 app connections and multiplexes them down to 20 to 50 actual Postgres backends.
Reset pg_stat_statements Periodically
pg_stat_statements accumulates query statistics until you reset it. If you fixed a slow query last month, the old stats still dominate the totals. Reset periodically to keep your picture current:
-- See when stats were last reset (Postgres 14+) 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
A quick health check on the ratio of commits to rollbacks:
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 an absolute point in time (internally 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: stores an absolute instant, 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
Every one of these came out of a real production incident or a bug someone shipped. Most are a one-line change, so pick one and try it this week.
Related Resources
Follow for daily Postgres tips, announcements, and community content.
Deep dives on Postgres features, extensions, and the ecosystem.
Long-form Postgres content from the team behind Snowflake Postgres.
The full topic guide for learning and operating Postgres.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances