From: | rokj <rjaklic(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: One or more tables? |
Date: | 2007-12-03 10:50:57 |
Message-ID: | d72f2cd0-0ab3-41a6-8eef-4c665791e546@w56g2000hsf.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Allison | 2007-12-03 11:03:40 | Re: power failure.... |
Previous Message | Stefan Niantschur | 2007-12-03 09:21:02 | Re: pgcrypto functions fail for asymmetric encryption/decryption |