database best practices

nelson artunduaga

Hello, we are reorganizing our databases, since we saw the need to make our processes more optimal, I am not really an expert in databases, I would like to have some opinion on what would be the best practices to avoid long times in the searches from our Front. apart from this, I have some questions: 

1. Is there any best practice to implement from now on where the data is segmented in some way? so that in the future if there is a massive amount of data it does not take a request to search for a specific data? 

2. When deciding which country to choose in Xano in our account which one should we choose? taking into account that we are located in Colombia south america and our clients also, but, we use webflow and Wized (hosted in germany) which should be the best location to choose?

3. the fact of using Xano already optimizes in some way our databases to minimize time in the future with large amounts of data?

4. is it better to perform a complicated logic in one request, where different tasks are performed at the same time (in the same request) or is it better to split this complicated logic in 2 or 3 different requests? 

Ray Deck

These questions boil down to how one gets data from the tables to your users. Certain aspects depend on more details than all this, but in general:

1. Position your data to be in proximity to your users. Don't worry about the proximity to your webflow or wized servers. In a web context, requests to the back-end come from the user's browser. The "front end" is not a relay.
2. The performance of a relational database like Xano scales when you use indexes. Indexes will (with some exceptions) only work on exact match queries. For example, everything where date = 2023-01-01 will be fast at scale, while time > 2022-12-31 11:59:59 and < 2023-01-02 00:00:00 will perform poorly. It's fine to have custom query critera that the index doesn't use - it's job is to do the major cut-down so subsequent analysis is on a smaller set of records. THat's the magic of a database at scale.
3. Fewer network round-trips are faster than more. That means rolling more of the question into a single request, rather than cutting it up across API endpoints.

Finally, I'd note that there is only one point of performance that matters: the bottleneck. It's useful to develop quickly to discover where the user experiences a slowdown. Then you can focus your attention on just that part of your system. It can be that their perceived experience is not in line with ideas of back-end performance. For example, in certain circumstances cutting up a complicated back-end request to deliver some value sooner might make the whole thing take longer, but reduce the user's perception of delay.

Data design is an art! Let me know if you have more qs!

nelson artunduaga

Heyy @Ray Deck

First, I would like to thank you for your time, for answering every doubt, it is really much clearer how I should structure in a more optimal way my backend and my base, I am really grateful for your help. !