Databases on GOV.UK
Many GOV.UK applications use a database. See our spreadsheet of applications and database engines for details.
Database engines
GOV.UK uses mostly PostgreSQL and some MySQL for apps that require a relational database. There’s no particular reason why we use both, and the fact that we do is considered tech debt.
GOV.UK uses MongoDB or DocumentDB for apps that require a document database. Whilst there are some big differences between the two, they’re broadly compatible, and we tend to use the term “Mongo” to apply to both.
Finally, GOV.UK uses an ElasticSearch database for search.
Hosting
Instead of running these databases locally on the same instance as the application, they’re hosted in separate infrastructure (with one exception).
Each Postgres and MySQL database runs in its own RDS instance. Whilst RDS instances are capable of hosting multiple databases, we decided to grant each database its own instance in RFC-143.
Mongo databases are hosted either in DocumentDB clusters (managed by AWS) or MongoDB clusters (managed by us on self-hosted EC2 instances). On production, there is currently one DocumentDB cluster for Licensify and one ‘shared’ DocumentDB cluster, each with three instances. There is also one Mongo cluster of three EC2s. We have agreed that we should move apps from MongoDB to DocumentDB.
ElasticSearch is hosted in AWS’s OpenSearch service. It has two types of node - “master” and “data” (instance).
DB admin
We use “DB admin” machines to perform operations on the hosted databases. DB Admin machines are responsible for running the nightly environment data sync, so that production data gets copied to Staging and Integration. In PostgreSQL’s case, the DB Admin machine is also responsible for creating the database and users in the RDS instance (this needs to be done by hand in MySQL).
Each Postgres/MySQL app has its own DB admin node named after the app, e.g. content_publisher_db_admin. The node is defined in govuk-puppet, with root database credentials defined in govuk-secrets (puppet_aws/hieradata/blue/{ENV}_credentials.yaml). Note that the application uses different credentials to connect to the database (username defined in the app, password defined in govuk-secrets in puppet_aws/hieradata/apps/{ENV}_credentials.yaml).
For Mongo databases, there is the db_admin node, which used to manage the relational databases too.
There is no DB admin machine for ElasticSearch (there is a search_admin_db_admin machine, but this is the DB admin machine for the Search Admin application, which uses MySQL). Instead, the search node itself is responsible for its own environment syncing. Every DB admin machine (and search node) uses the same environment sync script.