Faster Data Tables are here
We have just released a new storage engine for Data Tables, and it is multiple orders of magnitude faster than our original storage engine. It’s the same API, just faster. You can switch it on right now and start creating Faster Storage tables, or migrate your existing apps.
There’s more: Faster Storage also gives you full control over indexing (on Business plans and up), so you can optimise for queries and orderings on particular columns or combinations of columns. You can also control what happens if you delete a row that’s linked to by another row – set the link to None, raise an error, or even automatically delete the linking rows.
Check out the blog post, or scroll down for some indulgent backstory:
Let me tell you a story…
When we first built Anvil, we built Data Tables on top of Postgres. (Good choice, as it turns out.) However, knowing that we were going to be serving a large number of tables with wildly varying schemas, we went no-SQL: We combined lots of Data Tables into big shared Postgres tables, storing different-shaped data in one multi-purpose column by JSON-encoding it using Postgres’s efficient JSONB storage. We then indexed the whole thing with GIN (Postgres’s Generalised Inverted Index), so that you could quickly look up any column by any value with no prior developer configuration.
This unlocked a bunch of very cool functionality, but at scale it turned out to have some significant disadvantages. The average case continued to be fast, but our tail latencies weren’t great, and they got worse as Anvil grew.
Some of the failure modes were obvious in retrospect. One developer discovered that we weren’t enforcing storage size limits for SimpleObjects. To avoid upgrading their account, they instead started combining lots of records into one SimpleObject, stuffing dozens of megabytes into each row. Every time they updated one tiny record, they rewrote one of these enormous rows – this wasn’t a low-traffic app, so these updates were frequent – and because we were indexing absolutely everything, each update effectively wrote a full copy of the affected row into the index. We saw peaks of gigabytes per minute on a single table.
Fun fact: If a GIN index is receiving too much data, Postgres will victimise the next process that writes to that table, and force it to organise the index before it’s allowed to carry on with its work. Because this was a shared table, this problem first showed up as other apps’ Data Tables updates taking absurdly long times to write tiny updates, or just timing out. That was fun to debug. (We count SimpleObjects against data limits now.)
But the other issues were much harder to pin down. Postgres has some great query-analysis tools – you can run a query with EXPLAIN ANALYZE and get full details of its runtime, how much IO it did, and so on. Unfortunately, whenever we pulled a slow query from the logs, it would be fast! Performance was critically dependent on cache state in puzzling ways. With patient sampling, we identified inefficient filtering patterns, optimised our queries, and tuned Postgres settings, but the P99 latencies stayed stubbornly high.
It gradually became clear that this was a losing battle. We were using Postgres in a way it wasn’t designed to be used, we were piling workarounds on workarounds, and Data Tables performance was still a running complaint. The breaking point was a rapidly growing customer who could not continue as-is: They had their entire working dataset in RAM and their Data Tables still could not perform acceptably.
So we buckled down, and at the end of last year we started to rewrite our storage engine. This engine is in some sense much more straightforward: One Data Table = One Postgres table. Of course, this means a lot of things we’d been trying to avoid: Automating a bunch of nasty Postgres DDL, making sure our own representations were always in sync with the database, and making sure the new system satisfied the same API contracts as the old.
We’ve been trialling Faster Storage with selected customers for the last few months. The new engine has been battle-tested with production load, and the reaction has been extremely positive. When we first switched over one of our internal apps, there were audible gasps in the office when people saw how much faster it got.
So, that’s the story of Faster Storage. I hope you enjoy checking it out, migrating your apps, and enjoying that sweet, sweet performance!
