On Thu, Aug 20, 2009 at 11:18 PM, Craig James<craig_james@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
