Re: Postgres and data warehouses

From: John R Pierce <pierce(at)hogranch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Postgres and data warehouses
Date: 2015-03-08 22:57:55
Message-ID: 54FCD3F3.2010307@hogranch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 3/8/2015 7:40 AM, Nigel Gardiner wrote:
> I'm looking at making a data warehouse to address our rapidly
> spiralling report query times against the OLTP. I'm looking first at
> what it would take to make this a real-time data warehouse, as opposed
> to batch-driven.

we use a hybrid architecture. we have a 'republisher' process that
repeatedly slurps new data from the OLTP database and sends it to the
back end databases, using a 'publish/subscribe' messaging bus.
several back end databases subscribe to this data, and their subscriber
processes insert the incoming data into those OLAP and various other
reporting databases. this way the reporting databases can have
completely different schemas optimized for their needs, and have
different retention requirements than the OLTP database.

this republisher is usually within a few seconds of live new data. in
our case its made fairly easy to track 'new' because all our OLTP
transactions are event-oriented.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2015-03-09 03:24:26 Re: Postgres and data warehouses
Previous Message Tim Uckun 2015-03-08 22:44:13 Benchmarking partitioning triggers and rules