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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
