Table Partitioning

[talele08]

Currently common components/modules like collection service, records payment transactions across business modules(solutions) like Property Tax, Water & Sewerage Charges, TradeLicense etc. Over time the data size will grow which will lead to issues with query performance. At what stage(data size) should we think of partitioning our tables? Could partitioning the data based on tenantId(ULB/City code) or businessService (Module code) or combination of both help with scalability?

[eGovMan]

We should add this to the load testing scenario.

[vipinvijay1986]

@talele08 even I too completely agree on partitioning the data. I too have seen this problem in e-Chhawani where reports give timeout error as we need to join the data across tables and data is too large to handle in a given time. Making the data to the tenant-specific schema will improve the performance and security but brings its own challenge to the citizen endpoint.
At one point of time, even I was thinking of read-only replicas for tables for common tables to improve performance.

Currently common components/modules like collection service, records payment transactions across business modules(solutions) like Property Tax, Water & Sewerage Charges, TradeLicense etc. Over time the data size will grow which will lead to issues with query performance. At what stage(data size) should we think of partitioning our tables? Could partitioning the data based on tenantId(ULB/City code) or businessService (Module code) or combination of both help with scalability?

[eGovMan]

@vipinvijay1986 Reports a separate issue. Reports does live join from transactional tables and that itself is problematic from a design perspective. Where report is already being used, we should limit data ranges in the UI and ensure proper indexing. In the long term, we need to have separate tables where data is joined and stored for reporting.

@talele08 @kavi-egov - did we make any decision on how to handle this?

[subhashini-egov]

Table partitioning will definitely help with performance and scaling. Depending on the volumes of data we are dealing with, we could:

1. partitioning by tenant ID
2. year/month combination --> we can define this granularity.
3. Combination of both above

We also need a plan for hot/cold data archival. Do we have any built-in mechanisms in DIGIT core for accessing cold data? This should be configurable.

[eGovMan]

@subhashini-egov - "when to partition and how to partition" - should be part of the operations guide.