Connection pool size > 1 leads to very erroneous DB behaviour
Heyo,
I am using SvelteKit and Drizzle with a Postgres DB.
When doing a lot of concurrent requests to my backend and with a connection pool with a max values > 1, I get a lot of weird behaviour.
Sometimes after an insert, drizzle returns a correct looking DBO with an ID, but that row has never been inserted into postgres.
The same happens for deletes, the drizzle response tells me that the row has been deleted, but it just remains in the database.
I am not getting any errors, only warnings in postgres about transactions either being already in postgres or not existing. But these warnings to not directly correlate with the stated behaviour.
1 Reply
This conenction pool with the default max settings, causes those random errors:
return new Pool(dbCredentials);
Allowing only one connection works, but is slower:
return new Pool({
...dbCredentials,
max: 1,
});
Allowing only one use per connection also works, but is 3x slower than only having 1 connection with unlimited reuses.
return new Pool({
...dbCredentials,
max: 100,
maxUses: 1,
});
Multiple connection with multiple reuses doesn't work, without causing those wierd errors under high load. But reducing either is a huge bottleneck.
Here's an example of deleting a row, which seems to fail silently:
export async function removeEquipmentById(id: number) {
const toDelete = await db.select().from(equipments).where(eq(equipments.id, id));
console.log('🚀 ~ removeEquipmentById ~ toDelete:', id, toDelete);
if (!toDelete) {
error(400, { message: 'Invalid project ID: ' + id });
}
const result = await db.delete(equipments).where(eq(equipments.id, id));
console.log('🚀 ~ removeEquipmentById ~ result:', id, result);
const stillExists = await db.select().from(equipments).where(eq(equipments.id, id));
console.log('🚀 ~ removeEquipmentById ~ stillExists:', id, stillExists);
}
🚀 ~ removeEquipmentById ~ toDelete: 50194 [
{
id: 50194,
name: 'Sony Alpha II_00',
description: null,
departmentId: 10099,
equipmentTypeId: 2731,
locationId: null,
created: 2025-01-14T11:16:46.000Z,
updated: 2025-01-14T11:16:46.000Z
}
]
🚀 ~ removeEquipmentById ~ result: 50194 Result {
command: 'DELETE',
rowCount: 1,
oid: null,
rows: [],
fields: [],
_parsers: undefined,
_types: { getTypeParser: [Function: getTypeParser] },
RowCtor: null,
rowAsArray: false,
_prebuiltEmptyResultObject: null
}
🚀 ~ removeEquipmentById ~ stillExists: 50194 [
{
id: 50194,
name: 'Sony Alpha II_00',
description: null,
departmentId: 10099,
equipmentTypeId: 2731,
locationId: null,
created: 2025-01-14T11:16:46.000Z,
updated: 2025-01-14T11:16:46.000Z
}
]
And here's a similar example of an insert seemingly being succesufll, until I actually want to access the row outside of the transaction:
export async function insertProject(projectDto: InsertProject, userId: number) {
const formFiles = projectDto.addFiles;
try {
const dbo = await db.transaction(async (trx) => {
const models = await trx
.insert(projects)
.values({ ...projectDto, updatedBy: userId })
.returning();
console.log('Inserted dbo', models);
console.log(
'Select inserted model from db in trx',
await trx.select().from(projects).where(eq(projects.id, models[0].id))
);
return models[0];
});
console.log('Select model outside of trx from db', await db.select().from(projects).where(eq(projects.id, dbo.id)));
return dbo;
} catch (e) {
console.error('🚀 ~ insertProject ~ e:', e);
handleErrorBackend(e);
}
}
Inserted dbo [
{
id: 94450,
dispatcherId: 6640,
created: '2025-01-14 11:34:49',
updated: '2025-01-14 11:34:49'
}
]
Select inserted model from db in trx [
{
id: 94450,
dispatcherId: 6640,
created: '2025-01-14 11:34:49',
updated: '2025-01-14 11:34:49'
}
]
Select model outside of trx from db []