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