Thoughts on production database clusters for a global app?

Phoenix Forum Questions / Help
,
1

Can anyone give examples here and/or links to articles about a typical database cluster/group for an app that is designed to roll out initially in one country and then be hosted globally after 3 months of initial testing, with databases for:

  • Standard user identity eg user auth based on email + password and social network logins,
  • User profiles eg full name, short bio, and an avatar,
  • Should host images - max of 13 images per user’s ‘post’ (most users will have 3 or less ‘posts’ unless I extend the app to SaaS and companies as account types). This includes user avatar too.
  • Search - via elasticsearch or manticore,
  • Ecommerce - not a big feature set, just tied to user ‘posts’ and no shopping cart.
  • Admin of user accounts,
  • Backups.
  • Anything you think I may have missed.

* quotes used around ‘post’ because that’s as close as I can explain here.

Even questions like ‘Should I have a separate db for ‘post’ creation vs storage of post content’ eg a user fills in a wizard/multipage form entering text and images as part of onboarding or later - should I keep the tables with the content for dropdowns for that form in a seperate database from the place I store the posts created from that form, are not ‘intellectually paralysing’ but are forefront in my planning and I’m keen to learn from others here.

Thanks

PS Please, no replies with ‘I’m suspicious that this is premature optimisation’… or similar, because that’s a relative term, and I have the concept and time and resources to build an app that will be very popular, unique, and profitable globally… and do not want to stunt its growth by hitting critical issues. This is why I learned Elixir and Phoenix etc because beyond loving functional programming, they use the beam etc and can scale well, and I’m trying to avoid getting investors to get this app launched.
I’m struggling to find information and think about how to build with the right databases to scale up from the start. YES, everythhing is app specific, and there’s a lot of engineers blog posts from Twitter to Instgram out there, and there will be unforeseen changes, but a lot of apps have common architecture, and there’s a huge range of experience across members here, so I’m keen to read as many opinions as possible - and do not expect any ‘perfect answers’ (unlike stackoverflow that is built for that or leans towards it and general questions are often shot down by people trying to prove they’ve rote learned engineering mantras).

2

Questions about scaling are usually far too broad to be answered meaningfully as everything depends on the type of load and access pattern of your application. (I re-read the question after writing all this and realized I mentally translate global app to mean 3-penta-zillion requests per second which may not have been your intention. This may be useful to someone else, so leaving it up).

I’ve been out of the game for a while, but let’s take a quick look at authorization for instance and assume that you have a few million MAU and also need to support 10k qps api calls.

In general, most companies that I’ve been involved with have used RDBM’s for user authorization data with various replication. It’s a safe bet, especially if you’re planning to use it for billing also (unless you’re going to be doing usage type billing on large scale where you don’t care about small failures, in which case, you might want to consider Cassandra, but I digress).

So there are few issues regarding user data. The first is that you’ll need to know which geo-region to store it. One common pattern is to first have the user input their “email”. This connects to a datastore (depending on your legal department you just connect to the same datastore as it contains no PII or you have do some other shenanignans) and then figure out where the actual user data is. This could redirect to a new url - us.your-app.com, europe.your-app.com, etc. If you’re going to do global data, it’s likely you have to comply with all the various data laws of all the countries regarding user data.

In any case, the user initially supplies their email, you connect to an initial store which contains an email and the geo-location that email is pinned to. At that time, you either redirect to that via url, or you are using anycast or what not and just change the backend database to ensure that the db is location is connected appropriately (and worry about the number of concurrent db connections later).

So at that point, you can hash/salt the password and compare it with the value in the database. Great. But what about all those pages that are getting called? Do you really want to check a session token to ensure that the user is logged in? And how do you handle multiple groups?

The options that I recall debating over:

  1. Have a signed session token with an expiry. JWT is a form of this and there are various other forms of refresh/access tokens.
  2. Have an opaque secret you check in either a cache or a database.
  3. Change the business requirements so that the budget for authorization stays under $100k/month. (Yes, it was like $80k/month with the dev/stage/prod environments with all the iops, replication, backups, etc which is still cheaper than Okta - including having the development team)

Either way - you’ll have to hit the cache or the database to either verify a token or a blacklisted token. I’ve had to deal with enough load that a single threaded redis had issues. And you don’t really want your authorization to fail. At that level of traffic, 99.99% means 1/10,000 requests fail. That’s one request per second which means that someone is going to be irritated with your service. So you’ll have to implement internal retries also, before returning failures to the clients with random exponential backoff..

You’ll need at least one replica for authorization. If you’re going to use bi-directional replication, I would recommend mysql. Otherwise, postgresql. Of course, I’ve never used bi-directional replication at that scale and just dealt with failover.

Backup of the data layer are also a bit annoying. Depending on the database you’re using, it could cause a partial lock and slow down traffic which could cause an outage. Make sure you take it off the slaves.

GDPR laws for deletion also include backups. The best you can probably do if a user deletes their data is just wait for the backups to rollover past their delete day. So a user data deletion would take, for instance, 90 days to actually be 100% complete.

Now for images, they belong in S3 in the correct region. User profiles can be a separate service that has it’s own datastore. These are not mission-critical and are 90% reads.

Search - Used AWS elasticsearch for years and ran across almost every limit. I’ve blocked that entire period out of my mind now. Don’t worry though, rolling your own also sucks equally.

Dealing with everything on a global scale, is, well, just irritating. There’s replication latency when you go across the continent. There’s the SSL connection performance. There’s the anycast BGP routing accidentally going through Russia.

So to summarize:

  1. Each country has their own data laws.
  2. Scaling is going to depend for each service. Split your reads/writes where possible. Caching and cache invalidation are important. Keep your replication limited to the geo-region.
1 Like
3

Also, as an addendum, when in doubt, use postgresql.

It supports pretty decent full-text search, queue, had plugins for time-series, clustering, json, analytics.

As a converse, if you’re thinking of using mongo, use postgresql instead.

4 Likes