Distributed Data using Retool-hosted database

App Building
1

Hi,

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:

App
Table1
Table2
Table3
Table4
Table5

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 ...

2

It sounds like something that would work but honestly if you’re going to spend that much time doing this I think that the best solution your team won’t like is to break up your primary app into smaller apps or modules.

I think you might be better off dealing with sending users to a different page for each specific function of your app and have your primary app act like a dashboard that links to all the other related apps that will display the data with a smaller number of queries. That way you’ll (1) make those apps easier to maintain (2) reduce the number of simultaneous users since they’re more likely to be spread out over a bigger number of smaller apps thus (3) reduce the load on your retool database.

1 Like
3

I agree with @bonnyag. It would be to cumbersome to maintain this/these apps and I don't think it would speed up as it is still quering the same database(server).

Splitting up the app into multiple functions would be better, but another issue might be your actual postgres queries. Are you using a lot of joins or sub queries?

Maybe you're able to cache some frequently used queries with data that changes less frequently?

1 Like
4

Thanks, @bonnyag & @mbruijnpff for your insights! I'm also linking our performance docs which highlight the splitting up the app idea as well as leveraging query caching