0

I am using pg-promise in a GraphQL application and because of the nested/iterative nature of the resolvers each HTTP request makes a lot of database queries.

So I am wondering is there is a more effective way to share a connection from the connection pool as the resolvers are collecting data?

I understand that a pg-promise task is only alive for the callback of the function and I don't see any other way to chain the queries (as documented here).

Example

GraphQL Query:

{
  users {
    files {
      name
      date
    }
  }
}

Resolvers example when using Apollo Server

Query: {
    users: (obj, args, context, info) => {
      return context.db.manyOrNone('select id from users')
    }
 }

and 

Users: {
  files: (obj, args, context, info) => {
    const userId = obj.id;
    return context.db.manyOrNone('select * from files where user_id = $1', userId);
  }
}

This will generate lots of SQL queries if there are lots of users for instance.

NOTE

I'm aware of techniques like dataloader to address problems like N+1 Select but I cannot afford to rearchitect this application at the moment and simply being more efficient with database connections would be a huge performance win.

Thank you.

Dan Fairaizl
  • 2,172
  • 2
  • 28
  • 31

1 Answers1

1

Each HTTP endpoint and each database connection from the pool are meant to be asynchronous.

If you attempt to reuse the same database connection across multiple HTTP endpoints, those will be blocking each other, whenever they need access to the database, which is not good.

And if the number of connections in the pool is less than the number of HTTP endpoints that access the database, you've got yourself a poorly-scalable HTTP service. You need the number of connections at least to match that of the HTTP endpoints.

So what you are looking for - sharing a database connection across multiple HTTP endpoints is a bad idea to begin with.

And if you want to group multiple data resolvers within a single HTTP request, you can unify the processing logic within a single task (see Tasks).

There is also manual connection, via method connect, but I wouldn't recommend it for general connection reuse, as it is there for specific cases, can be error-prone otherwise and invalidates the idea of automated connections.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • Thanks @vitaly-t, my question is more referring to sharing a connection for lots of DB calls _within_ a single request, not necessarily across endpoints (thought that get convoluted with graphql). In essence what I'm asking if there is a way to use the Task method, but thats not limited to the callback its invoked with. I can't fit that portion of code inside that callback. Does that make sense? – Dan Fairaizl Feb 14 '19 at 15:21
  • I believe the problem most likely is that you are not fitting the task correctly. Best is to figure out how to do that, since it is essentially the same context. Use of method `connect` would be a work-around of the implementation flaw in this case. – vitaly-t Feb 14 '19 at 15:25
  • Yep that's what I figured. Cool thanks for the sanity check. I'll try using `connect` first and see how the performance is while looking for a longer term solution using tasks. – Dan Fairaizl Feb 14 '19 at 15:42