Re: how to handle a big table for data log

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.

I create a primary key (id, data_type, ts) to make my queries more
efficient. The major type of queries would ask the latest data_value of a
data_type by given id and timestamp. For this kind of query, I make the
following SQL statement
*SELECT * FROM log_table WHERE id=[given id] and data_type='longitude' and
(ts = (SELECT max(ts) FROM log_table WHERE id=[given id]and
data_type='longitude' and ts<=[given timestamp]));*
According to my evaluation, its performance is acceptable.

However, I concern more about the performance of insert operation. As I have
mentioned, the log_table is growing so I decide to partition it. Currently,
I partition it by date and only keep it 60 days. This partition is helpful.
But when I partition it by data_type (in my case, the number of data_type is
limited, say 10), the performance of insert operation will be degraded. I
guess this is caused by multiple vacuum/analyze on these partitioned
data_type log tables. However, if I put all data_type logs together, I can
expect that the performance of insert operation will also have degradation
if I want to expand the system to support more mobile phones or more
data_type.

This is my current situation. Please give me some hints to improve the
performance (especially for the insert part).

kuopo.

On Mon, Jul 19, 2010 at 11:37 PM, Jorge Montero <
jorge_montero(at)homedecorators(dot)com> wrote:
> Large tables, by themselves, are not necessarily a problem. The problem is
> what you might be trying to do with them. Depending on the operations you
> are trying to do, partitioning the table might help performance or make it
> worse.
>
> What kind of queries are you running? How many days of history are you
> keeping? Could you post an explain analyze output of a query that is being
> problematic?
> Given the amount of data you hint about, your server configuration, and
> custom statistic targets for the big tables in question would be useful.
>
>>>> kuopo <spkuo(at)cs(dot)nctu(dot)edu(dot)tw> 7/19/2010 1:27 AM >>>
> Hi,
>
> I have a situation to handle a log table which would accumulate a
> large amount of logs. This table only involves insert and query
> operations. To limit the table size, I tried to split this table by
> date. However, the number of the logs is still large (46 million
> records per day). To further limit its size, I tried to split this log
> table by log type. However, this action does not improve the
> performance. It is much slower than the big table solution. I guess
> this is because I need to pay more cost on the auto-vacuum/analyze for
> all split tables.
>
> Can anyone comment on this situation? Thanks in advance.
>
>
> kuopo.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>