Support for read-only database replicas

[egmar]

ISSUE TYPE

• Feature Idea

SUMMARY

It's a common practice to have read-only replicas for databases. This brings benefits in splitting the write operations from read operations, as well giving option for a clever routing of various database operations, e.g. long running analytical queries would be done against read-only replica.

Read-only replicas are generally available as an option in most cloud providers, for example on AWS https://aws.amazon.com/rds/features/read-replicas/

On Django framework level this is possible by using database routers https://docs.djangoproject.com/en/3.1/topics/db/multi-db/#database-routers and most naive implementation could look like:

class CustomRouter:
    def db_for_read(self, model, **hints):
        return 'replica'

    def db_for_write(self, model, **hints):
        return 'master'

with following in app configuration

DATABASES = {
    'default': {},
    'primary': {
        'NAME': 'master',
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'USER': 'user',
        'PASSWORD': 'password',
    },
    'replica1': {
        'NAME': 'replica',
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'USER': 'user',
        'PASSWORD': 'password',
    },
}
DATABASE_ROUTERS = ['path.to.CustomRouter']

Overall, using read-only replicas could bring benefits for UI/API-related queries (browsing inventory, job templates, old job logs) and analytics. It could help improve performance in certain use cases, in addition to the work in: https://github.com/ansible/awx/issues/9039

[wenottingham]

This is something we've thought about architecturally, but it involves significant changes to how we deploy the database (with added complexity), and adds some restrictions on how that can work.

It's probably not something we'll look at in the very short term.