From: Jorge Montero <jorge_montero@homedecorators.com>
To: kuopo <spkuo@cs.nctu.edu.tw>
To: pgsql-performance@postgresql.org
Subject: Re: how to handle a big table for data log
Date: Mon, 19 Jul 2010 10:37:55 -0500
Message-ID: <4C442B03.2E1C.0042.0@homedecorators.com> (raw)
In-Reply-To: <AANLkTilCP3sGTbHIvrM-ixG-P1Dz6ToqrhbgijY8m8V8@mail.gmail.com>
References: <AANLkTilCP3sGTbHIvrM-ixG-P1Dz6ToqrhbgijY8m8V8@mail.gmail.com>
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@cs.nctu.edu.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: jorge_montero@homedecorators.com, spkuo@cs.nctu.edu.tw
Subject: Re: how to handle a big table for data log
In-Reply-To: <4C442B03.2E1C.0042.0@homedecorators.com>
* 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