Postgres also isn't terrible, but also not brilliant for that use case.
How has your experience been in that regard?
I built a EAV secondary index system on top of Postgres to accelerate Notion’s user-defined-schema “Databases” feature about a year ago. By secondary index, I mean the EAV table was used for queries that returned IDs, and we hydrated the full objects from another store.
We’d heard that “EAV in Postgres is bad” but wanted to find out for ourselves. Our strategy was to push the whole query down to Postgres and avoid doing query planning in our application code.
When we first turned it on in our dogfood environment, the results looked quite promising; large improvement compared to the baseline system at p75, but above that things looked rough, and at p95 queries would never complete (time out after 60s).
It worked great if you want to filter and sort on the same single attribute. The problem queries were when we tried to query and sort on multiple different attributes. We spent a few weeks fixing the most obviously broken classes of query and learned a lot about common table expressions, all the different join types, and strategies for hinting the Postgres query planner. Performance up to p95 was looking good, but after p95 we still had a lot of timeout queries.
It turns out using an EAV table means Postgres statistics system is totally oblivious to the shape of objects, so the query planner will be very silly sometimes when you JOIN. Things like forget about the Value index and just use a primary key scan for some arms of the join because the index doesn’t look effective enough.
It was clear we’d need to move a lot of query planning to the application, maintain our own “table” statistics, and do app joins instead of Postgres joins if Postgres was going to mess it up. That last part was the last nail in the coffin - we really couldn’t lean on join in PG at all because we had no way to know when the query planner was going to be silly.
It was worth doing for the learning! I merged a PR deleting the EAV code about a month ago, and we rolled out a totally different design to production last week :)
Postgres really trips up when you start joining tables
Sometimes you can fix it with "(not) materialized" hints, but a lot of the time you just have to create materialized views or de-normalize your data into manual materialized views managed by the application
Long long time ago, I found that quite helpful with MySQL.
And it's a hosted service, so the performance issues are for the InstantDB team to worry about, and they can fold it into the price they charge. It does mean that your application architecture will get locked in to something that costs a fortune in server bills when it gets big, but from InstantDB's POV, that's a feature not a bug. From your POV as a startup it may be a feature as well, since if you get to that point you'll like have VC to blow on server bills or use to rewrite your backend.
We're also working on an individual Postgres adapter. This would replace the underlying triple store with a fully relational Postgres database.
[1] https://www.usenix.org/system/files/conference/atc13/atc13-b...
That's a rather tremendous oversimplification, unless something major changed in recent years. When I worked on database infra at FB, MySQL-backed TAO objects and associations were mapped to distinct underlying tables for each major type of entity or relationship. In other words, each UDB shard had hundreds of tables. Also each MySQL instance had a bunch (few dozen?) of shards, and each physical host had multiple MySQL instances. So the end result of that is that each individual table was kept to a quite reasonable size.
Nor was it an EAV / KV pattern at all, since each row represented a full object or association, rather than just a single attribute. And the read workload for associations typically consisted of range scans across an index, which isn't really a thing with EAV.