For facilities that want to use database replication, I think it would be useful if we have two separate database connections in the config file:
One connection for reads (SELECT)
One connection for writes (INSERT, UPDATE, DELETE, REPLACE, ...) and or transactions with mixed reads and writes
Galera replication requires read-write splitting, unless you want to deal with unpleasant errors (see e.g. the article Galera’s big gotcha for MySQL users). For standard, asynchronous primary-secondary replication it also makes sense to split reads and writes, so you can write to the primary, and read from the secondaries. I think you can configure the MariaDB and MySQL Python connectors to access multiple hosts (secondaries in this case) through a pool.
It's possible to use a database proxy to do automatic/intelligent read-write splitting, such as MaxScale (which is proprietary - requires a MariaDB support subscription if you have 3 or more nodes) or ProxySQL (which in my experience is difficult to use and has severe limitations, see e.g. my dba.se question).
Therefore, I think we should do this split in the API so that facilities don't necessarily have to invest in costly or difficult-to-use database proxies.
For facilities that want to use database replication, I think it would be useful if we have two separate database connections in the config file:
SELECT)INSERT,UPDATE,DELETE,REPLACE, ...) and or transactions with mixed reads and writesGalera replication requires read-write splitting, unless you want to deal with unpleasant errors (see e.g. the article Galera’s big gotcha for MySQL users). For standard, asynchronous primary-secondary replication it also makes sense to split reads and writes, so you can write to the primary, and read from the secondaries. I think you can configure the MariaDB and MySQL Python connectors to access multiple hosts (secondaries in this case) through a pool.
It's possible to use a database proxy to do automatic/intelligent read-write splitting, such as MaxScale (which is proprietary - requires a MariaDB support subscription if you have 3 or more nodes) or ProxySQL (which in my experience is difficult to use and has severe limitations, see e.g. my dba.se question).
Therefore, I think we should do this split in the API so that facilities don't necessarily have to invest in costly or difficult-to-use database proxies.