CD
Cloudflare Developers•2w ago
bloushi

Wrapper cache misses

Help: Zero Cache Hits – Hyperdrive Queries Bypassing Cache Straight to DB! Hey team, I'm scratching my head over this caching mystery. Every query skips the cache and hits my Postgres DB directly (running on a Hetzner VPS), resulting in 0 cache hits for weeks. No performance magic happening here! Quick Context: - Using Hyperdrive for connection pooling. - Drizzle queries are wrapped in a custom runWithDB function (code below). Reason: Prevent CF Workers from opening too many connections - Is this wrapper the culprit? If so, what's the fix to enable proper caching? The Suspect Code: runWithDB Function 
    export async function runWithDB<T>(
      context: unstable_RouterContextProvider,
      callback: (db: PostgresJsDatabase) => Promise<T>
    ): Promise<T> {
      const { HYPERDRIVE } = getBindings(context);
    
      const sql = postgres(HYPERDRIVE.connectionString, {
        prepare: false,
        max: 5,
        fetch_types: false,
        idle_timeout: 5,
        connect_timeout: 5,
        max_lifetime: 60,
      });
    
      try {
        const db = drizzlePostgresJs(sql);
    
        const result = await callback(db);
    
        return result;
      } catch (err) {
        console.log('err', err);
        throw err;
      } finally {
        const ctx = await getExecutionContext(context);
        ctx.waitUntil(sql.end());
      }
    }
    export async function runWithDB<T>(
      context: unstable_RouterContextProvider,
      callback: (db: PostgresJsDatabase) => Promise<T>
    ): Promise<T> {
      const { HYPERDRIVE } = getBindings(context);
    
      const sql = postgres(HYPERDRIVE.connectionString, {
        prepare: false,
        max: 5,
        fetch_types: false,
        idle_timeout: 5,
        connect_timeout: 5,
        max_lifetime: 60,
      });
    
      try {
        const db = drizzlePostgresJs(sql);
    
        const result = await callback(db);
    
        return result;
      } catch (err) {
        console.log('err', err);
        throw err;
      } finally {
        const ctx = await getExecutionContext(context);
        ctx.waitUntil(sql.end());
      }
    }
How I Use It (Example Query): 
    export const getUserByEmailAddress = async (
      email: string,
      context: unstable_RouterContextProvider
    ) => {
      const [user] = await runWithDB(context, async (db: PostgresJsDatabase) => {
        return await db
          .select()
          .from(UserTable)
          .where(eq(UserTable.email, email))
          .limit(1);
      });
    
      return user;
    };
    export const getUserByEmailAddress = async (
      email: string,
      context: unstable_RouterContextProvider
    ) => {
      const [user] = await runWithDB(context, async (db: PostgresJsDatabase) => {
        return await db
          .select()
          .from(UserTable)
          .where(eq(UserTable.email, email))
          .limit(1);
      });
    
      return user;
    };
Any insights or tweaks to get those cache hits working again? Thanks a ton! 🙌
5 Replies
AJR
AJR•2w ago
Ah, it's your prepare: false setting getting you, probably. The message pattern that that causes the driver to generate does not play well with tx-mode poolers at all, and will often disable caching. I'd recommend removing that. Also setting max: 1 unless you really know you want multiple concurrent queries during your Worker requests. That should fix it. Please follow up here if it doesn't and I'll dig further
bloushi
bloushiOP•2w ago
Thanks @AJR, appreciated. However, cache hits are still at a zero, here's the updated config: 
  const sql = postgres(HYPERDRIVE.connectionString, {
    prepare: true,
    max: 1,
    fetch_types: false,
    idle_timeout: 5,
    connect_timeout: 5,
    max_lifetime: 60,
  });
  const sql = postgres(HYPERDRIVE.connectionString, {
    prepare: true,
    max: 1,
    fetch_types: false,
    idle_timeout: 5,
    connect_timeout: 5,
    max_lifetime: 60,
  });
Do you think the wrapper is the issue?
AJR
AJR•2w ago
No, I can't really see how it would be. I already owe a different user some research on cache misses this week. I'll dig into it and see what I can find. Mind sending me your hyperdrive ID?
bloushi
bloushiOP•2w ago
Thanks for your help. Heres the id: 6866fe8f0dc34ffc96b69e57ab91ff00
AJR
AJR•2w ago
Okay, I spent some time, and got to the bottom of this. This is a rather unfortunate interaction between the message patterns that postgres.js uses and the way Drizzle builds its queries. In short, what Drizzle is doing here is taking the existing message pattern that postgres.js uses, with PDH+BES chunks, and creating them with the "unnamed" prepared statement. This is the same as what postgres.js does if you set prepare: false. This breaks many tx-mode poolers (such as pgbouncer, for example), and will cause caching to be disabled in Hyperdrive. Messages with Drizzle+postgres.js: 
message=b"P\0\0\0d\0select \"id\", \"name\", \"age\", \"email\" from \"users\" where \"users\".\"email\" = $1 limit $2\0\0\x02\0\0\0\0\0\0\0\0"
message=b"D\0\0\0\x06S\0"
message=b"H\0\0\0\x04"

// first response from DB here

message=b"B\0\0\0%\0\0\0\0\0\x02\0\0\0\x10john@example.com\0\0\0\x011\0\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"
message=b"P\0\0\0d\0select \"id\", \"name\", \"age\", \"email\" from \"users\" where \"users\".\"email\" = $1 limit $2\0\0\x02\0\0\0\0\0\0\0\0"
message=b"D\0\0\0\x06S\0"
message=b"H\0\0\0\x04"

// first response from DB here

message=b"B\0\0\0%\0\0\0\0\0\x02\0\0\0\x10john@example.com\0\0\0\x011\0\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"
My suggested fix here is to switch to using Drizzle+node-postgres. It also uses an unnamed prepared statement, however it structures its messages such that there's only a single roundtrip, which will have less latency and also works far better with caching. Messages with Drizzle+node-postgres: 
message=b"P\0\0\0\\\0select \"id\", \"name\", \"age\", \"email\" from \"users\" where \"users\".\"email\" = $1 limit $2\0\0\0"
message=b"B\0\0\0)\0\0\0\x02\0\0\0\0\0\x02\0\0\0\x10john@example.com\0\0\0\x011\0\0"
message=b"D\0\0\0\x06P\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"
message=b"P\0\0\0\\\0select \"id\", \"name\", \"age\", \"email\" from \"users\" where \"users\".\"email\" = $1 limit $2\0\0\0"
message=b"B\0\0\0)\0\0\0\x02\0\0\0\0\0\x02\0\0\0\x10john@example.com\0\0\0\x011\0\0"
message=b"D\0\0\0\x06P\0"
message=b"E\0\0\0\t\0\0\0\0\0"
message=b"S\0\0\0\x04"
It's also possible there's some way to tell Drizzle to quit overriding the normal behavior of postgres.js here, and leave in the randomly-generated statement name it usually uses. I couldn't find such an option after some brief searching, though, so I'm sticking with my "try node-postgres" suggestion. FYI @thomasgauvin ^. TLDR drizzle+postgres.js breaks caching.

Did you find this page helpful?