Who can write to your database
Someone opens a table, notices the IDs jump from 4,812 to 4,931, and sends a message late in the afternoon: we lost records. For the next two hours three people stop what they were doing to hunt for a hundred and nineteen rows that, in all likelihood, never existed.
Start with the correction, because it buys those two hours back.
The PostgreSQL documentation is explicit. A value obtained from nextval is not reclaimed when the calling transaction aborts, and an INSERT with an ON CONFLICT clause computes the tuple, sequence call included, before it detects the conflict that sends it down the other branch. The docs land it flatly: sequence objects cannot be used to obtain gapless sequences.
The gap is the price of not putting every writer in a queue behind a single lock. It's a design choice, not a defect.
What "the data is corrupted" usually means
When a team says that, they are rarely describing page corruption on disk. They are describing a row that doesn't match the others. A status the application never emits. A date in a format no library in the project produces. A record missing the counterpart it should have in another table.
That's the real signal, and the gap in the sequence hides it rather than pointing at it.
An id is a counter. It doesn't know who wrote it. The useful question isn't whether the numbering is contiguous. It's whether that row could have come out of your code at all, and that question has an objective answer you can usually reach in ten minutes.
Every write path in a mature application leaves a trail beyond the row itself. An audit entry. A queued job. An invalidated cache key. A counter that moved. A webhook that fired. If the row is there and the trail isn't, it came in somewhere else.
Cheap test, and it almost never comes back inconclusive.
The inventory nobody has
From there the investigation turns into an inventory question: who holds credentials that can write to this table?
Most teams answer "the application" and stop. Then someone remembers the migration runner. Then the sync that feeds reporting. The internal console a developer built one weekend and nobody maintains. The restore script. The monthly import job with its own database user. The psql session somebody opened during an incident back in March.
None of these are malicious. None of them appeared out of nowhere. Each one was created for a good reason, on a day when solving the immediate problem mattered more than registering one more writer into the system.
That's the actual tension, and it isn't technical. A new write path solves today's problem and creates a debt that surfaces months later as a row the whole team agrees shouldn't exist. The person who made the shortcut is rarely the person who pays for it.
Integrity is not a property of your code. It's a property of how few paths can write, plus the rules the database enforces no matter who connects.
Validation and constraints defend against different things
This distinction settles more arguments than it looks like it should.
Validation living in the application exists to protect you from your users. It handles bad input, error messages, a form filled in wrong. It does that well, and that's where it belongs: close to the person, with enough context to explain what went wrong.
Constraints living in the database exist to protect you from everything else. From your scripts, your colleagues, and your 2am self debugging an incident in psql. A constraint is not polite and explains nothing. It just refuses.
A NOT NULL, a CHECK, a foreign key and a UNIQUE are worth more than any service layer, because they apply to everyone. The service layer applies only to whoever goes through it.
The common mistake is treating the two as redundant and picking one. They answer different threats.
The criterion
What I run through now, before creating any new write access:
- Will this writer produce the same side effects the application produces, or will it leave rows with no audit entry and no queued job behind them?
- Are the rules this path has to respect written into the database, or only into code this path never executes?
- If I disappear for six months, can someone discover this path exists without opening a connection and listing permissions one by one?
If those three answers aren't comfortable, the path can still exist. It just doesn't get to be silent. It goes into the schema documentation along with the reason it's there.
When the opposite is the right call
Worth stating plainly, because this advice turns into dogma fast.
A secondary write path is frequently correct. Bulk loads routed through the application are slow enough to make the work impossible. Replication, by definition, writes without passing through your code. A migration has to do what the domain model forbids, since that's precisely what it exists to do. An analytics pipeline writing to its own tables threatens nothing.
Forcing all of that through one door doesn't improve integrity. It creates a bottleneck and, worse, it rewards exactly the undocumented shortcut you were trying to prevent.
The difference was never the number of writers. It's whether they were designed or discovered. A planned writer has an owner, a constraint in the database, and a line in the docs. A discovered writer has a strange row in a table and three people looking for records that never existed.
There's also the case where gaps genuinely matter: invoice numbering, contract numbering, any sequence required to be continuous by something outside your system. The answer there isn't to fix the database sequence. It's to stop using a database sequence for that job and use a control table with the transaction holding the number. It costs concurrency, and in that case the cost is the entire point.
If someone asked right now how many different paths can write to your most important table, would you answer from memory, or would you need to open a connection to find out?