Re: Number of tables

I think this requirement can be lumped into the category of "right
hammer, right nail" instead of the "one hammer, all nails" category.
There are many memory only or disk backed memory based key value
stores which meet your requirements like Reddis and memcached.

-Jerry

Jerry Champlin|Absolute Performance Inc.

On Aug 20, 2009, at 5:52 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:

> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>