On May 2, 2008, at 1:40 PM, Scott Marlowe wrote:
Again, a database protects your data from getting scrambled should the program updating it quit halfway through etc...
Right -- but this is a data mining work, I add a derived column to a row, and it's computed from that very row and a small second table which should fit in RAM.
Have you been vacuuming between these update attempts? Each one has created millions of dead rows and bloated your data store. vacuum full / cluster / reindex may be needed.
I've read postgresql.conf better and see autovacuum = on is commented out, so it's on. That explains why shutting down was taking so long to shut autovacuum down too.
Basically, the derived data is not critical at all, -- can I turn (1) off transactional behavior for an UPDATE, (2) should I care about vacuuming being done on the fly when saving RAM, or need I defer it/ manage it manually?
I wonder what MySQL would do here on MyISAM tables without transactional behavior -- perhaps this is the case more suitable for them?
Cheers, Alexy
