citus

Best Approach for Developing a SaaS-based Event Analytics

My company is developing a SaaS service to store events and provide analytics through dashboards.

Since we won't have deletes or updates, the idea is to create a columnar-based, OLAP architecture to benefit from compression and latency it provides, and PostgreSQL Citus is one platform we intend to evaluate.

The overall architecture is pretty standard: an API will receive the events and then store them on Kafka in JSON format. Then, those events will be sent to PostgreSQL. Some fields will be "jsonb" data type.

By reading the docs, the best practice is distribute tables by tenant id.

Just wanted to doucle-check a few things and would greatly appreciate someone's input:

  1. Does the architecture described above make sense? Is there anything we should change or pay attention to?
  2. Are there limitations in the number of nodes or shards that can be scaled out for this columnar approach?
  3. Is GIN index supported? (I believe it is, since it's not listed in 'Limitations')

Thanks!

Solution

  • I've used citus for a multi-tenant service and distributing tables by the tenant id worked pretty well.

    The overall architecture that you described makes sense but I'm not sure if someone from outside or at least without some details can give you much more to work with. Sending events through Kafka and processing them to be stored somewhere is a pretty standard way of working with events these days so nothing crazy is happening there.

    There seem to be no limitations to scale out in terms of the number of nodes but what you should keep in mind is that how you set your shards count from the start. re-balancing will lock your tables and can take a while to finish so you want to keep it as small and easy to process as you can. Take a look here for more details: https://docs.citusdata.com/en/v10.2/faq/faq.html#how-do-i-choose-the-shard-count-when-i-hash-partition-my-data

    GIN indexes are supported as they used it in their examples: https://docs.citusdata.com/en/v10.2/use_cases/multi_tenant.html?highlight=GIN#when-data-differs-across-tenants

    Also, note that you won't have failover support in the community version. You have to go with the enterprise version which supports failover and also allows you to rebalance the tables without locking the entire table.

    I wrote a simple service to handle the failover which you can use: https://github.com/Navid2zp/citus-failover