Homegrown Data Warehouse

From: Binand Sethumadhavan <binand(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Homegrown Data Warehouse
Date: 2016-07-12 03:01:48
Message-ID: CAFBJCCZjRd9j6=PgduKKa_o_UBPNm0_rDL5-Lc=0PFOP6V6=Dg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

We have a postgresql 9.2 instance pair (master+slave) supporting our
online application.
We have a postgresql 9.5 instance which contains a dump of the above database.

The primary purpose of the dump database is to facilitate analysis. It
is updated from the slave by way of a perl script and psql. Master has
archival policies etc. in place but dump contains complete historic
data.

My problem is that when the dump job runs, queries pause or when heavy
analysis queries run, dump process pauses. This creates significant
problems. One particular use-case has a production application picking
analyzed data from the dump database and running a campaign based on
that.

It is basically a hack to implement data warehouse, ETL, datamarts,
analysis & reporting and campaign management via a set of
perl/shell/sql scripts. It used to work fine, but now with analysis
workloads increasing it is creating problems.

I'd like to know where to go next; because at the moment I'm out of
ideas. I do not have the budget for a paid solution.

Basically, I'm looking for suggestions on architecture and
implementation of such a system. If there are any web references
please mention them too.

TIA,

Binand

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Kenneth Marshall 2016-07-12 12:59:16 Re: Homegrown Data Warehouse
Previous Message Pál Teleki 2016-07-04 19:20:58 Re: Tricky SQL - assistance appreicated. DDL and DML supplied.