Posts

Showing posts from March, 2018

Implementing a "distributed" reporting server using some of postgres10 features.

Today i will try to show how strong Postgres 10 is by combining different features in order to create a "distributed" reporting server. The features that i will be using are :
Logical ReplicationPartitioningForeign Data WrappersTable Inheritance The scenario that we want to implement is the following :
We have one central point for inserts, that we will call Bucket, bucket is partitioned by range yearly.In my example we have 3 partitions for 2016, 2017, 2018 and each partition is logically replicated to 3 data nodes, each responsible for 1 year of data. Finally we have a reporting proxy that is responsible for all selects and connects to each node through foreign data wrappers. The setup consists in 5 docker containers that have the following roles.
10.0.0.2, bucket, insert / update / delete10.0.0.3, node2016, data holder for 201610.0.0.4, node2017, data holder for 201710.0.0.5, node2018, data holder for 201810.0.0.6, reporting proxy, main point for selects 

Now lets start with t…