The store in this example is made up. It runs its orders on PostgreSQL. The product team now wants a new delivery-window field on every order. The app is busy all day, so downtime is hard to book. The team wants to know how to change the table without stopping sales. These checks cover that one live change, not a full database review.

We read the official PostgreSQL docs for version 18. We read the pages on ALTER TABLE, CREATE INDEX, table locks, and the lock_timeout setting. We also read the pages on pg_dump and on point-in-time recovery. The six checks below each point back to those pages.

As of 16 November 2026, the PostgreSQL versioning page lists 18.7 as the newest release of version 18. Version 19 is still a release candidate, not a final release.

This list does not promise zero downtime. Other databases lock tables in other ways, so read your own engine's docs too.

Word key

  • Schema: the shape of your tables, such as their columns, types, and rules.
  • Lock: a hold that a command places on a table so others wait or carry on safely.
  • Expand and contract: add the new shape first, move the app over, and remove the old shape last.
  • Rewrite: the database copies every row of a table into a new file.

Check 1. Does this change need the strictest lock, and for how long?

Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, unless the docs say otherwise for that form. This is the strictest lock in PostgreSQL. While a command holds it, no other session can use the table at all. That includes plain reads.

So ask how long the lock is held. A metadata-only change may hold it for a moment. A full scan or rewrite can hold it much longer.

Our advice: for each step in the change, write down which lock it takes. Then mark which steps are quick and which may scan or rewrite.

Check 2. Will adding the column copy the whole table?

The docs explain two cases. If the new column has no default, or a fixed default, PostgreSQL stores that value once in the table's metadata. Old rows show the value when they are read. No rewrite is needed, so the change is fast even on a big table.

A default that changes per row is different. One example is clock_timestamp(), which returns the current time. With a default like that, every row and every index on the table gets rewritten.

Our tip: add the column with no default or a fixed one. Fill real values later in small batches, outside the locking step. The docs suggest this order too.

Check 3. Have you set a short lock timeout for this change?

A schema command may have to wait for the table lock. Long reads or open transactions may already hold weaker locks. While your command waits, other queries on that table can end up waiting too.

The lock_timeout setting helps here. It stops any statement that waits too long to get a lock. By default it is zero, which means no limit. The docs advise against setting it for the whole server.

Our advice: set lock_timeout only in the session that runs the change. Pick a few seconds, not minutes. If the command times out, nothing changes, and you can retry at a quieter time.

Check 4. Can new rules and indexes go in without blocking writes?

A new rule, such as a foreign key or a CHECK rule, normally scans every row. During that scan, other updates to the table must wait. The docs describe a two-step path for this. First add the rule with NOT VALID, which skips the scan and commits fast. New writes must still follow the rule.

Then run VALIDATE CONSTRAINT as a second step. This step checks the old rows. It takes a lighter lock, so normal reads and writes keep going.

Indexes have a similar path. A normal CREATE INDEX blocks writes until it ends, though reads continue. CREATE INDEX CONCURRENTLY lets writes continue during the build. It takes longer, and it cannot run inside a transaction block.

If a concurrent build fails, it can leave an index marked INVALID. Queries ignore that index, but updates still pay to maintain it. The docs suggest you drop it and build it again.

Our tip: add a check for INVALID indexes to your release script.

Check 5. Will old and new app versions both work during the change?

During a rollout, old and new app servers often run side by side. If a step removes or renames a column too early, the old servers can start to fail.

The expand and contract pattern avoids this. In the expand stage, you add the new column and leave the old one alone. The new app writes to both, or reads the new one with a safe fallback. Then you backfill old rows in small batches.

In the contract stage, you remove the old column, but only after no running code uses it. Dropping a column also takes the strict lock, so give it the same timeout care.

Our advice: draw the steps as a simple table. Show which app version runs at each step and which columns it uses. Each step should work with the versions on both sides of it.

Check 6. If it goes wrong, how do you get back?

Have a way back before you start. The pg_dump tool makes a consistent export while the database stays in use. It does not block readers or writers. Still, the docs say it is usually not the right tool for regular production backups.

For that, the docs describe continuous archiving with point-in-time recovery. You keep a base backup plus a stream of change logs. You can then restore the database to any moment after that base backup.

A restore is a last resort. It can also undo good orders placed after the bad step.

Our tip: for each expand step, write the matching undo step. Test both on a copy of production data first. Note when each step starts, in case you ever need a restore to a set time.

Tick these off before you change the live table

  • [ ] Each step lists the lock it takes, based on the docs for that command.
  • [ ] No step adds a column with a per-row default on a large table.
  • [ ] lock_timeout is set in the change session only, with a retry plan.
  • [ ] New rules use NOT VALID first, and new indexes use CONCURRENTLY.
  • [ ] Old and new app versions both work at every step of the rollout.
  • [ ] A tested undo step and a recent restorable backup exist before the first step.

Ask your engineering team these questions

  1. Which step takes the strictest lock, and how long did it hold that lock on a copy of production?
  2. Does any step rewrite or scan the whole table?
  3. What lock timeout will we use, and what happens if it fires?
  4. How will we spot and remove an INVALID index after a failed build?
  5. When will the old column be safe to drop, and who decides?
  6. What is our undo step, and when did we last test a restore?

Read the docs yourself

Codewave Consulting LLC wrote this checklist from the public PostgreSQL documentation. It is not a promise that any change will run without downtime. It is not professional advice on your own systems, and it is not legal advice. A qualified engineer should test each step on a copy of your own data first. We are an E-Verified employer. We proudly support H-1B visa sponsorship for qualified resources. Eligibility is reviewed individually; sponsorship does not guarantee a role, selection or government approval.

Where we work: Codewave Consulting LLC is a Direct Vendor for Whirlpool, based in Michigan. We also build custom software, plan cloud and database work, and provide IT staffing. Learn more at codewaveconsulting.com.