Building an Index on a Large PostgreSQL Table Without Freezing the Rest of the App

Reporting is slow. The query behind the monthly dashboard filters events by account and date, and events is by far the biggest table in the database. Everyone agrees it needs an index. Nobody wants to be the one who runs it.

The fear is reasonable. An ordinary CREATE INDEX blocks writes to the table while it builds, and a migration that holds an exclusive lock blocks reads too. On a big table that can take a while. Lock every table in the schema for the length of the migration, as ForgeSchema’s own first alpha did, and the whole app stalls for a reporting index.

Know the lock before you run it

ForgeSchema’s 0.2 alpha locks only the tables a plan changes. The planner tells you which ones:

./bin/forgeschema plan --explain current.json desired.yaml

The review lists the create_index step, notes that it isn’t concurrent and can block writes, and lists the lock set. Here that’s one line, ACCESS EXCLUSIVE on events. That mode blocks reads and writes on events until commit. Users, billing and everything else keep running. Inspection takes no table locks at all, so apply doesn’t queue up behind traffic on tables it isn’t touching.

That turns a vague “this might take the site down” into something you can schedule. Anything touching events will wait, including the dashboard itself, so you pick a quiet hour and pause the collector that writes there.

Give the slow step enough time

Apply’s defaults are cautious: five seconds to get a lock, thirty for a statement, sixty overall. A big index build will blow through that and roll back, which is safe but not useful. Raise the limits for this plan:

./bin/forgeschema apply --lock-timeout 10s --statement-timeout 45m --timeout 1h reviewed-plan.json

The lock timeout stays short on purpose. If a long-running report is holding events, you’d rather apply give up after ten seconds than sit in the lock queue while every new write piles up behind it. If it does fail, you get PostgreSQL’s reason, 55P03 for a lock timeout, and nothing is committed.

What’s coming next

The 0.2 alpha builds indexes inside the migration transaction, so that one table is unavailable while the index builds. Concurrent index builds, which keep reads and writes going while a large table is indexed, are already in development for the next release. The roadmap tracks where things stand. Until then, the honest pitch is narrower: you know exactly which table is affected, for how long you’re willing to let it be, and that a failure leaves nothing half done.