A following VACCUM brings back return times to 'start' -but I cannot
run VACUUM any other minute (?). And it exactly vaccums asmany tuples
as I updated.. sure thing:Why not? You only have to vacuum this one table. Vacuuming it once a minute should be doable.
Shouldn't the Database server be the entity that decides when vacuum is needed?
How is the database supposed to know when you want to purge records? Once a vacuum has been run, the table can not be rolled back or time traveled.
Giving the administrator full control over database management is a good thing.Something is very, very strange about the whole PostgreSQL maintenance model.
If you want to write a cron job, to purge records automaticaly, thats your
prerogative. Not every one needs to, nor want's to constantly purge records.
Most of my databases collect information and changing information in them would be taboo. Since records are not updated or deleted their is no reason to vacuum the collection tables, and they collect between 400 K to 40 M records per period.
Oracle uses MVCC and I do not have to UPDATE STATISTICS constantly to keep the system from going into the toilet.
Does Oracle purge records automaticaly? If so how do you configure it, and what are the default parameters?
It sounds like you have significant hardware limitations.Also, I should be able to do an update on every row in a database table without causing severe problems. Every other database system I know of does not have this problem.
If I have a million row table with a column called is_current, and I do this: UPDATE tname SET is_current = 0; Horrible things happen.
Just an idea: Why not recognize that more rows will be modified than the row setting can support and actually break the command into batches internally?
I have a database I use for traffic analysys, that has over 40,000,000 records, I have
done some complicated queries with multiple subselects and joins. The complicated
queries take a long time to complete, but they work. I have also done updates that
affected at least 5% of the records, then vacuumed the table shortly there after.
The bigger the table the more "scatch pad" disk space, and memory you need.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend