From postgresql-cookbook
PostgreSQL patterns and best practices cookbook. Use this skill when working with PostgreSQL databases to reference proven patterns for database design, queries, and operations.
How this skill is triggered — by the user, by Claude, or both
Slash command
/postgresql-cookbook:postgresql-cookbookThis skill is limited to the following tools:
The summary Claude sees in its skill listing — used to decide when to auto-load this skill
This cookbook provides proven PostgreSQL patterns and best practices for common database operations.
This cookbook provides proven PostgreSQL patterns and best practices for common database operations.
SQL is less expressive than many programming languages, so comments may be more acceptable than in application code. However, they should still be used judiciously and never as a substitute for clear code.
Comments may be useful in:
Never use comments when:
Good (explains non-obvious why):
-- Using LEFT JOIN instead of NOT EXISTS because the query planner
-- generates a more efficient plan for this table size (see issue #234)
SELECT u.user_id, u.email
FROM users u
LEFT JOIN deleted_users d ON u.user_id = d.user_id
WHERE d.user_id IS NULL;
CREATE OR REPLACE FUNCTION process_user_orders(p_user_id BIGINT)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
-- Must lock user record first to prevent race condition with concurrent
-- order updates (documented in issue #456)
PERFORM * FROM users WHERE user_id = p_user_id FOR UPDATE;
UPDATE orders
SET processed = true
WHERE user_id = p_user_id;
END;
$$;
Bad (stating the obvious):
-- Select users
SELECT * FROM users;
-- Join orders with users
SELECT u.email, o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
-- ---------------------------------
-- Process Orders Section
-- ---------------------------------
UPDATE orders SET status = 'processed';
Better (no comments needed for simple queries):
SELECT * FROM users;
SELECT u.email, o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
UPDATE orders SET status = 'processed';
Always use snake_case for table names, column names, and all SQL identifiers. This is the de facto standard in the PostgreSQL ecosystem and prevents issues with case sensitivity and identifier quoting.
Good - snake_case:
CREATE TABLE user_account (
user_id BIGSERIAL PRIMARY KEY,
email_address TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_login_at TIMESTAMPTZ
);
CREATE INDEX idx_user_account_email ON user_account(email_address);
SELECT user_id, email_address, created_at
FROM user_account
WHERE last_login_at > NOW() - INTERVAL '30 days';
Bad - camelCase (requires quoting):
-- DON'T DO THIS
CREATE TABLE "userAccount" (
"userId" BIGSERIAL PRIMARY KEY,
"emailAddress" TEXT NOT NULL,
"createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Every query requires quotes
SELECT "userId", "emailAddress"
FROM "userAccount"
WHERE "createdAt" > NOW();
user_account, order_item)user_id, email_address, created_at)idx_ followed by table and columns (e.g., idx_user_email)cleanup_old_logs, process_pending_jobs)Stored procedures (functions) in PostgreSQL can be useful for specific use cases, but should be used judiciously. They are acceptable when kept small and focused on database operations, but should never contain business logic.
Stored procedures are appropriate for:
Use pg_cron to automatically delete old records on a schedule. This is useful for cleaning up audit logs, temporary data, or any time-based records without manual intervention or external cron jobs.
Install and enable the pg_cron extension (requires superuser privileges):
CREATE EXTENSION IF NOT EXISTS pg_cron;
Create a stored procedure for the deletion logic:
CREATE OR REPLACE FUNCTION cleanup_old_audit_logs()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT 10000;
RAISE NOTICE 'Deleted old audit logs at %', NOW();
END;
$$;
Schedule the job to run daily:
SELECT cron.schedule(
'delete-old-audit-logs',
'0 3 * * *', -- 3 AM daily
'SELECT cleanup_old_audit_logs()'
);
cron.job_run_details for failuresCREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
Use SELECT FOR UPDATE SKIP LOCKED to allow multiple service instances to safely compete for work items from a shared queue without blocking each other or processing the same item twice.
-- Worker process claims the next available job
UPDATE jobs
SET
status = 'processing',
started_at = NOW(),
worker_id = 'worker-123',
attempts = attempts + 1
WHERE id = (
SELECT id
FROM jobs
WHERE status = 'pending'
AND attempts < 10
ORDER BY created_at ASC
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;
FOR UPDATE: Locks the selected row(s) for updateSKIP LOCKED: Skips rows that are already locked by other transactions, preventing workers from waitingLIMIT 1: Each worker claims exactly one jobORDER BY: Ensures FIFO processing (oldest jobs first)attempts < 10: Excludes jobs that have failed too many timesWhen selecting across multiple tables (e.g., joins), use FOR UPDATE OF table_name to explicitly specify which table's rows to lock:
UPDATE jobs
SET
status = 'processing',
started_at = NOW(),
attempts = attempts + 1
WHERE id = (
SELECT j.id
FROM jobs j
JOIN job_metadata m ON j.id = m.job_id
WHERE j.status = 'pending'
AND j.attempts < 10
ORDER BY j.created_at ASC
FOR UPDATE OF j SKIP LOCKED
LIMIT 1
)
RETURNING *;
Without OF table_name, PostgreSQL may lock rows from all joined tables, potentially causing race conditions where multiple workers claim the same job.
CREATE INDEX idx_jobs_pending ON jobs(status, created_at) WHERE status = 'pending';
FOR UPDATE OF table_name when joining tablesUse advisory locks to control concurrency when you don't have a specific database row to lock. Advisory locks use arbitrary numbers as lock identifiers, making them ideal for serializing operations per user, resource, or any other logical grouping.
-- Lock using a numeric user ID
SELECT pg_advisory_xact_lock(user_id);
-- Perform operations knowing only one transaction can proceed for this user
INSERT INTO user_actions (user_id, action, created_at)
VALUES (user_id, 'some_action', NOW());
-- Lock is automatically released at transaction end
For non-numeric identifiers (like email addresses), hash them to get a number:
-- Lock using a hashed email address
SELECT pg_advisory_xact_lock(hashtext('[email protected]'));
-- Perform operations
-- ...
pg_advisory_xact_lock(key): Lock is automatically released when the transaction ends (recommended)pg_advisory_lock(key): Lock persists until explicitly released with pg_advisory_unlock(key) or session endsAlways prefer transaction-level locks (pg_advisory_xact_lock) to avoid accidentally holding locks indefinitely.
pg_advisory_xact_lock over pg_advisory_lock for automatic cleanupProper indexing is critical for query performance. Add indexes on columns that are frequently queried, use partial indexes to reduce index size, and create indexes concurrently on production systems to avoid blocking.
Single column indexes for fields used in WHERE clauses:
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
Partial indexes to exclude irrelevant rows and reduce index size:
-- Exclude soft-deleted rows
CREATE INDEX CONCURRENTLY idx_users_active
ON users(email)
WHERE deleted_at IS NULL;
-- Exclude completed jobs to avoid index updates when workers claim jobs
CREATE INDEX CONCURRENTLY idx_jobs_active
ON jobs(status, created_at)
WHERE status <> 'completed';
When a worker claims a job and changes status from 'pending' to 'processing', using status <> 'completed' avoids an index update since both statuses remain in the index. This is more efficient than status = 'pending' which would require index maintenance on every status change.
Compound indexes for specific query performance. Put the most general column first so it can be used independently:
-- Can be used for queries filtering on status alone, or status + created_at
CREATE INDEX CONCURRENTLY idx_jobs_status_created
ON jobs(status, created_at);
-- Example queries that benefit:
-- SELECT * FROM jobs WHERE status = 'pending'; -- Uses index
-- SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at; -- Uses index
Always use CONCURRENTLY when adding indexes to large tables in production:
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
Why it matters:
CONCURRENTLY: Index creation locks the table, blocking all writesCONCURRENTLY: Index builds in background, allowing normal operationsTrade-offs:
pg_stat_user_indexes to identify unused indexesPostgreSQL can emit NOTICE, WARNING, and other messages during query execution. Integrate these notices with your application's logging system so database-level events are visible alongside application logs. This is particularly useful for debugging triggers, functions, and constraints.
// src/Database.ts
import { Pool } from 'pg';
export default class Database {
private pool: Pool;
private severityMap: Record<string, "debug" | "info" | "warn" | "error" | "fatal"> = {
DEBUG: "debug",
LOG: "debug",
INFO: "info",
NOTICE: "info",
WARNING: "warn",
ERROR: "error",
FATAL: "fatal",
PANIC: "fatal",
};
constructor(config: any) {
this.pool = new Pool(config);
// Listen for errors on idle clients
this.pool.on("error", (error) => {
logger.error("Database pool error", error);
});
// Attach notice handler when new clients connect
this.pool.on("connect", (client) => {
client.on("notice", (notice) => {
const { message = "Database notice", severity = "NOTICE", ...params } = notice;
const logLevel = this.severityMap[severity?.toUpperCase()] || "info";
logger[logLevel](message, params);
});
});
}
}
Enable SQL query logging to see exactly what queries PostgreSQL executes. Combine this with RAISE NOTICE in tests to correlate test execution with database queries, making it easier to debug test failures and understand query performance.
Configure logging via connection options:
const pool = new Pool({
host: 'localhost',
database: 'myapp_test',
user: 'test_user',
password: 'test_password',
options: '-c log_statement=all -c client_min_messages=notice'
});
Add test context to database logs using RAISE NOTICE in test hooks:
import { describe, it, beforeEach } from 'node:test';
import Database from '../src/Database';
describe("UserService", () => {
let database: Database;
beforeEach(async (ctx) => {
// Log the current test name to the database
await database.query(
`DO $$ BEGIN RAISE NOTICE 'Starting test: %', $1; END $$`,
[ctx.name]
);
// Clean up test data
await database.nuke();
});
it("creates a new user", async () => {
// Test implementation
// All SQL queries will be logged with test context above
});
});
PostgreSQL logs will show:
NOTICE: Starting test: creates a new user
NOTICE: Nuked tables: users, orders, products
LOG: statement: INSERT INTO users (name, email) VALUES ('John', '[email protected]')
LOG: statement: SELECT * FROM users WHERE email = '[email protected]'
This makes it easy to see which queries correspond to which tests, invaluable for debugging test failures or optimizing slow tests.
ctx.name to identify which test is runninglog_statement=all in development/test environments (very verbose)npx claudepluginhub cressie176/cressie176-claude-marketplace --plugin postgresql-cookbookProvides PostgreSQL patterns for query optimization, schema design, indexing, security, RLS, UPSERT, pagination, and anti-pattern detection based on Supabase practices. Useful for SQL queries, migrations, slow query troubleshooting, and connection pooling.
Reference for PostgreSQL query optimization, schema design, indexing strategies, and Row Level Security. Based on Supabase best practices.
Guides PostgreSQL operations: schema design, index selection (B-tree/GIN/GiST/BRIN), query tuning (EXPLAIN ANALYZE), backups (pg_dump/pg_basebackup), replication, vacuum tuning, monitoring (pg_stat_statements), RLS, extensions (PostGIS/timescaledb).