Re: Number of tables

On Thu, Aug 20, 2009 at 11:18 PM, Craig James<craig_james(at)emolecules(dot)com> wrote:
> Greg Stark wrote:
>>
>> What you want is a multi-column primary key where userid is part of
>> the key. You don't want to have a separate table for each user unless
>> each user has their own unique set of columns.
> Not always true.
...
> The primary difference is between
>  delete from big_table where userid = xx
> vesus
>  truncate user_table

This is a valid point but it's a fairly special case. For most
applications the overhead of deleting records and having to run vacuum
will be manageable and a small contribution to the normal vacuum
traffic. Assuming the above is necessary is a premature optimization
which is probably unnecessary.

> There are also significant differences in performance for large inserts,
> because a single-user table almost never needs indexes at all, whereas a big
> table for everyone has to have at least one user-id column that's indexed.

Maintaining indexes isn't free but one index is hardly going to be a
dealbreaker.

> Once the hitlist is populated, the user can page through it quickly with no
> further searching, e.g. using a web app.

The "traditional" approach to this would be a temporary table. However
in the modern world of web applications where the user session does
not map directly to a database session that no longer works (well it
never really worked in Postgres where temporary tables are not so
lightweight :( ).

It would be nice to have a solution to that where you could create
lightweight temporary objects which belong to an "application session"
which can be picked up by a different database connection each go
around.

--
greg
http://mit.edu/~gsstark/resume.pdf