Re: Homegrown Data Warehouse

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