Re: how to handle a big table for data log

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