Using Time Series Tables
If you maintain data for a rolling time period, use a series of tables, as the following diagram illustrates.
Create a new table each time you add a set of data, then delete the oldest table in the series. You gain a double benefit:
You avoid the added cost of deleting rows, because a DROP TABLE operation is much more efficient than a mass DELETE.
If the tables are sorted by timestamp, no vacuum is needed. If each table contains data for one month, a vacuum will at most have to rewrite one month’s worth of data, even if the tables are not sorted by timestamp.