Tom Lane wrote:
> Nick Burrett <nick@dsvr.net> writes:
>
>>Tom Lane wrote:
>>
>>>We don't normally hear of people needing that --- is there anything
>>>unusual about the schema of this database?
>
>
>>Not particularly. The database consists of around 3000 tables created
>>using this:
>
>
>>CREATE TABLE vs_foo (date date NOT NULL,
>> time time NOT NULL,
>> bytesin int8 CHECK (bytesin >= 0),
>> bytesout int8 CHECK (bytesout >= 0));
>
>
>>Each table has around 1500 rows.
>
>
> 3000 tables? That's why you need so many locks.
I'm surprised that I've never hit this problem before though.
> 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.
Having everything in one large table would have been great and would
have made life much easier.
date and time were split to simplify queries. I think it had an impact
on index sizes.
Regards,
Nick.
--
Nick Burrett
Network Engineer, Designer Servers Ltd. http://www.dsvr.co.uk