The startup's Postgres survival guide

Original Article ↗ Hacker News Discussion ↗

Startup engineers trading war stories about running PostgreSQL emphasize that operational basics—monitoring, backups, and connection pooling—matter more for survival than exotic tuning. Commenters debate managed services like AWS RDS versus self‑hosting on cheap VPSes, balancing cost, lock‑in, and the need for high availability, while sharing concrete backup tools, pooling strategies, and limits of DIY setups. Schema design and query patterns are another major theme, with advice to favor sound normalization over heavy JSONB use, be cautious with long‑running transactions and locks, and understand indexing, migrations, and ORMs well enough to avoid performance and reliability pitfalls as systems scale.

Backups, Monitoring, and “Survival” Basics

  • Several commenters think the guide underplays backups, restores, and monitoring.
  • Strong sentiment that any production Postgres needs:
    • Automated backups (ideally PITR) and regular restore tests.
    • Monitoring for XID wraparound, disk usage, and long transactions tied to paging, not email.
  • Tools mentioned: pgBackRest (popular, PITR, incremental deltas, but requires periodic full backups), Barman, simple pg_dump+cron+object storage, and volume snapshots (e.g., EBS) as a secondary strategy.

Managed vs Self-Hosted Postgres

  • Many advocate using managed services (RDS/Cloud SQL) early for HA, backups, PITR, and less operational burden.
  • Others report overprovisioned replicas and cost bloat, or frustrating limitations and cloud lock‑in.
  • Some argue a couple of DBAs plus self‑hosted Postgres (often on cheap VPS or Hetzner) gives more flexibility and lower cost; basic HA setups can be run cheaply.

Schema Design, Normalization, and JSONB

  • Strong agreement that good schema design and normalization matter; ORMs auto‑generating schemas are criticized.
  • JSONB is useful for variable or log‑like data, but overusing it can hurt performance and data quality; normalized schemas plus joins are usually fast enough.
  • Some recommend append‑only “source of truth” tables with derived denormalized views; others warn event sourcing everywhere is overkill for startups.

Indexes, UUIDs, and Query Planning

  • Discussion of index types: btree by default, but GIN/GiST, BRIN, and hash indexes can be powerful in the right workloads.
  • Advice to consider UUIDv7 over UUIDv4 for better index locality; others note bigint serial PKs are often simpler and faster for joins.
  • Query planner quirks: sometimes multiple simpler queries or in‑memory joins outperform one complex query; some disable seqscan in tests to inspect index usage.

Transactions, Locking, and Deadlocks

  • Warnings against long‑running or idle‑in‑transaction sessions; suggested use of timeouts (idle_in_transaction_session_timeout, lock_timeout, statement_timeout).
  • To avoid deadlocks, consistently order row and table locking; retries can worsen hot‑row contention.
  • Debate over SELECT … FOR UPDATE and SKIP LOCKED: useful for queues and games vs a smell if your core model is append‑only.

Connection Pooling

  • Connection limits are a common startup failure mode.
  • External poolers like PgBouncer (LIFO) help reduce DB connections vs in‑process FIFO pools that mainly reduce latency.
  • Caution about per‑request transactions and dependency‑injected connections that keep transactions open too long.

Stored Functions and ORMs

  • Divided views: some see stored procedures as powerful for constraints, triggers, and security; others avoid them to keep logic in app code and maintain flexibility.
  • Similar split on ORMs: some call them long‑term tech debt and prefer raw SQL; others find them productive, as long as you understand SQL and drop down when needed.