Hi,
Recently, I ran a huge update on an Integer column affecting 100 million
rows in my database. What happened was my disk space increased in size and
my IO load was very high. It appears that MVCC wants to rewrite each row
(each row was about 5kB due to a bytea column). In addition, VACUUM needs to
run to recover space eating up even more IO bandwidth.
It came to my mind that what if there could be a mechanism in place to allow
overwriting portions of the same row *whenever possible* instead of creating
a new row as MVCC would require. This would work well for timestamp, char,
integer, float, boolean columns etc..
A user must explicitly call:
EXCLUSIVE LOCK ON TABLE
UPDATE ROWs
RELEASE LOCK ON TABLE.
It basically immitates the behavior of MySQL. Surely, this would be faster
than recreating the new row and marking the old one as invalid at the
expense of locking the table. MySQL users can then use Postgres and get
similar performance simply by locking the table first.
It probably works well when the transaction volume is low, when you need a
quick counter, when your IO bandwidth is saturated or when you want to avoid
VACUUMing after a massive update.
Any thoughts?
