How to efficiently scale a NestJS application with TypeORM for large datasets?
I'm trying to configure I'm currently working on a NestJS application that uses TypeORM with a PostgreSQL database. As our user base grows, we're noticing severe performance degradation when querying large datasets. Specifically, we have a `User` entity with around 1 million entries, and the following query takes over 30 seconds: ```typescript const users = await this.userRepository.find({ where: { isActive: true }, take: 100, skip: page * 100, }); ``` I’ve tried indexing the `isActive` field and optimizing the database configuration, but it hasn't significantly improved performance. Additionally, we're using pagination to load users, but I'm concerned that the current approach won’t scale as the dataset grows. To help diagnose the question, I enabled query logging in TypeORM, and the generated SQL looks like this: ```sql SELECT "User".* FROM "User" WHERE "User"."isActive" = true LIMIT 100 OFFSET 200; ``` It seems that the execution plan is not efficient as it scans the entire table before applying the filter. I also considered using `queryBuilder`, but I’m not sure if it would yield a better performance. Does anyone have suggestions on how to adjust our queries or any best practices for scaling NestJS applications using TypeORM? Are there specific patterns or configurations we should consider to handle large datasets more efficiently? I'm using Typescript 3.9 in this project. What's the correct way to implement this? Hoping someone can shed some light on this. I'm working in a CentOS environment.