Re: 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/