Operating on a minimal two-core Postgres instance: Query optimization insights

Original Article ↗ Hacker News Discussion ↗

Running an entire company on a tiny two‑core PostgreSQL instance prompts a broader debate about where to put complexity: in careful query design and schema optimization, or in simply buying more database capacity. Commenters argue over pushing logic and joins into the application layer versus keeping business rules and data integrity in the database, touching on maintainability, performance, and ACID guarantees. Many see basic SQL literacy, indexing, and query‑plan analysis as underused, while others stress that developer time and product‑market fit often matter more than squeezing every last drop of efficiency from the database.

Feasibility of tiny Postgres instances

  • Many commenters like the reminder that modest hardware (2 cores, a few GB RAM) can handle serious workloads, echoing “20 years ago we did a lot with far less.”
  • Some run entire apps on very small VMs or cheap ARM hosts and report good TPS/RPS with careful design and caching.
  • Others argue that while inspiring, this can be overkill when cloud instances are relatively cheap to scale up.

Shifting logic from database to application

  • The article’s “shift logic to the application” line is debated.
  • Critics say moving joins/filters to the app often increases network I/O, round-trips, and complexity, and wastes the DB’s strengths.
  • Defenders note cases where:
    • App resources scale more cheaply than DB resources.
    • Optional parameters or complex conditions are easier handled by several targeted queries than one huge “do everything” query.
    • Splitting “pointer-chasing” joins across multiple indexed queries can make systems more “NoSQL-ready” and easier to cache.

Business logic in the DB vs in code

  • One camp prefers heavy use of stored procedures and constraints so critical logic is ACID, centralized, and consistent.
  • Another camp prefers a “dumb DB”:
    • Mixed compute + data workloads are harder to profile and tune.
    • DB-language ecosystems and tooling (PL/SQL-style) are seen as weaker, harder to test, version, and document.
  • Disagreement centers on maintainability vs strong data integrity, not just performance.

Query planning, joins, and indexes

  • Commenters challenge the idea that join methods like nested loop/hash/merge are “suboptimal” in general; they’re context-dependent.
  • Postgres’ cost-based planner can pick poor join orders, especially with bad stats or many joins; lack of explicit join hints frustrates some.
  • Workarounds mentioned: tuning work_mem, join_collapse_limit, disabling join types (enable_*), using WITH MATERIALIZED, and understanding table statistics.
  • There’s broad agreement that understanding SQL, query plans, and indexing is increasingly rare but crucial.

Cost and optimization tradeoffs

  • One side: developer time is far more expensive than an extra few cores or more RAM; over-optimizing to save a few thousand per year is penny-wise, pound-foolish.
  • The other side: the “just add hardware/cloud” culture leads to massive recurring bills; basic query tuning and schema design should be standard practice.
  • Several stress that modest, continuous attention to performance avoids later crises and SRE firefighting.