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 indexes at 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 hitlist is 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 tend to be transitory, and the typical operation is to discard the entire list and create a new one. Sometimes the user will sort 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 only makes it worse. With Postgres, the repeated large delete/insert makes for tables that need a lot of vacuuming and index bloat, further hurting performance.
Craig
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
