Performance degradation in Node.js application while using Sequelize with PostgreSQL for large datasets
I just started working with I'm trying to figure out I'm experiencing important performance optimization in my Node.js application while using Sequelize as an ORM with PostgreSQL. The application handles large datasets, specifically over 100,000 records, and the queries to fetch data are taking much longer than expected. I have tried to optimize my queries by selecting only the necessary fields and using pagination, but the response times are still around 5-10 seconds for a simple SELECT query. Here's an example of the query I'm using: ```javascript const { User } = require('./models'); async function fetchUsers(page, limit) { try { const users = await User.findAll({ attributes: ['id', 'name', 'email'], limit: limit, offset: (page - 1) * limit }); return users; } catch (behavior) { console.behavior('behavior fetching users:', behavior); throw behavior; } } ``` Despite adding indexes to the `name` and `email` columns in the PostgreSQL database, I still see a notable delay. I also tried using raw queries with Sequelize, but the performance didn't improve significantly. Furthermore, I'm not sure if the way I'm handling connections to the database might be affecting performance. I set the connection pool size to 10, but I'm working with a warning in my logs: `Connection pool is exhausted, create more connections`. Here’s how I set up the Sequelize connection: ```javascript const { Sequelize } = require('sequelize'); const sequelize = new Sequelize('database', 'username', 'password', { host: 'localhost', dialect: 'postgres', pool: { max: 10, min: 0, acquire: 30000, idle: 10000 } }); ``` I would appreciate any suggestions on further optimizing my queries or adjusting the Sequelize configuration for better performance. Are there specific practices in using Sequelize with large datasets that I might be missing? Any advice would be greatly appreciated. Hoping someone can shed some light on this. This issue appeared after updating to Javascript stable. Could someone point me to the right documentation?