3 ms·

Number of queries is not the problem, it's the total query time for the frontend that you're ultimately concerned with. An API is designed to aggregate and for

Number of queries is not the problem, it's the total query time for the frontend that you're ultimately concerned with. An API is designed to aggregate and format data from various data-sources. The speed of your REST API will ultimately come down to the SQL queries that need to be executed and how your RDBMS executes them. Using a naive ORM (such as Django or GraphQL) typically leads to poor performance as you lack control over query execution. Have you written your SQL from scratch and benchmarked slow JOINs? Sometimes doing multiple queries is faster than JOINs (when you only need to fetch related data for a small number of entities for example). Are you using covering indices to accelerate entity lookups? This is probably the main way of speeding up your DB. De-normalizing your schema in certain cases is also a hack to be aware of. Have you tuned caching in your RDBMS? You don't really need Redis when RDBMS can do caching for you (hint in-memory tables are awesome, RAM is cheaper than dev time). Have you tried stored procedures? If you have complicated queries this may be helpful. There's a whole list of things you can do to get great performance out of your RDBMS. Start there instead of introducing another layer of abstraction/tech that will complicate the architecture. I can guarantee that good schema & query design can yield massive improvements.

xwowsersx 4y ago
Thanks for the thorough reply, very helpful! I have looked into some of these, but not all of them and not for all cases. I really do need to look at constructing some of the queries by hand and comparing with the queries constructed by Django's ORM - I know that in many cases the ORM generates terribly inefficient queries. Stored procedures is not something I've tried yet, but will look into it. Any more color or examples how this might work in an application? Are far as RDBMS caching goes, you're referring to the ordinary query caching, related to things like shared buffers in PG, etc? Just want to make sure I'm clear on all the suggestions you raised. Thank you!
- akrymski 4y ago
  You should be able to achieve a 10X improvement in speed just with good query and schema design, but ultimately everything depends on your data layout. Avoid ORMs, GraphQL etc - write raw SQL specific to the views that your clients need, if you care about performance. Low hanging fruit is: - connection pool: your DB can in all probability execute many simple PK lookups at once, make sure you've got a large enough pool of DB connections to let it do this - covering indices: if you are computing over columns A,B,C but looking up by PK you can add a covering index over (PK,A,B,C) which means no disk lookups. make sure all queries are using an index! - joins: ORMs generally don't know how to do joins and use multiple queries instead. You need to get the right balance, for example for HN it could be: get PKs for top 30 articles in one query (one covering index), and lookup their attributes in another query (using a join across N tables where PK IN (1,2,3) which doesn't use a covering index). Reason being: covering indices are expensive, so you minimise the data they hold by splitting up your queries. - de-normalization: if you have very complex joins, de-normalizing your schema may help I wouldn't worry about stored procedures to begin with, unless you have a very complex schema/query plan. For caching you just need to make sure you have allocated enough RAM for your DB to keep indices in memory, the more you allocate the more pages it can cache in RAM. Focus on schema + SQL.
  - eurasiantiger 4y ago
    Raw SQL is not a cure-all; it’s an exchange of design simplicity and team velocity for application performance. Prisma could handle the query generation part somewhat intelligently, at least in the future. Denormalization is not a low hanging fruit. It may seem so because the mass of all the tangled fruits makes the top branch droop down where it can be munched on, but never quite satisfactorily picked.
    - akrymski 4y ago
      I don't agree. Extra layers of abstraction like Prisma / ORMs only add complexity. Sure it means you don't have to learn SQL at all, and that's really the only advantage. But time spent learning an API/ORM is better spent learning SQL.
- eurasiantiger 4y ago
  Word of advice: avoid stored procedures unless you can store them in your codebase and have a CI/CD pipeline integrating their deployment seamlessly with the rest of the DB/API/app. Storing code in a database is a terrible idea unless the entire workflow can support developing, debugging, deploying and monitoring that code.

xwowsersx 4y ago

Thanks for the thorough reply, very helpful! I have looked into some of these, but not all of them and not for all cases. I really do need to look at constructing some of the queries by hand and comparing with the queries constructed by Django's ORM - I know that in many cases the ORM generates terribly inefficient queries. Stored procedures is not something I've tried yet, but will look into it. Any more color or examples how this might work in an application? Are far as RDBMS caching goes, you're referring to the ordinary query caching, related to things like shared buffers in PG, etc? Just want to make sure I'm clear on all the suggestions you raised. Thank you!

akrymski 4y ago
You should be able to achieve a 10X improvement in speed just with good query and schema design, but ultimately everything depends on your data layout. Avoid ORMs, GraphQL etc - write raw SQL specific to the views that your clients need, if you care about performance. Low hanging fruit is: - connection pool: your DB can in all probability execute many simple PK lookups at once, make sure you've got a large enough pool of DB connections to let it do this - covering indices: if you are computing over columns A,B,C but looking up by PK you can add a covering index over (PK,A,B,C) which means no disk lookups. make sure all queries are using an index! - joins: ORMs generally don't know how to do joins and use multiple queries instead. You need to get the right balance, for example for HN it could be: get PKs for top 30 articles in one query (one covering index), and lookup their attributes in another query (using a join across N tables where PK IN (1,2,3) which doesn't use a covering index). Reason being: covering indices are expensive, so you minimise the data they hold by splitting up your queries. - de-normalization: if you have very complex joins, de-normalizing your schema may help I wouldn't worry about stored procedures to begin with, unless you have a very complex schema/query plan. For caching you just need to make sure you have allocated enough RAM for your DB to keep indices in memory, the more you allocate the more pages it can cache in RAM. Focus on schema + SQL.
- eurasiantiger 4y ago
  Raw SQL is not a cure-all; it’s an exchange of design simplicity and team velocity for application performance. Prisma could handle the query generation part somewhat intelligently, at least in the future. Denormalization is not a low hanging fruit. It may seem so because the mass of all the tangled fruits makes the top branch droop down where it can be munched on, but never quite satisfactorily picked.
  - akrymski 4y ago
    I don't agree. Extra layers of abstraction like Prisma / ORMs only add complexity. Sure it means you don't have to learn SQL at all, and that's really the only advantage. But time spent learning an API/ORM is better spent learning SQL.
eurasiantiger 4y ago
Word of advice: avoid stored procedures unless you can store them in your codebase and have a CI/CD pipeline integrating their deployment seamlessly with the rest of the DB/API/app. Storing code in a database is a terrible idea unless the entire workflow can support developing, debugging, deploying and monitoring that code.