Re:Just use Postgres for everything
by Izkata on Saturday December 10, 2022 @02:52PM
(#33936136)
> I think because Postgres writes the entire row even if a single column has been updated.
This is because of the consistency model postgres uses: Roughly, the old row remains in existence as long as transactions started before the UPDATE are still running, and the new row exists alongside it for that duration. Transactions started before the UPDATE can't see the new row, and transactions started after the update can't see the old row, because the transaction id (txid) is added to the query and is compared to hidden columns on each table/row (xmin and xmax).
This is one of the things VACUUM cleans up, actually deleting those old rows once all the old transactions have ended.
This is because of the consistency model postgres uses: Roughly, the old row remains in existence as long as transactions started before the UPDATE are still running, and the new row exists alongside it for that duration. Transactions started before the UPDATE can't see the new row, and transactions started after the update can't see the old row, because the transaction id (txid) is added to the query and is compared to hidden columns on each table/row (xmin and xmax).
This is one of the things VACUUM cleans up, actually deleting those old rows once all the old transactions have ended.