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 Replication Partitioning Foreign Data Wrappers Table 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 / delete 10.0.0.3, node2016, data holder for 2016 10.0.0.4, node2017, data holder for 2017 10.0.0.5, node2018, data holder for 2018 10.0.0.6, reporting proxy, main point for