PostgreSQL Real-Time Analytics

Question

Latest months I am working with PostgreSQL while coming from Microsoft world (SQLServer). My scenario is the following:

1. Transfer data from different databases into single one (same set of tables and structure)
2. Perform lightweight transformations (some simple joins, groups, sums and divisions)

The first part of transferring in real-time between PostgreSQL databases can be achieved using Logical Replication, especially in PostgreSQL 15 where you can select subset of columns of the tables.

For the second part there is the Incremental Materialized View extension for PostgreSQL, but this is currently supporting only Inner-Joins and does not work with Logical Replication, while having lot of other limitations. In addition is not yet implemented on Azure Managed Instances...

Any ideas of how we can archive the second goal?

Answer 1

After a lot investigation around PostgreSQL seems that there no other good choices (with native nature) which can help accomplish the real-time transformation.

What I did in my design was to be able to create three objects within the platform I am developing, one which generates a view for real-time (but slow) and second one calculated tables which are refreshed based on some interval using pg_cron until the Incremental Materialized View extension for PostgreSQL be available also in Azure Managed Instances.