From: | Kenneth Marshall <ktm(at)rice(dot)edu> |
---|---|
To: | Binand Sethumadhavan <binand(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Homegrown Data Warehouse |
Date: | 2016-07-12 12:59:16 |
Message-ID: | 20160712125916.GM31544@aart.rice.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, Jul 12, 2016 at 08:31:48AM +0530, Binand Sethumadhavan wrote:
> 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
>
Hi Binand,
Since you are running PostgreSQL 9.5 on the data warehouse, have you
considered using postgres_fdw for your data ingestion process. That
might allow you to reduce the impact of keeping it up to date by
managing it more incrementally.
Regards,
Ken
From | Date | Subject | |
---|---|---|---|
Next Message | Keith | 2016-07-12 14:01:05 | Re: Homegrown Data Warehouse |
Previous Message | Binand Sethumadhavan | 2016-07-12 03:01:48 | Homegrown Data Warehouse |