From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: best db schema for time series data?
Date: Fri, 19 Nov 2010 12:13:58 -0500
Message-ID: <87mxp5p76x.fsf@cbbrowne.afilias-int.info> (raw)
References: <20101116105055.GA26129@apartia.fr>
<8762vxfcqb.fsf@cbbrowne.afilias-int.info>
<20101119094624.GA27168@apartia.fr>
vindex+lists-pgsql-performance@apartia.org (Louis-David Mitterrand)
writes:
> On Tue, Nov 16, 2010 at 11:35:24AM -0500, Chris Browne wrote:
>> vindex+lists-pgsql-performance@apartia.org (Louis-David Mitterrand)
>> writes:
>> > 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?
>>
>> I'd definitely bias towards #1, but with a bit of a change...
>>
>> create table product (
>> id_product serial primary key
>> );
>>
>> create table price (
>> id_product integer references product,
>> as_at timestamptz default now(),
>> primary key (id_product, as_at),
>> price integer
>> );
>
> Hi Chris,
>
> So an "id_price serial" on the price table is not necessary in your
> opinion? I am using "order by id_price limit X" or "max(id_price)" to
> get at the most recent prices.
It (id_price) is an extra piece of information that doesn't reveal an
important fact, namely when the price was added.
I'm uncomfortable with adding data that doesn't provide much more
information, and it troubles me when people put a lot of interpretation
into the meanings of SERIAL columns.
I'd like to set up some schemas (for experiment, if not necessarily to
get deployed to production) where I'd use DCE UUID values rather than
sequences, so that people wouldn't make the error of imagining meanings
in the values that aren't really there.
And I suppose that there lies a way to think about it... If you used
UUIDs rather than SERIAL, how would your application break?
And of the ways in which it would break, which of those are errors that
fall from:
a) Ignorant usage, assuming order that isn't really there? (e.g. - a
SERIAL might capture some order information, but UUID won't!)
b) Inadequate data capture, where you're using the implicit data
collection from SERIAL to capture, poorly, information that should
be expressly captured?
When I added the timestamp to the "price" table, that's intended to
address b), capturing the time that the price was added.
>> The query to get the last 5 prices for a product should be
>> splendidly efficient:
>>
>> select price, as_at from price
>> where id_product = 17
>> order by as_at desc limit 5;
>>
>> (That'll use the PK index perfectly nicely.)
>>
>> If you needed higher performance, for "latest price," then I'd add a
>> secondary table, and use triggers to copy latest price into place:
>>
>> create table latest_prices (
>> id_product integer primary key references product,
>> price integer
>> );
>
> I did the same thing with a 'price_dispatch' trigger and partitioned
> tables (inheritance). It's definitely needed when the price database
> grow into the millions.
>
> Thanks,
The conversations are always interesting! Cheers!
--
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/x.html
FLORIDA: If you think we can't vote, wait till you see us drive.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: best db schema for time series data?
In-Reply-To: <87mxp5p76x.fsf@cbbrowne.afilias-int.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox