Message-ID: <4C4F4C06.1050404@agliodbs.com>
Date: Tue, 27 Jul 2010 14:13:42 -0700
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: kuopo <spkuo@cs.nctu.edu.tw>
CC: pgsql-performance@postgresql.org
Subject: Re: how to handle a big table for data log
References: <AANLkTilCP3sGTbHIvrM-ixG-P1Dz6ToqrhbgijY8m8V8@mail.gmail.com>
	<4C442B03.2E1C.0042.0@homedecorators.com>
	<AANLkTilcCUP2ohhotmvoOoDlp2j1OiqwcZusEvYjjfzS@mail.gmail.com>
In-Reply-To: <AANLkTilcCUP2ohhotmvoOoDlp2j1OiqwcZusEvYjjfzS@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 7/20/10 8:51 PM, kuopo wrote:
> Let me make my problem clearer. Here is a requirement to log data from a
> set of objects consistently. For example, the object maybe a mobile
> phone and it will report its location every 30s. To record its
> historical trace, I create a table like
> /CREATE TABLE log_table
> (
>   id integer NOT NULL,
>  data_type integer NOT NULL,
>  data_value double precision,
>  ts timestamp with time zone NOT NULL,
>  CONSTRAINT log_table_pkey PRIMARY KEY (id, data_type, ts)
> )/;
> In my location log example, the field data_type could be longitude or
> latitude.

If what you have is longitude and latitude, why this brain-dead EAV
table structure?  You're making the table twice as large and half as
useful for no particular reason.

Use the "point" datatype instead of anonymizing the data.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com