🛠️ The Founder's Guide to Postgres Survival

By Alexander Belanger, Co-Founder of Hatchet
Published: 2026-07-22

"I’ve spent the last six months distilling two years of hard-won Postgres battles into an internal guide for our engineers. While the official Postgres manual is an incredible resource, it's often too comprehensive overwhelming when your production database is actually crashing."

This guide is designed for those who understand the basics of SQL (rows, tables, and the general concept of indexing). If you are relying entirely on AI like Claude to write your queries, you might find this tedious—though I highly recommend checking out supabase/agent-skills to level up.

🔌 A Word on ORMs

These tips apply regardless of your tool, but you may need to translate them into your specific ORM. Be warned: many high-scale optimizations are impossible through an ORM abstraction. To truly optimize, you must be able to write raw SQL.

• Recommended for Go: sqlc (used at Hatchet).
• Recommended for TypeScript: Prisma TypedSQL or similar tools.

---

🗺️ Table of Contents

1. The Fundamentals: Schemas, Reads, and Writes
2. Intermediate Concepts: The Planner, Bulk Updates, and Autovacuum
3. Advanced Tactics: FOR UPDATE SKIP LOCKED and Partitioning

---

🟢 The Fundamentals: Good Reads, Writes, and Schemas

🏗️ Designing a Robust Schema

Schemas are the most rigid part of your infrastructure; changing them post-deployment is painful. I suggest an iterative approach: build a rough draft of tables and primary keys, then refine them based on the actual queries your app requires.

The Great Debate: Normalization vs. Flexibility While database normalization (1NF, 2NF, 3NF) is the academic standard, it can sometimes hinder speed and developer velocity.

My Schema Checklist:

• Use Identity Columns (auto-incrementing ints) or UUIDs for PKs.
• Always utilize timestamptz for timestamps.
• Every table must have a Primary Key.
• Use Foreign Keys with cascading deletes for low-volume tables to ensure consistency.

🔍 Optimizing Read Queries

To understand Postgres performance, use this mental model: the database either finds a specific row instantly or it reads the entire table.

The Index vs. The Seq Scan

Postgres finds rows quickly via:

1. Explicit indexes.
2. Unique constraints.
3. Primary keys.

Most indexes use a B-tree implementation. Mathematically, finding a row in a B-tree happens in: $\text{Time Complexity} \approx O(\log n)$ (where $n$ is the total number of rows).

If no index is available, Postgres performs a Sequential Scan (Seq Scan). While generally slower, modern hardware is so fast that seq scans on tables with $< 20,000$ rows are usually instantaneous.

🤝 Performant Joins

When dealing with INNER JOIN, you should almost always join on primary keys. If you find yourself doing otherwise, it's usually a sign of a deeper schema design flaw. Treat your ON clauses with the same scrutiny as your WHERE clauses.

📈 Compound Indexes & ORDER BY

List queries on massive tables are often the first performance bottleneck. A compound index is the solution.

Example Syntax:

CREATE INDEX idx_user_status_created ON users (status, created_at DESC);

Pro Tip: To optimize ordering, ensure the columns in your ORDER BY clause are the final columns in your index and that their order matches the index definition. (Note: B-trees can be scanned in both directions, but aligning DESC is still best practice).

✍️ Writing Efficient Write Queries

The golden rule for writes is: Keep transactions as short as possible.

• Avoid External Calls: Never query an external API or service inside a database transaction.
• Minimize Locking: Only lock the specific rows you need. Every UPDATE creates a lock until the transaction commits; as traffic grows, lock contention becomes a major bottleneck.

⚠️ Critical Warning on Indexing: Running a standard CREATE INDEX on a live, large table will lock the table, blocking all inserts and updates. To avoid downtime, always use:

CREATE INDEX CONCURRENTLY index_name ON table_name (column);