Neon•16mo ago

fascinating-indigo

I'm currently using Knex to execute

I'm currently using Knex to execute queries against Neon. I'm noticing that sometimes, it takes almost half a second to release a connection back to the pool. Deos anyone know if this is related to Neon at all?

22 Replies

provincial-silver•16mo ago

I'm not 100% familiar with Knex, do you mind sharing a code snippet?

foreign-sapphire•16mo ago

hi @Mahmoud yes of course. It is a very simple query, and i've attached the table columns to show the table schema as well. Pretty simple

foreign-sapphire•16mo ago

I actually have a much higher level question if that adds more context. My team was previously using SQLite to store some basic data that gets read 99% of the time (we wrote to that db probably once a month to make some minor data updates). Our main API that queries to that db would have ~500ms performance. I've tried to migrate our SQLite db over to Neon since hosting data in-memory no longer scales with us. After the data migration, our API takes up to ~4 seconds now. Our application is hosted in Oregon, as is our Neon instance. I also checked that the tables + their associated indexes are identical between DBs. Neon is looking to be impossible for us to leverage if we can't improve our performance to similar metrics as pre-migration 😦 Would love any advice here

provincial-silver•16mo ago

How are you creating the knexInstance?

foreign-sapphire•16mo ago

here you go!

provincial-silver•16mo ago

I've tried to migrate our SQLite db over to Neon since hosting data in-memory no longer scales with us. After the data migration, our API takes up to ~4 seconds now.

Hmm, that's a big jump. Is the API supposed to return all records from the table directly? Are you on the free tier? What's the database compute size you're using?

foreign-sapphire•16mo ago

@Mahmoud Paid tier, the queries underlying the API fetch just a single row from each of the tables it queries

foreign-sapphire•16mo ago

and yes, it's a huge jump that's making the migration looking impossible 😦

frozen-sapphire•16mo ago

@leosmissingpaw something is amiss if it's taking 4 seconds. A few questions: 1. How many rows are you returning? 2. How long does the query take if you run it in the SQL Editor or using psql (with \timing enabled)? (this will rule our knex and hosting provider to Neon latency) 3. Did you see the sample knex v3 code I shared in your old support thread? (https://gist.github.com/evanshortiss/23c679dbd13a2cb0d0198a9d8b79d211) 4. Did you get anywhere with the cache support issue?

Gist

Using knex@3.x with Neon's HTTP Driver

Using knex@3.x with Neon's HTTP Driver. GitHub Gist: instantly share code, notes, and snippets.

foreign-sapphire•16mo ago

@ShinyPokemon 1. 1 row, they're very simple tables with no joins 2. the query itself matches the time in the SQL editor 3. not yet! Do you think it'll be faster than just the knex driver itself? - the other detail I can provide is that the API actually makes 209 queries. When the data was hosted on SQLite, the cumulative time to make all those queries was ~500ms. Now, it's taking ~4 seconds for the API

frozen-sapphire•16mo ago

1 row returned, but 209 queries? Can you explain that a little further? Are you running those queries in series? If so, that would go towards explaining much of the the delay I bet. If the round trip time of each is 10ms that’s over two seconds right there. Can you batch them? Or run them in parallel? You said the query itself matched the time in the SQL Editor. Which query? Is it one of the 209? That detail has me a bit lost 😅

foreign-sapphire•16mo ago

1 row returned per query!

frozen-sapphire•16mo ago

Gotcha. In that case, how about batching them or parallelising them since you’re using a pool? Or are you doing that already?

foreign-sapphire•16mo ago

Right, it's a little complicated without giving away too much detail on our work. Basically, you can imagine being given an array of input. Each object in the array needs to have a series of math operations executed against it; the values in those math operations are queried from the DB. We're currently looping through the array to do this, but I actually tried to parallelize the requests and don't see any difference in response time from the API... though I'm not sure if it's also a connection issue. This is our connection pool configuration

frozen-sapphire•16mo ago

If you’re looping using regular for with an await within the loop, then it’ll always be in series. Sorry if you already know this, but without more code it’s hard to say more. Could you batch the query values upfront? Like

const inputs = [/* contains some objects that require work */]

// Get required values in parallel upfront
const combinedInputsAndValues = await Promise.all(inputs.map(async (input) => {
  const values = await db.query(`SELECT something`)
  return {
    ...values,
    ...input
  }
}))

// Now loop over each and do the math
for (let i = 0; i<combinedInputsAndValues.length; i++) {
  // math...
}

const inputs = [/* contains some objects that require work */]

// Get required values in parallel upfront
const combinedInputsAndValues = await Promise.all(inputs.map(async (input) => {
  const values = await db.query(`SELECT something`)
  return {
    ...values,
    ...input
  }
}))

// Now loop over each and do the math
for (let i = 0; i<combinedInputsAndValues.length; i++) {
  // math...
}

If the various queries execute quickly in the SQL Editor, then it suggests the bottleneck is somewhere in the app code or due to latency between the app backend and the db. You can rule out latency by doing something like this in app code

const start = Date.now()
const result = db.query(`SELECT 1`)
console.log(`latency is ${Date.now() - start}ms`)

const start = Date.now()
const result = db.query(`SELECT 1`)
console.log(`latency is ${Date.now() - start}ms`)

Oh and when I say batch, I mean something like this. I'm sure Knex has an equivalent API:

import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL);
const showLatestN = 10;

const [posts, tags] = await sql.transaction([
  sql`SELECT * FROM posts ORDER BY posted_at DESC LIMIT ${showLatestN}`,
  sql`SELECT * FROM tags`,
]);

import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL);
const showLatestN = 10;

const [posts, tags] = await sql.transaction([
  sql`SELECT * FROM posts ORDER BY posted_at DESC LIMIT ${showLatestN}`,
  sql`SELECT * FROM tags`,
]);

Basically, one round trip is performed, but it executes multiple queries. So instead of 209 * query_time * round_trip_time it is (209 * query_time) + round_trip_time Final note. If you can't get all the values up front, then you could parallelise like so:

const inputs = []

// Kick off all the operations in parallel, returning promises.
// Wait for them all to complete. Use allSettled if you want to
// wait for all to complete regardless of errors.
const allResults = await Promise.all(
  inputs.map(async (i) => {
    const data = await db.query(`SOME SQL QUERY`)

    // do math
    const results = 1 + 1

    return results
  })
)

const inputs = []

// Kick off all the operations in parallel, returning promises.
// Wait for them all to complete. Use allSettled if you want to
// wait for all to complete regardless of errors.
const allResults = await Promise.all(
  inputs.map(async (i) => {
    const data = await db.query(`SOME SQL QUERY`)

    // do math
    const results = 1 + 1

    return results
  })
)

foreign-sapphire•16mo ago

thanks @ShinyPokemon , is this the correct syntax?

      const [data1, data2] = await knexInstance.transaction(async (trx) => {
        const data1 = await trx.raw(getDataQueryString);
        const data2 = await trx.raw(getData2QueryString);
        return [data1, data2];
      });

      const [data1, data2] = await knexInstance.transaction(async (trx) => {
        const data1 = await trx.raw(getDataQueryString);
        const data2 = await trx.raw(getData2QueryString);
        return [data1, data2];
      });

frozen-sapphire•16mo ago

With knex? I’m not sure. You’ll need to check their docs.

foreign-sapphire•16mo ago

Thanks @ShinyPokemon , really appreciate all the tips! I definitely adopted the concurrency and transaction recommendations. Interesting, I'm seeing very long transaction connection pool release times. Do you happen to know what could be the cause of this?

frozen-sapphire•16mo ago

Do you mean the 433ms? My understanding is that that’s the millisecond difference between the debug print lines, not the time for a release. So that means 433ms passed since the “starting top level” line

frozen-sapphire•16mo ago

https://www.npmjs.com/package/debug

npm

debug

Lightweight debugging utility for Node.js and the browser. Latest version: 4.3.4, last published: 2 years ago. Start using debug in your project by running npm i debug. There are 50724 other projects in the npm registry using debug.

foreign-sapphire•16mo ago

so that means 411 ms difference... which is still quite high? Also confused since the trx93 "starting top level transaction" is 61ms, but the "releasing connection" is 55ms, so not a time difference?

frozen-sapphire•16mo ago

Why not? It's 61ms since the prior line was logged. Yeah, I guess the transaction is doing a lot of work? If it looks like this it's still running in series:

const [data1, data2] = await knexInstance.transaction(async (trx) => {
        const data1 = await trx.raw(getDataQueryString);
        const data2 = await trx.raw(getData2QueryString);
        return [data1, data2];
      });

const [data1, data2] = await knexInstance.transaction(async (trx) => {
        const data1 = await trx.raw(getDataQueryString);
        const data2 = await trx.raw(getData2QueryString);
        return [data1, data2];
      });

Gaming

Programming

I'm currently using Knex to execute

Did you find this page helpful?