Prestanda Consulting
Home › Insights › 300 Million+ Records. One Postgres Database. Zero Downtime — Here’s How We Pulled It Off.
Data Engineering

300 Million+ Records. One Postgres Database. Zero Downtime — Here’s How We Pulled It Off.

How Prestanda Consulting tuned, partitioned, and fine-tuned Heroku Postgres to support 300 million+ customer leads with sub-200 ms queries and zero downtime.

PBy Prestanda Consulting
Oct 15, 2025 · 3 min read

1. The Challenge: Heroku Postgres Meets Nationwide Data

When most teams hit the 50-million-record mark, they start discussing a migration to BigQuery, Snowflake, or Redshift. In this case, the platform needed real-time visibility across 300 million+ customer leads spanning every U.S. ZIP code — all running on Heroku Postgres.

There were no outages, maintenance windows, or missed SLAs available as a trade-off. The platform served a national field-service operation, ingesting, enriching, and distributing leads in real time to thousands of agents across the country. Each record carried 80+ columns of customer intelligence, location data, and campaign metadata.

  • Data volume: 300M+ records, growing by 5M+ per day.
  • Concurrent access: Thousands of web and mobile clients.
  • Storage ceiling: Approximately 500 GB per Heroku Postgres instance.
  • Performance target: Sub-200 ms query latency.
  • Downtime tolerance: Zero.
Operating principle: Scaling up was not the option. The requirement was to scale intelligently within the managed platform constraints.

2. Breaking Down the Bottlenecks

Bulk loading without table locks

Early bulk loads froze the system. The team replaced long transactions with a chunked COPY strategy, processing 100K rows per batch and committing incrementally. This allowed reads and writes to continue while data was ingested.

Resilient stored procedures

Monolithic SQL functions were refactored into modular PL/pgSQL procedures with early-exit guards for completed chunks, temporary tables for transformation stages, and exception-based rollback only for failing batches.

Result: 3× faster nightly jobs, with no table locks.

Query latency meltdown

Full-table scans were eliminated by adding partial indexes on active ZIP codes, GIN indexes for JSON fields, and incrementally refreshed materialized views.

Result: Average query time dropped from 5 seconds to under 200 ms.

3. Partitioning: The Turning Point

The dataset was reorganized into state-based partitions: 50 states, with approximately 6 million records each. This materially changed the behavior of the database:

  • Inserts hit only one child table, reducing global index bloat.
  • Queries scoped to a state enabled faster planner decisions.
  • Vacuuming could occur independently, avoiding I/O storms.

Postgres stopped struggling and started flying.

4. Fine-Tuning Heroku Postgres for Enterprise Performance

Heroku defaults are safe, but they are not optimized for this workload. Within the managed service constraints, the implementation added:

  • PgBouncer connection pooling for API and worker dynos.
  • Read replicas for reporting queries.
  • Automated VACUUM/ANALYZE to preserve planner accuracy.

Together, these controls brought Heroku Postgres to enterprise-class stability.

5. Results and Enterprise Lessons

Postgres now handles 300M+ records while powering thousands of concurrent API requests smoothly and predictably, without a minute of downtime.

  1. Partition early; refactor later.
  2. Batch intelligently and commit smaller.
  3. Monitor everything: latency, pools, and autovacuum.
  4. Tune for workload, not defaults.

Enterprise scale does not require an exotic cloud stack. It requires data discipline, surgical tuning, and observability.

Originally published by Prestanda Consulting on Medium. View the original Medium publication.