Re: Scale, Normalization, and Table Count

Tom Cross <decius(at)whack(dot)org> writes:
> Much to my horror I got an error message while attempting to back up my
> database tonight:

> pg_dump: NOTICE: ShmemAlloc: out of memory
> pg_dump: Attempt to lock table "thread_204" failed. ERROR: LockAcquire:
> lock table 1 is out of memory

> Obviously this is related to my shared memory allocations.

The easiest response to this is to increase your
max_locks_per_transaction parameter in postgresql.conf.
The default (64) seems to be plenty for most people, as we hear reports
of such problems only once in a blue moon. But you're right that a
system with many thousands of tables might need more.

> There are, lets say, 1000 messages on each topic. Users will only access
> one topic at a time. It seems intuitive that it would be less expensive
> to create an individual table for each topic then it would be to put all
> of the messages in a single table.

I concur with the nearby responses that say this is indeed a bad design.

regards, tom lane