Scaling Plausible Analytics 1.4 for millions of sites
Recently, we came across a very interesting scenario with a client where they were running an old open source version of Plausible Analytics as a way to offer an analytics solution to their customers. In practice this meant that for every new user, a new site record need to be created in the Plausible Postgres database that they would then log subsequent analytics events against.
The solution worked fine up to about 250k users, but soon after this the response from the POST endpoint for creating a site started to creep up in time, and eventually started timing out with 60s + request times. This user increase, coupled with the fact that new users were signing up at a rate of about 1 user every 5 seconds, meant that the requests started failing and new users' analytics were broken.
Their initial solution to the problem was to keep upgrading the ec2 instance that the Plausible instance ran on every time requests started taking longer, but it was only ever a short term fix as the problems quickly came back as the user numbers increased again. Soon they were running a c6a.2xlarge ec2 instance with 16gb of RAM, costing around $260 per month, just to host a Plausible instance for 500k users. Even then it was unreliable with 1 in 5 requests failing, despite the fact that CPU utilization was consistent at around 12.5%... something was clearly going wrong.
The first step in diagnosing the issue was to SSH into the ec2 instance, and inspect the docker container logs to see if anything obvious was happening. As you can see below, there were no obvious errors except for an invalid Sentry DSN flooding the logs. Noisy, but definitely not the root cause of our problem.
After not making much headway with the the container itself, I decided to pull down the open source version of Plausible locally to see if anything obvious was going on in the code. It seemed suspicious that the requests time increased linearly with the amount of sites in the system, and considering this wasn't how Plausible was originally intended to be used, I wondered if something inefficient was going on in the code.
To investigate the code, having very little experience with Elixir, I opened Cursor and asked it to investigate for me if anything strange was going on with the POST /sites endpoint which might cause requests to get really slow if a lot of sites existed in the database. It immediately found the issue. This version of Plausible was setup with a gate that limited the amount of sites you could have to whatever value was set on your user account. A feature designed for the hosted version, and not the open source one. The check itself wasn't the issue. It was always coming back as 'true' since the user account had no limit set. The issue was how the check was being done. It was loading every single site into memory to then count them in memory, and then compare the value to the figure on the user account... A completely pointless check for us, but one which resulted in 300mb of db data being loaded into memory on every request to /POST.
After finding the source of the issue, all we needed to do was fork the Plausible repo, push the code change below to remove the check, publish a custom image to ECR, and then use this image inside our EC2 instance instead of the official image we were using before.
After doing this one really simple change, request times dropped dramatically to only 21 ms per request and there were no more time outs. After this fix we were also able to decrease the ec2 instance size back to ec6.large with 4gb of RAM costing only $5o per month with no issues, even now they are at 2 million users. 8x the amount from when they initially hit the problem.
1 simple file change not only saved this client $200 per month, but also solved a persistent problem that was limiting a core product offering for 100s of thousands of users for many months before.
If you've got these sorts of persistently hard problems in your platform today, then we're always here for a conversation to see if we can help.
