> Date: Sun, 6 Oct 2002 02:39:57 -0700 (PDT)
> From: Tom Cross <decius@whack.org>
> To: pgsql-general@postgresql.org
> Subject: Scale, Normalization, and Table Count
> Message-ID: <Pine.GSO.4.40.0210060210140.28070-100000@apogee.whack.org>
>
> Essentially, I have a discussion system. There could be millions of
> messages in the system. Lets pretend that there are 10 million for the
> sake of arguement. Of course, these messages are all on different topics.
> 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. If I can directly access the 1000
> messages that I need by loading the table that they are in, I save myself
> from having to search a table which contains 10 million messages.
>
I feel somewhat uneasy with your "one table per topic" setup because it makes
the database structure dependent from the data contents.
A more natural setup would be two tables:
- a table "topics" containing key and topic
- a table "messages" containing a foreign key reference to "topic"
If you create an index on the topic reference field in "messages", performance
should be ok (apart from the usual Postgres "analyze" caveat).
If this setup does not work performant you might try a compromise by dividing
all topics into several broad categories, each of which gets its own table.
In that case you can still emulate the full messages table with a view.
Hope this helps,
Christoph Dalitz