Skip site navigation (1) Skip section navigation (2)

Re: Number of tables

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: fabio(dot)lafarcioli(at)molinoalimonti(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: Number of tables
Date: 2009-08-20 23:52:50
Message-ID: 407d949e0908201652g7faf2c64kaa172cc4a23fd8fb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Aug 20, 2009 at 11:18 PM, Craig James<craig_james(at)emolecules(dot)com> 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

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2009-08-21 00:38:55
Subject: Re: Number of tables
Previous:From: Kevin KempterDate: 2009-08-20 23:09:25
Subject: improving my query plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group