use read-only database replicas when available

[mbjones]

Describe the feature you'd like
Currently metacat is configured with a single database service to a rw instance of postgres, as most of our postgres deployments have used single-instance configurations. With newer postgres tooling (e.g., CloudNativePG), it is much easier to set up PostgresQL clusters that have a single rw instance but mutiple ro replica instances that are load-balanced. These ro replicas can even easily be expanded, and each can handle read requests to the database in parallel. The ro-replicas can also be load-balanced, so a single database service can provide round-robin access to many backend ro instances.

For this feature, expand metacat to be configured to use a rw database service and optionally a ro database connection. The ro service would be used for any REST API calls that don't require writes to the database (e.g., getSystemMetadata(). This would enable Metacat to be configured with far more than 200 database connections, handled in parallel by a suite of ro postgres instances.

Note that which API calls are truly read-only is a little complicated. For example, the getObject(PID) API is primarily a read operation (returning the contents of an object from disk), but it involves both read database calls (e.g., to read access controls and system metadata) and write operations (to record the read event in the event logging tables). So, a more sophisticated implementation of this would require a careful analysis of which connection (rw or ro) should be used. Metacat could probably mix and match and use both types during the course of processing a REST API call.

Note this feature must be optional, in that some deployments won't have postgres replicas available. So, this would be enabled via configuration. For example, our conection pool methods might be modified to 1) be configured to know about both rw and ro service endpoints, and 2) be modified to optionally accept a "readonly=true" parameter on requests for database connections. If that is set, and if the ro replica is available, then it would return a connection to the ro cluster rather than the rw cluster. In the Metacat code, this would likely mean overloading the current DBConnectionPool.getDBConnection method from this:

public static synchronized DBConnection getDBConnection(String methodName)

with something like this:

public static synchronized DBConnection getDBConnection(String methodName, boolean readonly)

along with changes that manage multiple connection pools to the different rw and ro services.

That would allow us to incrementally implement this and use ro connections judiciously in the Metacat code, starting with the parts of the API that would most benefit from ro connections.

Is your feature request related to a problem? Please describe.

Metacat request processing does not scale out well under load, and too many request are left pending when the DBConnectionPool size limit has been reached.

[artntek]

I agree this would be a valuable change. One thing we need to watch for is consistency (good ol' CAP theorem :-); for example, are there cases where someone submits a new dataset (or makes changes) to the RW instance, then immediately queries that info back from an RO instance that hasn't yet been updated?

[mbjones]

Yes, great point. I think focusing on services in this order would make sense:

1. read-only services that don't have a write component
2. read services that have an incidental write (e.g., for event logging)
3. rw services where a resource is both being written and read and consistency would be a major issue -- distinguishing these from (1) and (2) might be harder than it seems