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