Aurora Serverless – a migration story
On our team we swapped databases 6 times in a year. We’ve landed on Aurora Serverless V2 – was it worth it?
Since our migration from Mongo to Postgres in 2018, at the Guardian the search for the perfect database hasn’t stopped. In the last year on the Investigations and Reporting team we’ve spent a lot of time trying out AWS’s Serverless relational databases. We hope that by sharing our learnings we’ll help others avoid the pitfalls we hit along the way.
Application characteristics
The database in question is for a tool called ‘Lurch’. Lurch assembles different public data sources into a single search engine. These data sources vary in size (0.5 to 8GB) and how frequently they’re published (from daily to monthly).
Unfortunately, unlike many Guardian projects, Lurch is currently closed source, so I’m can’t link directly to PRs in this post.
The tool is only accessible to Guardian staff, with a small number of very active users, typically using it within UK working hours.
The whole point of this application is to provide journalists with quick answers to queries that would otherwise have been too time consuming to answer. With that in mind, good read performance is important.The most commonly used databases at the Guardian are Elasticsearch, Postgres and DynamoDB.
Probably any of these databases would have done the job. We thought Postgres was a good fit because the structure of the data we ingest is very predictable and we need to perform queries across multiple datasets (so lots of JOINs). Existing Postgres expertise on the team was also a consideration.
The tool links lots of different data sources together, so while we considered using a graph database, the data coming out of Lurch is often destined for a journalists’ spreadsheet, so we found it helpful to think about data in terms of tables.
Most of the challenges discussed in this post are to do with high write volume. On the read side we’ve been making use of the full text search functionality in Postgres (edge ngrams, tsquery/tsvector) and have been very happy with it. Any slowness in search queries has always been traced back to a missing index rather than a lack of resources on the database instance.
Application Architecture
This is what Lurch looks like:
