I've found triple stores to have pretty poor performance when most of your queries fetch full objects, or many fields of the same object, which in the real world seems to be very common.
Postgres also isn't terrible, but also not brilliant for that use case.
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 :)
I really love Postgres, but I'll never not laugh at the fact that duplicating a CTE caused my query to go faster... (60s to 5s)
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
It does not, and that fact is the #1 downside of Postgres. It is not predictable or controllable at scale, and comes with inherent risk because you cannot “lock into” a good query plan. I have been paged at 3 am a few times because Postgres decided it didn’t like a perfectly reasonable index anymore and wanted to try a full table scan instead :(
I've also found triple stores to have terrible performance, but it looks like the intended use-case for this (like Firebase) is rapid development, prototyping, and startups. You aren't going to generate enough traffic when you're building an MVP for this to be an issue.
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.
So far we haven't hit intractable problems with query performance. One approach that we could evolving too down the road is similar to Tao [1]. In Tao, there are two tables: objects and references. This has scaled well for Facebook.
We're also working on an individual Postgres adapter. This would replace the underlying triple store with a fully relational Postgres database.
> In Tao, there are two tables: objects and references. This has scaled well for Facebook.
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.
Postgres also isn't terrible, but also not brilliant for that use case.
How has your experience been in that regard?