Re: ShmemAlloc errors

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