DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=ERIwOigURDY6VeaH9beqhbwWcR1TyX7kEvb6WjAhxCHBTDPfUDlFUnTG0oysNO2aVfdk49SOB038j/CNiLlKfD4nrWxzWr02KyQLVDgQ3VMVZ6icsASGe4a2bpk1dycXOPl9Rj3EiJ3d0dHCtTWdkSuOsCXwHV0C+z7kkkQJwsg=;
Message-ID: <229795.24623.qm@web39703.mail.mud.yahoo.com>
Date: Sat, 20 Nov 2010 08:27:51 -0800 (PST)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: best db schema for time series data?
To: pgsql-performance@postgresql.org
In-Reply-To: <AANLkTik5OULt2gK+Z45OMWTOd_kbEPVkMp5fLKTokh2P@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

--- On Fri, 11/19/10, Robert Klemme <shortcutter@googlemail.com> wrote:=0A=
=0A> From: Robert Klemme <shortcutter@googlemail.com>=0A> Subject: Re: [PER=
FORM] best db schema for time series data?=0A> To: pgsql-performance@postgr=
esql.org=0A> Date: Friday, November 19, 2010, 7:16 PM=0A> On Fri, Nov 19, 2=
010 at 10:50 AM,=0A> Louis-David Mitterrand=0A> <vindex+lists-pgsql-perform=
ance@apartia.org>=0A> wrote:=0A> > On Tue, Nov 16, 2010 at 05:28:19PM +0100=
, Harald Fuchs=0A> wrote:=0A> >> In article <4CE2688B.2050000@tweakers.net>=
,=0A> >> Arjen van der Meijden <acmmailing@tweakers.net>=0A> writes:=0A> >>=
=0A> >> > On 16-11-2010 11:50, Louis-David Mitterrand=0A> wrote:=0A> >> >> =
I have to collect lots of prices from web=0A> sites and keep track of their=
=0A> >> >> changes. What is the best option?=0A> >> >>=0A> >> >> 1) one 'pr=
ice' row per price change:=0A> >> >>=0A> >> >> create table price (=0A> >> =
>> id_price primary key,=0A> >> >> id_product integer references product,=
=0A> >> >> price integer=0A> >> >> );=0A> >> >>=0A> >> >> 2) a single 'pric=
e' row containing all=0A> the changes:=0A> >> >>=0A> >> >> create table pri=
ce (=0A> >> >> id_price primary key,=0A> >> >> id_product integer reference=
s product,=0A> >> >> price integer[] -- prices are 'pushed' on=0A> this arr=
ay as they change=0A> >> >> );=0A> >> >>=0A> >> >> Which is bound to give t=
he best=0A> performance, knowing I will often need=0A> >> >> to access the =
latest and next-to-latest=0A> prices?=0A> >>=0A> >> > If you mostly need th=
e last few prices, I'd=0A> definitaly go with the=0A> >> > first aproach, i=
ts much cleaner. Besides, you=0A> can store a date/time=0A> >> > per price,=
 so you know when it changed. With=0A> the array-approach that's=0A> >> > a=
 bit harder to do.=0A> >>=0A> >> I'd probably use a variant of this:=0A> >>=
=0A> >> =A0 CREATE TABLE prices (=0A> >> =A0 =A0 pid int NOT NULL REFERENCE=
S products,=0A> >> =A0 =A0 validTil timestamp(0) NULL,=0A> >> =A0 =A0 price=
 int NOT NULL,=0A> >> =A0 =A0 UNIQUE (pid, validTil)=0A> >> =A0 );=0A> >>=
=0A> >> The current price of a product is always the row=0A> with validTil =
IS NULL.=0A> >> The lookup should be pretty fast because it can=0A> use the=
 index of the=0A> >> UNIQUE constraint.=0A> =0A> Even better: with a partia=
l index lookup should be more=0A> efficient and=0A> probably will stay that=
 way even when the number of prices=0A> increases=0A> (and the number of pr=
oducts stays the same).=A0 With=0A> =0A> CREATE UNIQUE INDEX current_prices=
=0A> ON prices (=0A> =A0 pid=0A> )=0A> WHERE validTil IS NULL;=0A> =0A> I g=
et=0A> =0A> robert=3D> explain select price from prices where pid =3D=0A> 1=
2344 and=0A> validTil is null;=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =
=A0 =A0 =A0 =A0 =A0 =A0=0A> =A0=A0=A0QUERY PLAN=0A> -----------------------=
------------------------------------------------------=0A>  Index Scan usin=
g current_prices on prices=A0=0A> (cost=3D0.00..8.28 rows=3D1 width=3D4)=0A=
> =A0=A0=A0Index Cond: (pid =3D 12344)=0A> (2 rows)=0A> =0A> The index can =
actually be used here.=0A> =0A> (see attachment)=0A> =0A> > The validTil id=
ea is nice, but you have to manage that=0A> field with a=0A> > trigger, rig=
ht?=0A> =0A> Well, you don't need to.=A0 You can always do=0A> =0A> begin;=
=0A> update prices set validTil =3D current_timestamp=0A> =A0 where pid =3D=
 123 and validTil is NULL;=0A> insert into prices values ( 123, null, 94 );=
=0A> commit;=0A> =0A> But with a trigger it would be more convenient of cou=
rse.=0A> =0A> Kind regards=0A> =0A> robert=0A> =0A> -- =0A> remember.guy do=
 |as, often| as.you_can - without end=0A> http://blog.rubybestpractices.com=
/=0A> =0A> -----Inline Attachment Follows-----=0A> =0A> =0A> -- =0A> Sent v=
ia pgsql-performance mailing list (pgsql-performance@postgresql.org)=0A> To=
 make changes to your subscription:=0A> http://www.postgresql.org/mailpref/=
pgsql-performance=0A> =0A=0ALouis,=0A=0ASomeday, as sure as Codd made littl=
e relational databases, someone will put an incorrect price in that table, =
and it will have to be changed, and that change will ripple throughout your=
 system.  You have a unique chance here, at the beginning, to foresee that =
inevitability and plan for it.  =0A=0ATake a look at =0A=0A  http://en.wiki=
pedia.org/wiki/Temporal_database=0A=0Aand =0A=0A  http://pgfoundry.org/proj=
ects/temporal/=0A=0Aand anything Snodgrass ever wrote about temporal databa=
ses.  Its a fascinating schema design subject, one that comes in very handy=
 in dealing with time-influenced data.=0A=0AGood luck!=0A=0ABob Lunney=0A=
=0A=0A