I was reading a few blogs about Prisma’s issue with performance on complex queries and greater needs of RAM beyond free tiers mostly.
Prisma is highly scalable and performant. We have improved a ton since those blog posts were written. It is the main concern here. I am aware that prisma uses more memory cause we use Scala for the server which requires JVM. We will be resolving this in the future, we already have some plans cooking about this internally. We are also working on a binary release of prisma that will allow you to run prisma serverless environments like AWS lambdas which will allow for an extremely scalable architecture. Here is the PR for the binary: https://github.com/prisma/prisma/pull/3695
could somebody with experience or from Prisma team please explain what are its limitations, and what is it not optimized for?
The main advantage of using prisma is that how you can adapt to your concerns. You mentioned Hasura and Postgraphile but these two will lock you in Postgres. But in the real world, each database fits for a different use case. Prisma now supports MongoDB which will blow out these in certain benchmarks. Prisma also provides typesafe database access that will prevent bugs in production.
Also in the future, you will be able to use prisma as a full ORM replacement and the prisma server will be there for scalability: https://github.com/prisma/prisma/issues/3761. We are just not a server that will give you a graphQL API on top of your server with some authentication layer. Prisma is a tool to access databases in an enjoyable and typesafe manner. Hasura and Postgraphile are basically graphql to SQL compilers. You can mix and match stuff like for example you can bootstrap a graphQL API with hasura and you can use prisma to add additional logic as an ORM replacement.