Xata•3mo ago

Vercel connections timeout when using a transaction

Hi We are using Drizzle together with the TCP Pool pg client.

import { drizzle as drizzlePg } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

import * as schema from './schema';
import { getXataClient } from './xata';

export const xata = getXataClient();

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 1,
});

export const db = drizzlePg(pool, {
  schema,
});

import { drizzle as drizzlePg } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

import * as schema from './schema';
import { getXataClient } from './xata';

export const xata = getXataClient();

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 1,
});

export const db = drizzlePg(pool, {
  schema,
});

I have also tried

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 1,
  maxUses: 1,
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 1000,
  allowExitOnIdle: true,
});

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 1,
  maxUses: 1,
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 1000,
  allowExitOnIdle: true,
});

which just makes at least the serverless function not run until its own timeout is reached. However, the serverless function times out when we want to use a transaction. This only happens when deployed to Vercel. I tried to check how many connections we have using pgAdmin. But there are none, which I think we are just not allowed to see, because my current one should show up. How can I use transactions on Vercel using Drizzle? This is currently a major blocker. I read some threads here of a 40 connection limit. When reading stuff like this https://neon.tech/docs/connect/connection-pooling#connection-pooling it makes me question if Xata can even handle TCP in serverless functions. Would a dedicated Cluster solve this issue?

5 Replies

tsg•3mo ago

Hi, can you show an example of the transaction you are trying for me to give it a try? I don’t think it should block, unless you are running many of them. Generally using the HTTP protocol would be better from serverless environments, but I assume you don’t do that because you need transactions? The issue with the TCP client and serverless env is that you are paying the connection overhead on each call. So if you can make your app use the HTTP client that will be overall less headache. Xata doesn’t yet do pgbouncer like connection pooling, though, that’s something we want to do but don’t have it yet.

Michael Schaufelberger•3mo ago

Hi @tsg Thank you for the quick answer. So with this config

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 2, // this one
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 1000,
  allowExitOnIdle: true,
});

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 2, // this one
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 1000,
  allowExitOnIdle: true,
});

it seems to be working. Current theory The issue was, that I started a transaction with about 6 queries, but then, inside the transaction, I did other queries without using the transaction instance. I think the issue was, that the non-transaction query, couldn't get a connection because the only one available was already taken.

await db.transaction(tx => {
  await tx.query.....
  await tx.query.....
  await db.query // deeply nested, and not obvious also inside a Promise.all()
})

await db.transaction(tx => {
  await tx.query.....
  await tx.query.....
  await db.query // deeply nested, and not obvious also inside a Promise.all()
})

However, why does it work locally, with the same configuration? I.e. I had

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 1,
});

const pool = new Pool({
  connectionString: xata.sql.connectionString,
  max: 1,
});

locally in Next.js dev mode and it always worked. and after building and starting nextjs, it worked as well.

kostas•3mo ago

The db.query nested within the transaction probably calls a separate client instance which can have unpredictable behavior: there is a warning in the node-postgres docs here: https://node-postgres.com/features/transactions pointing out the same client should be used for all statements within a transaction and "Do not use transactions with the pool.query method.". I suppose that's what db.query within the transaction does? That said mixing up the two clients seems like an anti-pattern.

Michael Schaufelberger•3mo ago

Hmm, wouldn't that just mean that the db.query is just outside the transaction context? While not ideal, this should just be "yet another JS code block" for the tx context, no? And since the db.query uses (probably) a new client, it will make a new connection, hence why the code wasn't working with the max: 1 config. P.S. I need to apologise for my wording about Xata's capabilities in the OP. I was really frustrated... (But it still doesn't make sense that it worked locally, IMO (but that's because of node-pg/drizzle)

kostas•3mo ago

Yes, that is how I interpret it as well. If node-postgres doesn't recommend it, there should be technical reasons for it (might have to do with handling concurrent client contexts). The explanation of blocking when max connections was set to 1 makes sense, since at least 2 were needed to execute the tx and the query call concurrently. Maybe it has to do with strictly enforcing such limitations in node's prod (in vercel) vs dev (local) mode, but that's just a guess. And no worries about wording or anything, we all get frustrated when things don't make much sense 🙂

Gaming

Programming

Vercel connections timeout when using a transaction