Re: One or more tables?

On 3 dec., 01:18, ron(dot)l(dot)john(dot)(dot)(dot)(at)cox(dot)net (Ron Johnson) wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 12/02/07 14:58, Usama Dar wrote:
>
>
>
> > On Dec 2, 2007 6:35 PM, rokj <rjak(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
> >> Hi.
>
> >> For an example let me say that I have a big (over 1 million) user
> >> "base". Then every user does a lot of inserting/updating of data.
> >> Would it be better to create different tables for insert/updating for
> >> every user or would it be better just to have one big table with all
> >> data (tables would have of course the same columns, ...). How do you
> >> cope with this kind of things?
>
> >> 1.example (1 enormous table)
> >> tablename (id, user_id, datetime, some_data)
>
> >> 2. example (a big number of tables)
> >> tablename_user_id( id, datetime, some_data)
>
> > Although there isn't enough information in the email, but instead of
> > creating a separate table for every user, you could use one table ,
> > partitioned on userid, that would , however, add a maint overhead whenever
> > you add a new user.
>
> Cluster by *range* of user ids, and preallocate some number of
> tablespaces.
>
> - --
> Ron Johnson, Jr.
> Jefferson LA USA
>
>

I was just looking http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
which is something you said about and which is something I was looking
for.

So if I do table like:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);

CREATE TABLE measurement_y2004m02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE
'2004-03-01' )
) INHERITS (measurement);
...
..
.

I do SELECT with:
SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2004-02-01';

-------------------------

Personally I think this is really powerfull thing, since it saves a
lot of resources especially in big "environments".

Regards,

Rok