Re: Online DW

On 6/10/2016 1:11 AM, Sridhar N Bamandlapally wrote:
> Is there any feature in PostgreSQL where online DW (Dataware housing)
> is possible ?
>
> am looking for scenario like
>
> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>
> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>
> expecting something like streaming, but not ETL

you'd need to manage that yourself.

I'd probably use a custom ETL job that pulls data from the production
database and sends it to the EDW using a message queue (EMS, JMS, AMQ,
etc). the production database would be partitioned by day, and once a
day drop the 7+ day old partition, while the EDW wouldn't ever drop, and
if its partitioned, maybe by week or month.

It would probably be easier if the the EDW didn't need the latest
transactions, only data an hour to a day old. If the EDW needed
near-live data, then I'd want to be using the same sort of messaging
queue platform (EMS, JMS, AMQ, etc) to send the data to the production
database, so that the EDW feed process could subscribe to the same events.

this sort of pattern works easier if your data is inserted once and not
updated, but it can be made to handle updates, too. the message queue
paradigm ('publish/subscribe') is a very powerful way of implementing
complex distributed systems.

--
john r pierce, recycling bits in santa cruz