Managing HTTP requests at different scales

In a recent article I set up a small (20 EUR/mo) VM with a basic personal website. Even at its busiest, I don't expect more than 10 people visiting it per day. This got me thinking though: what would it take to handle 1000 people per day? Or even 1 million visitors per day? Or maybe even per second??

This challenge becomes very different depending on what the users are actually requesting. It's easy enough to just serve a cached image, but it gets a lot harder (and more interesting) if we are setting up a web API with authentication, business logic and access to a database.

1 req/sec

For this "challenge", it's reasonable to have 1 small VM that handles everything. 1 reverse proxy handling HTTPS, 1 application process and 1 database instance. There is plenty of time in between each request, so it's perfectly fine to use something like a PHP server, where requests are handled synchronously (req A must be fully finished before starting req B).

Despite the scaling part being easy, there is still a significant amount of work to be done to get this website fully working: the database schema needs to be fully designed, transactions need to work correctly, authentication/authorization needs to be implemented properly, inputs need to be validated properly, rate limiting should be added to prevent abuse, backups should be done automatically, errors/logs should be collected and failed processes need to be restarted.

1,000 req/sec

This is the area where the design of your system starts to become more important. Here usually the database starts to become a bottleneck, but with some smart design you can still get away with just 1 (albeit a bit bigger) VM handling everything.

Let's take a look at some bad query designs and see how we can improve them (significantly!):

We need to get all orders that a user has made. One of the ways we can do this is by getting all orders, then inside of the application we filter for the current user, sort orders and keep 20 to show inside of a table:

SELECT * FROM orders;

The problem here is that there is a lot of wasted data being sent from the database to the app. We can reduce this data by making the database handle the filtering, sorting and limiting:

SELECT id, created_at, total
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

While this greatly reduces the amount of data being sent, it doesn't magically make this query cheap to run. We can help things by adding an index:

CREATE INDEX orders_user_latest_idx
ON orders (user_id, created_at DESC, id DESC);

Notice how we are dealing with raw SQL queries. It's common to use ORMs in your app that will generate these queries for you. This is convenient, but you should then be very careful what the generated queries actually are. An option somewhere in between is to use something like Drizzle's sql operator (https://orm.drizzle.team/docs/sql), where you can write raw SQL without worrying about SQL injection.

1,000,000 req/sec

todo :)