Read replicas connections from Massive

[joshuamzm]

Hi!

Here at Airtm we're very happy to use Massive due to its simplicity and embrace of PostgreSQL full potential.

However we're growing faster and we need to handle more demand on our main DB server. So, for us, it's a time where we can spread horizontally than refactoring business logic.

Has anyone considered adding support for read replicas connections from Massive? I mean, there was a feature request on Sequelize like 6 years ago and it's working just fine. We're very hesitant to add such a heavy dependency on our services and we're willing to contribute code to Massive. This issue report is a heads up on any existing effort to sum up, or if there isn't any, to contribute such feature on Massive.

Reviewing Sequelize contribution on read replicas, their only criteria to choose a master or slave replica is if the involved DML query starts with SELECT or not. Another proposal is to send a property in the options object to force use of master replica in any operation, such as find, findOne, count and others on Massive instance.

If you have any thoughts about this please let us know. We'll start to work on some PoC as soon as we find some spare time to try it out.

[dmfay]

There have not been any efforts in that direction on Massive, or, as far as I'm aware, the lower-level driver chain with pg-promise and node-pg. I'd be thrilled to have the option available though!

It sounds like there are two parts: connection configuration needs to include the replicas and the connection process needs to set up the appropriate pools (there's some relevant discussion in #381 you might want to look at, although my proxy experiments didn't wind up going anywhere), and then queries need to be routed appropriately. I think both your ideas sound solid: send SELECTs to replicas by default if you have replicas, and offer a way to go directly to the primary on top. The complicated part is going to be choosing among multiple replicas if there's more than one.

Other than that, glad you've liked using Massive thus far :)

[momirov]

Is there at least a way to have 2 instances of massive running at the same time? One for writes and one for reads? We will manually use the correct one.

[robertrossmann]

I feel that query routing based on query types would be better suited for pgpool instead of re-implementing it at the application layer. 🤔 Application should not be bothered with such details as to how many replicas there are, where are they, which queries should be sent to them etc. There seem to be plenty of solutions for that already, why re-invent the wheel. 😄

[dmfay]

@momirov sure, tracking and managing them is on you but it's perfectly possible to spin up as many instances as you like.

[momirov]

When I try to initialize 2 instances I get a warning:

WARNING: Creating a duplicate QueryFile object for the same file - 
    /src/node_modules/massive/lib/scripts/drop_table.sql
    at files.forEach.file (/src/node_modules/massive/lib/database.js:227:46)
    at initPromises.push.$p.then.files (/src/node_modules/massive/lib/database.js:226:13)

When I try to use second instance I get an error:

TypeError: Cannot read property 'QueryFile' of undefined
    at Object.Database.query [as value] (/src/node_modules/massive/lib/database.js:361:33)
    at Database.options.query (/src/node_modules/pg-monitor/lib/index.js:291:34)
    at Executable.invoke (/src/node_modules/massive/lib/executable.js:84:18)
    at Database.executor [as getSalesForReport] (/src/node_modules/massive/lib/database.js:89:46)

I'm using query file for this query.

Init script:

Promise.all([
  massive(config.db.url),
  massive(config.db.readUrl),
]).then(([db, readDb]) => {
  Repository.initialize(db, readDb);
});

[dmfay]

oh right, I factored the test initialization into a reload instead of swapping instances out a while back 🤦‍♀️

This could get complicated and I don't have time to really dig in at the moment, but if you'd like to try hacking at it feel free!