Design pattern recommendation to load balance queries between multiple replicated databases

[shaunakv1]

Hi @vitaly-t ,

(Moving this to discussion from issue (#811) per your recommendation)

This is kind of related to #222 , but as that was closed, wanted to start a new thread for this question. Sorry digging up an old topic. This is more of a question about the design pattern than a technical issue.

In the same way as the linked to issue, say I have four copies of the exact same database. Some refer to this as read replicas. Now the application makes very large number of simultaneous queries that are read-only. Typically in a one database situation we start a connection pool to it, and pg-promise manages the connections over a connection pool.

Now say I want to replicate database connections to different copies of the same database. All connections use connection pools. As all queries are read only and data is replicated, in theory this should be possible. What I am looking for is a recommendation for a good design pattern, recommendation on how to go about handling this code, that would fit in the repository pattern.

Ideally I want to keep the code that makes query generally same, but when we do something like db.any(sql.offshoreWindEnergyLeases, { aoi: aoi }) I want to load balance the queries between say database A, B, C and D on connection level. The code is setup exactly same as the repository pattern you recommend in the wiki.

I also realize there are setups like pg-bouncer and citus, but that's not what I am referring to. I would just like to load balance queries over multiple copies of same database. Each connection will have it's own authentication, pool limit etc. When the application starts I want to be able to define how many databases are in the load balancing setup.

I have tried a few approaches using making connection objects, and then trying to use them in round robin. But I am curious as to how you would recommend designing this code.

Thanks in advance.

[vitaly-t]

What I wrote in the original reply to #811 is valid, but just to clarify:

export const db = pgp({...max: 30, database: 'current-db'}, 'bla-1'}); // your current db object
export const dbr = pgp({...max: 30, database: 'replica-db'}, 'bla-2'}); // your replica db object

And again, do not exceed the total maximum size of all pools, as NodeJS will start slowing down, pass 100 total maximum connection pool sizes. In our example above, the total size is 60, which is reasonable.

Also, in the example, I used dc - Database Context, which you can access throughout the protocol, like from tasks and transactions context. It can be anything, and is there to help you identify when needed which db you are working with on any particular connection, and then execute specific SQL, if needed. See Database.

[vitaly-t]

@shaunakv1 Did you get this sorted?