One of the biggest problems that people run into is whenever a vacuum/
data compression runs at 10 past the hour, the entire system slows
down. We came up with a new database schema for the storage of the
detailed data that used to be stored in eam_measurement_data, which
is the table that's getting hit the hardest when data compression
happens, because we are trying to delete data at the same time that
we are trying to insert and read data. The new scheme is to break up
the table into smaller tables, representing each day's worth of data
in two tables. At any one time, there will be the concept of the
"current" table that we are inserting into. However, when we need to
delete data, we simply find the table that has data older than the
configured interval, and simply truncate that table. We would front
all of the tables by a view so that selects are not affected. This
has several advantages:
1. We no longer need to worry about vacuuming the
eam_measurement_data table
2. We would not be doing delete or insert on the same table
3. Smaller tables are more available to be completely loaded into
Postgres' cache memory
Are there DBAs out there who want to validate this strategy? This
should be pretty portable across the different database backends that
we support.
Charles
