So one of the ways people solve issues with slow databases is through read-only replicas – you can have a database running as a read-only replica of the master. (And possibly serving double-duty as a hot spare if the master crashes.) There’s replication lag, so you have to be mindful of it, but a pristine database that’s not under any load at all and is only 10-50 milliseconds behind the writer can make a lot of searching and reporting tasks easier.
Ideally it would take this form:
- A second set of database fields (fine if it can’t be set automatically but it would be nice if it could) for the second datasource, configured as a second parallel connection pool.
- SELECT queries inside the codebase could be flagged to use the replica if available, and would use the master if the replica wasn’t configured. Thought would have to be given to exactly which queries could use this indirection – basically it’s not appropriate for anything that has to immediately reflect a change on-click because sometimes pages can actually beat the replication lag, but it’s fine for a patient search or some sort of report or export. (Not sure if it’s a good match for a calendar or not, depends on how things show up on it.)
It won’t help everybody – anybody using only a single database node, and let’s not lie, that’s “most people”, wouldn’t be able to leverage it, but it’ll help multi-facility performance at the high-end. Amazon Aurora has some charming trickery where every instance has a writer and reader endpoint and it Just Magically Works even if there’s only one node in the cluster or if you change the scaling.