Background and Why Knex
Knex.js is a Javascript query builder for PostgreSQL (any many other relational databases).
It is preferable to use a query builder such as Knex to avoid many of the security vulnerabilities of constructing raw SQL as well as to get useful functions for using transactions without dealing with the implementation complexities.
I have used Knex for many Node applications written in Typescript. They have great documentation and even provide type definitions for Typescript users.
Knex: Timeout acquiring a connection
Let’s dive into why you’re probably here. The timeout acquiring a connection has been a pretty common error (unhandled promise rejection) that I’ve run into when using Knex for applications under load. The diagnosis and solution suggested below will also apply to other query building packages.
Unhandled rejection TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
Diagnosis
The error message contains a suggestion to get started debugging. “Are you missing a .transacting(trx) call?” If you are using knex.transaction, maybe you forgot the .transaction(trx) call. Unfortunately, in my experience, this was usually not the problem.
More commonly, this error occurs due to making too many queries at a time or very slow queries. Unfortunately the error message doesn’t include the actual query that is timing out. To help us figure out if this is the case, we can time queries to see if they are taking a long time.
Here’s an example of adding logs to time your knex queries while debugging.
1
2
3
4
5
6
|
function getNodes(conn: Connection, nodeId: string): Promise<Node> {
const getNodesTimingTag = `getNodes ${Math.random()}`;
console.time(getNodesTimingTag);
const node= await conn('nodes').where({nodeId});
console.timeEnd(getNodesTimingTag);
}
|
When you run your application, this should output a bunch of logs looking like:
getNodes 0.7576625381743156: 148.150ms
getNodes 0.6049968597856601: 168.230ms
getNodes 0.02798437817797117: 178.130ms
If the logs indicate that the time for queries to resolve starts out very low and is taking longer and longer, we can confirm that this is a performance issue. Usually we will be looking for the times to increase to 60 seconds which indicates that we are either overloading Knex or Postgres with queries.
These logs also give us insight into which queries are slow which we should prioritize optimizing using the suggested solutions below.
Solutions
Increasing Pool Size
Increasing the pool size may be the fastest solution. An example of when this will work is if you have around a dozen slow running queries that need to run concurrently and the rest of the queries are relatively fast. By increasing the pool size, we allow these slow queries to run while still having the ability to run our fast queries.
However, this may not work if you have so many concurrent queries that all the connections in the pool are being used. In this case, consider decreasing the load on Postgres by limiting concurrency, more efficient queries, caching or using promise queues.
Otherwise, let’s start with increasing the pool size.
According to the docs the default pool size is 0 to 10. You may also need to increase the pool size or memory limit of your Postgres database to support the extra connections.
1
2
3
4
5
6
7
8
9
10
|
var knex = require('knex')({
client: 'psql,
connection: {
host : '127.0.0.1',
user : 'your_database_user',
password : 'your_database_password',
database : 'myapp_test'
},
pool: { min: 0, max: 50 }
});
|
Limit Concurrency
You could just reduce the concurrent calls to Knex if performance is not critical. Even if performance is critical, you don’t want to simultaneously send off a thousand requests since most of those requests will have to wait for a connection to free up and the timeout timer starts as soon as you send the request to Knex.
To limit concurrency use bluebird’s map (or mapSeries) which allows you to control concurrency, instead of the ES6 map.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
import * as bluebird from 'bluebird';
// Use bluebird map with concurrency limited
async function databaseCallsInParallel(nodeIds: string): Promise<void> {
await bluebird.map(
nodeIds,
async nodeId => updateNodes(nodeId),
{concurrency: 10}, // Limit concurrency to 10 at a time
);
}
// Instead of ES6 map which does not limit concurrency
async function unlimitedDatabaseCallsInParallel(nodeIds: string): Promise<void> {
await Promise.all(nodeIds.map(async nodeId => updateNodes(nodeId)));
}
|
Promise Queues - Another Way to Limit Concurrency
Maybe your application operates in bursts and needs to make many queries during these bursts. In this case we can limit the queries sent to Knex at a time by using promise queues. Promise queues enable us to rate limit async operations such as Knex Postgres queries. These queries will still wait in memory but Knex won’t try to acquire a connection until the previous queries are fulfilled. p-queue is the library that we use.
Increasing the pool size and limiting promise queues may just be treating the symptoms of the problem if we need our application to be very performant. On the other hand, maybe those solutions won’t work at all if database performance (CPU usage) is our bottleneck.
For example, if the tables we are querying are very large, we will want to consider creating and utilizing database indices to speed up access to our data.
Caching
You could also reduce database load by reducing the number of database calls by caching in memory. Check out the memoizee library for caching function calls or store query results in variables.
If you are caching static results then this is very straightforward. Otherwise, make sure to handle cache invalidation and keeping the cache updated.
Takeaways
Knex timing out trying to acquire a connection is often due to an overload of queries.
There are some fixes we can make in our application such as increasing pool size or limiting concurrency.
Ideally, we can also implement caching to reduce calls to the database. If we are dealing with large tables, creating new database indices and optimizing queries to use these indices will also reduce load on the database and increase performance.
Hope this was helpful! Leave feedback in the comments section below.
Are you still having trouble debugging the timeout acquiring a connection in your application? Leave a comment below and I’ll get back to you as soon as possible!