# Ahead of the 2.1 Billion Wall: bigint Migrations Delivered as Pull Requests

> Our system health check found integer primary keys on the platform's busiest tables. An integer column tops out at 2.1 billion, and these counters only move in one direction. Hit the wall and inserts fail. Not degrade. Fail, in the middle of a business day, on the tables the whole product depends on.

# Ahead of the 2.1 Billion Wall

Postgres bigint Migrations Delivered as Pull Requests

[ Learn More ](</contact-us/>) [ Get Support ](</support/>)

* **Industry:** Automotive services software (multi-tenant SaaS)
  *  **Organization:** A venture-backed SaaS platform for auto repair shops
  *  **Environment:** Heroku Postgres, 687 GB production database, 112 tables, multi-tenant Ruby on Rails
  *  **Engagement:** PostgreSQL health check plus hands-on remediation
  *  **Services:** Schema migration engineering · Full-text search tuning · Platform evaluation · Emergency standby



Metric  |  Before  |  After   
---|---|---  
Primary key headroom on core tables  |  Integer, a hard 2.1 billion ceiling  |  Bigint, ceiling removed   
Migration runtime  |  Unknown  |  Timed on a 687 GB fork of production: 16m50s, 1h24m, 2h32m for the three largest   
Largest migration vs. maintenance window  |  Would not fit  |  Split in two; first production window executed cleanly   
Delivery format  |  Recommendations memo  |  Rails migrations written by us, submitted as pull requests to the client's repository   
Results at a glance

## Get in touch

We would love to chat about how we can be your Postgres or Open Source partner

[Learn More](</contact-us/>)

## The Client

The client runs a multi-tenant SaaS platform that auto repair shops use to write estimates, order parts, and get cars back to their owners. Every repair order, every vehicle, every customer profile lives in one Postgres database on Heroku. When that database slows down, a service writer is standing at a counter with a customer waiting. When it stops, hundreds of shops stop with it.

## The Challenge

Our system health check found integer primary keys on the platform's busiest tables. An integer column tops out at 2.1 billion, and these counters only move in one direction. Hit the wall and inserts fail. Not degrade. Fail, in the middle of a business day, on the tables the whole product depends on.

Fixing it means rewriting some of the largest tables in a 687 GB database, and the platform made that harder, not easier. Heroku Postgres allows no access to the configuration knobs that speed up bulk rewrites. Logical replication, our preferred near-zero-downtime path, was blocked: publications could be created, subscriptions could not. One concurrent index build on this database had already run past 24 hours and finished INVALID. Everything had to happen inside maintenance windows the client could actually afford.

The client's engineering team had one more constraint. They manage every schema change through version-controlled Rails migrations, and they did not have spare capacity to write these.

## The Solution

So we wrote them.

Our engineer checked out the client's codebase, got the application running locally, and wrote the ActiveRecord migrations to move the primary and foreign keys to bigint, in the client's own conventions, submitted as pull requests through their normal review process. Not a script attached to a ticket. A commit with our name on it, passing their checks.

Then we rehearsed. The client forked the production database, all 687 GB of it, and we ran every migration against the fork with a stopwatch: 16 minutes 50 seconds for the vehicle references, 1 hour 24 minutes for inventory, 2 hours 32 minutes for profiles. The rehearsal caught what a dev database never could. One migration failed against a trigger that existed only in production. We fixed it before it ever mattered.

The timings showed the largest migration would not fit the maintenance window, so we split it into two parts and sequenced them across separate windows. The first production window ran in late July and executed cleanly. The client ran the remaining windows themselves, following our runbook, with our 24x7 emergency line standing by. They never needed it for a failure, but it was there.

Along the way we handled what the engagement surfaced: we diagnosed a deadlock during CREATE FUNCTION down to catalog tuple locks and function body checking, redesigned their multi-tenant full-text search around tenant-scoped partial GIN indexes so big tenants stopped wrecking the planner for everyone, and evaluated a move off Heroku entirely, recommending EC2 where the knobs we kept reaching for would finally exist.

## The Results

The 2.1 billion ceiling is gone from the tables that were counting toward it. The client got version-controlled migrations that fit their process, timed runbooks proven against a full-size copy of production, and a team that had watched every step before doing it alone. The engagement kept going: search tuning, platform planning, and standby coverage.

## Why it Matters

Every consultancy will tell you your integer keys are going to overflow. A memo does not move a 687 GB database. The measure of a database partner is whether they will open the pull request, run the rehearsal, and put their name in your commit history next to yours.

Advice is cheap. Commits are accountability.

---
[View this page online](https://www.commandprompt.com/about/success-stories-case-studies/ahead-of-the-21-billion-wall-bigint-migrations-delivered-as-pull-requests/)