Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C410E1337B66 for ; Fri, 19 Nov 2010 05:50:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 42712-05 for ; Fri, 19 Nov 2010 09:50:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from zenon.apartia.fr (zenon.apartia.fr [82.66.93.83]) by mail.postgresql.org (Postfix) with ESMTP id 28B951337B5B for ; Fri, 19 Nov 2010 05:50:22 -0400 (AST) Received: from sylla.apartia.fr (sylla.apartia.fr [10.0.3.3]) by zenon.apartia.fr (Postfix) with ESMTP id F0C634044C5D9 for ; Fri, 19 Nov 2010 10:50:21 +0100 (CET) Received: by sylla.apartia.fr (Postfix, from userid 1000) id D535C4026AB10; Fri, 19 Nov 2010 10:50:21 +0100 (CET) Date: Fri, 19 Nov 2010 10:50:21 +0100 From: Louis-David Mitterrand To: pgsql-performance@postgresql.org Subject: Re: best db schema for time series data? Message-ID: <20101119095021.GB27168@apartia.fr> Mail-Followup-To: pgsql-performance@postgresql.org References: <20101116105055.GA26129@apartia.fr> <4CE2688B.2050000@tweakers.net> <86zkt9qllo.fsf@mgm.protecting.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <86zkt9qllo.fsf@mgm.protecting.net> User-Agent: Mutt/1.5.21 (2010-09-15) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/319 X-Sequence-Number: 41343 On Tue, Nov 16, 2010 at 05:28:19PM +0100, Harald Fuchs wrote: > In article <4CE2688B.2050000@tweakers.net>, > Arjen van der Meijden writes: > > > On 16-11-2010 11:50, Louis-David Mitterrand wrote: > >> I have to collect lots of prices from web sites and keep track of their > >> changes. What is the best option? > >> > >> 1) one 'price' row per price change: > >> > >> create table price ( > >> id_price primary key, > >> id_product integer references product, > >> price integer > >> ); > >> > >> 2) a single 'price' row containing all the changes: > >> > >> create table price ( > >> id_price primary key, > >> id_product integer references product, > >> price integer[] -- prices are 'pushed' on this array as they change > >> ); > >> > >> Which is bound to give the best performance, knowing I will often need > >> to access the latest and next-to-latest prices? > > > If you mostly need the last few prices, I'd definitaly go with the > > first aproach, its much cleaner. Besides, you can store a date/time > > per price, so you know when it changed. With the array-approach that's > > a bit harder to do. > > I'd probably use a variant of this: > > CREATE TABLE prices ( > pid int NOT NULL REFERENCES products, > validTil timestamp(0) NULL, > price int NOT NULL, > UNIQUE (pid, validTil) > ); > > The current price of a product is always the row with validTil IS NULL. > The lookup should be pretty fast because it can use the index of the > UNIQUE constraint. Hi, The validTil idea is nice, but you have to manage that field with a trigger, right?