PostgreSQL — Pitfalls

OWNER: boris, yoanna ALSO_USED_BY: urszula, maxim, piotr, ashley, floris, hugo LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: PostgreSQL 16.x

---

Overview

Known failure modes and traps in PostgreSQL that GE agents have encountered. Every item here was discovered in production, during security audits, or through performance incidents. Read before writing any database-facing code.

---

Lock Contention

ANTI_PATTERN: Long-running transactions holding row-level locks.

// Transaction holds locks for the entire duration
await db.transaction(async (tx) => {
  const client = await tx.select().from(clients).where(eq(clients.id, id));
  // ... 5 seconds of API calls ...
  await tx.update(clients).set({ status: 'active' }).where(eq(clients.id, id));
});

FIX: Keep transactions short. Move external calls outside:

const client = await db.select().from(clients).where(eq(clients.id, id));
const apiResult = await callExternalApi(client);
await db.transaction(async (tx) => {
  await tx.update(clients).set({ status: 'active' }).where(eq(clients.id, id));
  await tx.insert(auditLog).values({ action: 'activate', data: apiResult });
});

ANTI_PATTERN: ALTER TABLE on a busy table during business hours. FIX: Most ALTER TABLE operations acquire ACCESS EXCLUSIVE lock. Schedule for maintenance windows. Use lock_timeout to prevent indefinite waits:

SET lock_timeout = '5s';
ALTER TABLE tasks ADD COLUMN new_col text;

---

Idle Transactions

ANTI_PATTERN: Opening a transaction and forgetting to commit/rollback. Idle transactions hold locks and prevent autovacuum from reclaiming dead rows.

-- Find idle transactions
SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes';

FIX: Set idle_in_transaction_session_timeout:

# postgresql.conf
idle_in_transaction_session_timeout = 30000  # 30 seconds

This automatically terminates transactions idle for more than 30 seconds.

CHECK: Agent sees autovacuum not cleaning up dead rows. IF: pg_stat_activity shows idle-in-transaction connections. THEN: Those transactions are blocking autovacuum. Kill them:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '10 minutes';

---

Sequence Gaps

ANTI_PATTERN: Expecting sequences (serial/identity columns) to be gapless. Sequences increment on INSERT attempt, NOT on successful commit. Rolled-back transactions consume sequence values permanently.

FIX: Accept gaps. If gapless numbering is required (invoice numbers), use a separate counter table with row-level locking:

CREATE TABLE invoice_counters (
  tenant_id uuid PRIMARY KEY REFERENCES tenants(id),
  last_number integer NOT NULL DEFAULT 0
);

-- Atomic increment
UPDATE invoice_counters
SET last_number = last_number + 1
WHERE tenant_id = $1
RETURNING last_number;

ANTI_PATTERN: Using sequences for human-facing invoice or order numbers. FIX: Sequences are database internals. Human-facing numbers use dedicated counter tables with explicit gap control.

---

Timezone Handling

ANTI_PATTERN: Using timestamp without time zone.

-- WRONG
created_at timestamp DEFAULT now()

-- RIGHT
created_at timestamptz DEFAULT now()

FIX: Always use timestamptz (shorthand for timestamp with time zone). The database stores in UTC internally. Without timezone, the timestamp is interpreted differently depending on the server's timezone setting.

ANTI_PATTERN: Comparing timestamps with string literals without timezone.

-- WRONG — interpreted as server timezone, not UTC
WHERE created_at > '2026-03-26 00:00:00'

-- RIGHT — explicit timezone
WHERE created_at > '2026-03-26T00:00:00Z'
WHERE created_at > '2026-03-26 00:00:00+00'

FIX: Always include timezone in timestamp literals. Or use AT TIME ZONE explicitly.

CHECK: Agent is writing a timestamp comparison. IF: The literal has no timezone suffix. THEN: Add Z (UTC) or the appropriate offset.

---

JSONB vs Normalized Tables

ANTI_PATTERN: Storing relational data in JSONB because "it's flexible."

-- WRONG — cannot efficiently query, join, or index these fields
CREATE TABLE projects (
  id uuid PRIMARY KEY,
  data jsonb  -- contains client_name, status, team, milestones...
);

FIX: If you query it, filter it, join on it, or aggregate it — it belongs in a proper column or related table.

JSONB is appropriate for: - Truly dynamic metadata (plugin config, user preferences). - Denormalized cache of external API responses. - Event payloads with variable schemas.

JSONB is NOT appropriate for: - Core business fields (name, status, dates). - Fields used in WHERE clauses. - Fields that need referential integrity. - Fields that need to be NOT NULL.

---

Enum Modification Traps

ANTI_PATTERN: Trying to remove a value from a PostgreSQL enum.

-- THIS DOES NOT WORK
ALTER TYPE task_status DROP VALUE 'cancelled';

FIX: PostgreSQL enums are append-only. To "remove" a value: 1. Add the replacement value. 2. Migrate all rows using the old value. 3. Leave the old value in the enum (unused). Or: recreate the enum type (requires dropping all columns that use it).

ANTI_PATTERN: Renaming an enum value.

-- PostgreSQL 10+: This works but is risky during rolling deploys
ALTER TYPE task_status RENAME VALUE 'cancelled' TO 'voided';

FIX: Treat as add-new + migrate-data + leave-old pattern. Running application instances may still INSERT the old value during the deploy window.

---

Index Bloat After Bulk Updates

ANTI_PATTERN: Bulk updating a column that has a B-tree index.

-- Updates 100K rows — creates 100K dead index entries
UPDATE tasks SET status = 'archived' WHERE created_at < '2025-01-01';

FIX: After bulk updates, reindex:

REINDEX INDEX CONCURRENTLY tasks_status_idx;

Or preventatively, batch the updates:

-- Process in chunks of 1000
WITH batch AS (
  SELECT id FROM tasks
  WHERE created_at < '2025-01-01' AND status != 'archived'
  LIMIT 1000
)
UPDATE tasks SET status = 'archived'
WHERE id IN (SELECT id FROM batch);

---

Connection Exhaustion

ANTI_PATTERN: Each microservice opening its own connection pool without limits.

Service A: max=20
Service B: max=20
Service C: max=20
Total: 60 connections → may exceed max_connections=100

FIX: Use PgBouncer as a single pooling layer:

Service A → PgBouncer (max=20 to PostgreSQL)
Service B → PgBouncer (reuses same pool)
Service C → PgBouncer (reuses same pool)

CHECK: Agent is adding a new service that connects to PostgreSQL. IF: The service creates its own postgres connection. THEN: Route through PgBouncer. Never connect directly to PostgreSQL from application services.

---

Implicit Casting Surprises

ANTI_PATTERN: Comparing UUID column with text without explicit cast.

-- May work, may not — depends on PostgreSQL config
WHERE id = '550e8400-e29b-41d4-a716-446655440000'

FIX: Be explicit:

WHERE id = '550e8400-e29b-41d4-a716-446655440000'::uuid

In Drizzle, the ORM handles casting, but raw SQL queries need explicit casts.

---

Backup Restore Overwrites Sequences

ANTI_PATTERN: Restoring a backup and then getting duplicate key errors. pg_restore resets sequences to the backup's last value, but new rows may have been inserted since the backup.

FIX: After restoring, reset sequences to current max:

SELECT setval('tablename_id_seq', (SELECT max(id) FROM tablename));

---

RLS Leak: Missing FORCE

ANTI_PATTERN: Enabling RLS without FORCE ROW LEVEL SECURITY.

-- Without FORCE, the table owner bypasses ALL policies
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Owner can still see everything!

FIX: Always use both:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md

---

Deadlocks from Inconsistent Lock Ordering

ANTI_PATTERN: Two transactions updating the same rows in different order.

TX1: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
TX1: UPDATE accounts SET balance = balance + 100 WHERE id = 2;

TX2: UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- waits on TX1
TX2: UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- DEADLOCK

FIX: Always lock rows in a consistent order (e.g., by ID ascending):

-- Both transactions lock id=1 first, then id=2
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

---

Cross-References

READ_ALSO: wiki/docs/stack/postgresql/index.md READ_ALSO: wiki/docs/stack/postgresql/performance.md READ_ALSO: wiki/docs/stack/postgresql/security.md READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/postgresql/checklist.md READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md