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