Re: Number of tables

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.

>> When the user logs back in, a hidden part of the login process gets a table
>> from the pool of available tables, assigns it to this user, and copies the
>> user's  data from the archive into this personal table.  They are now ready
>> to work. This whole process takes just a fraction of a second for most
>> users.
>
> And what does all this accomplish?

The primary difference is between

  delete from big_table where userid = xx

vesus

  truncate user_table

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

In our application, the per-user tables are "hitlists" -- scratch lists that are populated something like this.  The
hitlistis something like this: 

   create table hitlist_xxx (
     row_id integer,
     sortorder integer default nextval('hitlist_seq_xxx')
   )


   truncate table hitlist_xxx;
   select setval(hitlist_seq_xxx, 1, false);
   insert into hitlist_xxx (row_id) (select some_id from ... where ... order by ...);

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

We tested the performance using a single large table in Postgres, and it was not nearly what we needed.  These hitlists
tendto be transitory, and the typical operation is to discard the entire list and create a new one.  Sometimes the user
willsort the entire list based on some criterion, which also requires a copy/delete/re-insert using a new order-by. 

With both Oracle and Postgres, truncate is MUCH faster than delete, and the added index needed for a single large table
onlymakes it worse.  With Postgres, the repeated large delete/insert makes for tables that need a lot of vacuuming and
indexbloat, further hurting performance. 

Craig