My question has to do with database optimisation.
My system consists of a Retool-hosted Postgresql database with several tables containing around 50k records each and is currently using 1.0 GB of storage space.
My primary app contains about 100 queries and may be used by 9 or more users simultaneously. The app in question primarily looks up information from tables in the database using relational logic. Ammendments that are made by users to the database records are minor in terms of data content.
My problem is that system performance degrades significantly when many users are using the app. Certain queries seem to timeout and leave their respective onscreen fields displaying no data. This is such a common problem that I have provided the problematic areas with refresh buttons and indicators that confirm whether there is in fact data to be displayed.
I am considering implementing a distributed data model but wanted to run it by you to get your opinion as to whether this 'fix' would in fact alleviate the problem. To illustrate let's suppose that a system comprises a read/write app and a database with 5 tables:
It would be possible to implement a distributed data soulution by mirroring the tables:
AApp BApp CApp
ATable1 BTable1 CTable1
ATable2 BTable2 CTable2
ATable3 BTable3 CTable3
ATable4 BTable4 CTable4
ATable5 BTable5 CTable5 etc...
In this scenario the A, B and C tables are identical to each other. The users are split into three groups and are assigned either AApp, BApp or CApp according to management discretion. All 'select' queries triggered by AApp are only run against A tables, likewise with BApp and CApp. However, 'update' queries are performed against all tables, thus maintaining the mirroring of data, irrespective of which of the apps are being used.
In this scenario the number of 'select' queries directed to any individual table is reduced to 30%, while the number of 'update' queries handled accross the entire system is tripled. Given that the data load of the update queries is negligable, this solution should result in better system performance.
And so to the $64,000 question, will this architecture reduce system latency and 'dropped' queries? If not, is there another strategy that can be employed?
Many thanks ...