Re: newbie table design question

I just finished doing something very close to this - not quite once per
minute, but close. I started off with an array of integers and after about
a month of it, I'm having to redesign my way out of it. It would have
worked fine, but you just have to be sure that simple searches is all you're
ever going to want to do. I started needing to do some more complex
analysis and it took forever and a day. I'd recommend the normalized
approach with partitioning personally. Just my two cents.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sam Mason
Sent: Monday, June 01, 2009 5:00 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] newbie table design question

On Sun, May 31, 2009 at 11:54:09PM +0800, Andrew Smith wrote:
> I'm a beginner when it comes to Postgresql, and have a table design
question
> about a project I'm currently working on. I have 1500 data items that
need
> to be copied every minute from an external system into my database. The
> items have a timestamp, an identifier and a value.

Not sure if it would help, but maybe an array of integers would be
appropriate. I.e. you'd have the following table structure:

CREATE TABLE data (
time TIMESTAMP PRIMARY KEY,
vals INTEGER[]
);

and you'd be inserting something like:

INSERT INTO data (time,vals) VALUES
('2009-06-01 10:54', '{1,2,3,4}');

This would have much lower overhead than having one row per value and
will make some queries easier and some more difficult. It also relies
upon having the values in the same order each time.

Another thing you can get PG to do would be a constraint like:

CHECK (time = date_trunc('minute',time))

this would ensure that you get at-most one entry per minute and that
it's on the minute. This sort of thing should make some sorts of
queries easier.

--
Sam http://samason.me.uk/