Re: Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 12:02 PM, Marti Raudsepp wrote:

> Which brings me to another important point: don't do lots of small
> write transactions, SAVEPOINTs or PL/pgSQL subtransactions. Besides
> being inefficient, they introduce a big maintenance burden.

I'd like to second this. Before a notable application overhaul, we were
handling about 300-million transactions per day (250M of that was over a
6-hour period). To avoid the risk of mid-day vacuum-freeze, we disabled
autovacuum and run a nightly vacuum over the entire database. And that
was *after* bumping autovacuum_freeze_max_age to 600-million.

You do *not* want to screw with that if you don't have to, and a setting
of 600M is about 1/3 of the reasonable boundary there. If not for the
forced autovacuums, a database with this much traffic would be corrupt
in less than a week. We've managed to cut that transaction traffic by
60%, and it greatly improved the database's overall health.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email