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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Sam Mason Sent: Monday, June 01, 2009 5:00 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
