Benefit to keeping a record in the database, rather than deleting it, for performance issues?

Question

So I have a client that I am building a Rails app for....I am using PostgreSQL.

He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I have heard about this I figured I would ask you guys to hear your thoughts.

I'd rather hide than delete because deletions in tables eventually lead to table index havoc that causes queries to take longer than expected (much worse than Inserts or Updates). This won't be a problem in the beginning of the site (it gets exponentially worse over time), but seems like an easy issue to never encounter by just not deleting anything (yet) as part of the "everyday" web application functionality. We can always handle deletions much later as part of a Data Optimization & Maintenance process and re-index tables in that process on some (yet to be determined) scheduled basis.

In all the Rails apps I have built, I have never had an issue with records being deleted and it affecting the index.

Am I missing something? Is this a problem that used to exist, but modern RDBMS products have fixed it?

Answer 1

In PostgreSQL, an UPDATE is implemented as marking the current row version as dead, followed by inserting a new version of the row with the new values. The entire row is recreated no matter how many columns are updated.
Additionally, if it leads to an index change, a new row version is also created in each concerned index. On the whole, it's pretty heavy (compared to overwriting bytes in a file).

By contrast, DELETE is implemented as marking the row version as dead, both in data and indexes. Since dead in this context means no longer visible to this transaction and the future transactions (unless it's rolled back), this is a bit similar to your hidden flag, except it's more efficient than an UPDATE since there's no new version of the row.

At some point, VACUUM will kick in, presumaby by way of autovacuum, and mark the dead row as reusable space. Quoting the documentation:

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

The bloat caused by high-frequency or high-volume writes, both UPDATEs and DELETEs, is one of the things that a postgres DBA should indeed keep an eye on, but there's no apparent reason DELETE would cause more bloat. In fact, based on the implementation choices, it's probably the contrary.

In any case, periodic REINDEX are worthwhile. See Routine reindexing.