Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2 - Mailing list pgsql-admin

Hi All,

We have a reporting application to consume transactional log created by a billing system. The flow of data in ETL framework is from TRANSACTION_LOG->STAGING_TABLES->FACT_TABLES->AGGREGATE_TABLES.

There are around 15 aggregate tables to be populated from these staging tables. Number of records for a one time will be 1.2 million records on an average. We are expecting anywhere near to 10 such processes in a day.

Problem:

-          Staging table population happens in expected time. Anywhere from 10-15 minutes for each process.

-          Aggregation process almost never completes which is a set of 15 insert queries. Entire process happens in a single commit (population of staging tables and aggregation).

-          When I run these 15 queries individually, it happens in no time.

Could you be kind enough to explain how differently queries run when in a “single commit” as opposed to running each of them individually?

----

The system is CentOS 5.5, Postgres installed is 9.1.2.

There are 3 postgres servers installed, each at 5432, 5433 and 5434 ports. It is a 16GB RAM with 3 NetAPP storage box mounted with 500GB each.

Postgresql.conf remains untouched, except for log writing which has been made to “all”.

----

Any info greatly appreciated.

Thanks in advance,

Akash.