Hmm how does Neon perform with

Hmm how does Neon perform with transactions? Just added a Pooled connections with my Drizzle ORM. Basically the transaction performs 500 inserts in to 1 table, and the result of the main ID is used to create related records that is usually 1 -> 1.2 in relation ratio. but each batch with related data transactions takes about 40-50 seconds to insert. (using Drizzle ORM) R and W comes from the same Neon DB instance

11 Replies

correct-apricot•15mo ago

And the pooled connection is created like this:

import { WebSocket } from 'undici'
neonConfig.webSocketConstructor = WebSocket
const pool = new Pool({ connectionString: env.DATABASE_URL })
export const dbPool = drizzleServerless(pool, { schema, logger: logger })

import { WebSocket } from 'undici'
neonConfig.webSocketConstructor = WebSocket
const pool = new Pool({ connectionString: env.DATABASE_URL })
export const dbPool = drizzleServerless(pool, { schema, logger: logger })

and with imports (as I'm using HTTP for other things):

import { drizzle as drizzleHttp } from 'drizzle-orm/neon-http'
import { drizzle as drizzleServerless } from 'drizzle-orm/neon-serverless'

import { drizzle as drizzleHttp } from 'drizzle-orm/neon-http'
import { drizzle as drizzleServerless } from 'drizzle-orm/neon-serverless'

well if it would matter, here's my whole drizzle-neon file:

import { neon, neonConfig, Pool } from '@neondatabase/serverless'
import { drizzle as drizzleHttp } from 'drizzle-orm/neon-http'
import { drizzle as drizzleServerless } from 'drizzle-orm/neon-serverless'

import http from 'node:http'
import https from 'node:https'
import schema from '~/backend/db/schema.server'
import { env } from '~/env/env.server'
import { FormattedDrizzleLogger } from '~/backend/db/logging/FormattedDrizzleLogger.server'
import { WebSocket } from 'undici'

const httpAgent = new http.Agent({
    keepAlive: true,
})
const httpsAgent = new https.Agent({
    keepAlive: true,
})

const sqlClient = neon(env.DATABASE_URL, {
    fetchOptions: {
        agent: function (_parsedURL: URL) {
            if (_parsedURL.protocol == 'http:') {
                return httpAgent
            } else {
                return httpsAgent
            }
        },
    },
})

const isProduction = process.env.NODE_ENV === 'production'
const enableSqlLogging = false
const logger = enableSqlLogging && !isProduction ? new FormattedDrizzleLogger() : undefined

export const db = drizzleHttp(sqlClient, { schema, logger: logger })

neonConfig.webSocketConstructor = WebSocket
const pool = new Pool({ connectionString: env.DATABASE_URL })
export const dbPool = drizzleServerless(pool, { schema, logger: logger })

import { neon, neonConfig, Pool } from '@neondatabase/serverless'
import { drizzle as drizzleHttp } from 'drizzle-orm/neon-http'
import { drizzle as drizzleServerless } from 'drizzle-orm/neon-serverless'

import http from 'node:http'
import https from 'node:https'
import schema from '~/backend/db/schema.server'
import { env } from '~/env/env.server'
import { FormattedDrizzleLogger } from '~/backend/db/logging/FormattedDrizzleLogger.server'
import { WebSocket } from 'undici'

const httpAgent = new http.Agent({
    keepAlive: true,
})
const httpsAgent = new https.Agent({
    keepAlive: true,
})

const sqlClient = neon(env.DATABASE_URL, {
    fetchOptions: {
        agent: function (_parsedURL: URL) {
            if (_parsedURL.protocol == 'http:') {
                return httpAgent
            } else {
                return httpsAgent
            }
        },
    },
})

const isProduction = process.env.NODE_ENV === 'production'
const enableSqlLogging = false
const logger = enableSqlLogging && !isProduction ? new FormattedDrizzleLogger() : undefined

export const db = drizzleHttp(sqlClient, { schema, logger: logger })

neonConfig.webSocketConstructor = WebSocket
const pool = new Pool({ connectionString: env.DATABASE_URL })
export const dbPool = drizzleServerless(pool, { schema, logger: logger })

So - inserting about 12k records with about 1.2 ratio related records using transactions took about 12 minutes, which feels like either I'm doing sometjing insalely dumb or I haven't understood transactions and pooling correclty

subsequent-cyan•15mo ago

@martin is it possible to try not using the http client and instead use a postgres client. I'd like to understand the http overhead

correct-apricot•15mo ago

I'm getting the slowness from running dbPool. Not sure if I understand correctly if I can switch that one. Anyway it all came from the error of not being able to use transactions: Error: No transactions support in neon-http driver thus I ended up with creating a pooled client using the info from: https://github.com/neondatabase/serverless#pool-and-client and https://orm.drizzle.team/docs/get-started-postgresql#neon The transaction / update script looks like this I've tried various batch sizes as well but the same result

subsequent-cyan•15mo ago

@Mahmoud do you have any insight here since you're much more familiar with Drizzle?

stormy-gold•15mo ago

Hmm @martin Are you connecting to Neon from a long-running server?

correct-apricot•15mo ago

Sorry been out sick a few days, currently this is just running locally off my computer (M3 Pro)

stormy-gold•15mo ago

Are you planning on deploying your app to a long-running server or a serverless platform? If it's a long-running server then you shouldn't be using the serverless driver

correct-apricot•15mo ago

The code is shared between both, so the main app is running serverless, thus the serverless connection, but some background stuff are deployed on another service that's long running (e.g. in my case doing a lot of background data syncing, thus why I wanted DB transactions)

stormy-gold•15mo ago

Hmm, do you mind sharing the query you're using?

correct-apricot•15mo ago

Let me try to rewrite the script a bit so it makes some more sense and not have a ton of weird business logic. But I think as well it might be me using transactions the wrong way But basically, my idea was to use the Pool and transactions for long running jobs in the BG that needs to read and insert a ton of data The normal read/writes for user facing things uses the serverless adapter (as it's all deployed on Vercel anyway)

stormy-gold•15mo ago

makes sense. But the perf issue you're seeing is probably related to the queries themselves. Not something related in particular to Neon

Gaming

Programming

Hmm how does Neon perform with

Did you find this page helpful?