Nick Burrett <nick@dsvr.net> writes:
> At the start of the fourth month, the data from the first month is deleted and
> the database vacummed.
When dropping a quarter of the records of a large table you would need a very
large setting free space map. For an occasional periodic job like this you can
just use VACUUM FULL to rebuild the table and free up the space.
> CREATE TABLE fiveminute ( server CHAR(32),
You know CHAR() is space padded, right?
> CREATE UNIQUE INDEX fiveminute_idx ON fiveminute(server,stamp);
>
> It probably would have made sense to just have an index on the server column,
> but I can't remember why (decision was made Nov-2000). I suspect that is the
> cause of the index bloat.
Well, having a unique index is good conceptually because it prevents duplicate
insertions from application errors. But it's probably not worth the
performance hit, given that there are a lot more errors it won't catch.
> The database never survived operational use. The original import of data took
> so long (2+ days) that the re-design was done almost immediately.
How were you importing? The fastest way would be to generate an ascii file in
the format \copy expects.
--
greg