Nick Burrett <nick@dsvr.net> writes:
> Tom Lane wrote:
>> Have you thought about
>> collapsing these into *one* table with an extra key column? Also, it'd
>> likely be better to combine the date and time into a timestamp column.
> I tried it back in the days when we only had around 1000 tables.
> Problem was that inserts and deletes took a *very* long time. IIRC a
> one row insert was taking over 10 seconds. I think this was because the
> index files were growing to several gigabytes.
That really shouldn't happen. If the date/time range that you are
storing moves over time (ie, you delete old stuff at the same rate you
are adding new stuff) then I can see that you would have had an
index-bloat problem, since the old btree code was unable to recycle
no-longer-needed subtrees. (This issue should go away in 7.4, FWIW.)
However, that should only affect the size of the index on disk; I can't
see that it would have much of an impact on insert or delete time.
For sure I don't see how an insert could take 10 seconds. What indexes
and foreign keys do you have on these tables?
It would be worth revisiting this and trying to understand where the
performance problem is, rather than just assuming that you have to work
around it.
regards, tom lane