Making scylla optional

[julietshen]

We've been talking about the plan to make Scylla optional for a bit, so creating a Discussion thread to align on the approach and lock in target dates and timelines.

Relates to #190

Goal

Making Scylla (and optionally ClickHouse / Redis) foldable into Postgres without shipping dead tables, while keeping “Postgres” a logical choice that can still span multiple physical hosts.

Proposal

@juanmrad created an HTML file for the proposal, which i'm attaching here.

datastore-consolidation (1).html

The mistake to avoid is treating “use Postgres” as one decision. There are two independent axes. Keeping them separate is what lets a domain move from a shared host to its own without touching application code or rewriting queries.

Axis 1 - Storage engine
Which technology backs a domain — Postgres, Scylla, ClickHouse, Redis. Selected per-domain via env (e.g. ITEM_STORE_BACKEND, ANALYTICS_BACKEND) behind a storage interface.

Axis 2 - Connection routing
Which physical host serves that engine. Each logical Postgres handle resolves to a connection, defaulting to the app DB. One host today; lift to its own host later — no schema rename.

Consequence
“Folding into Postgres” is a logical-engine choice. It does not force a single host — Scale · PG-split runs three separate Postgres instances and still counts as consolidation (one engine to learn, back up, and operate).

Open Questions and Risks

• Item-history query perf
  • Thread / creator traversal currently leans on Scylla materialized views + streaming. PG needs careful composite indexes and cursor pagination; validate at expected volume before committing Scale users.
• TTL strategy on PG
  • 30d items / 90d strikes need partitioning, an expires_at filter, or pg_cron. Decide once and apply to both domains.
• CDC has no PG equivalent
  • Scylla CDC feeds analytics streaming. Dropping Scylla means a different change-capture mechanism (or accept batch).
• Resist a 3rd impl
  • Every seam stays ≤2 engines + no-op. The moment a seam grows a fourth backend, migration and test cost balloons — exactly the failure mode to avoid.

[taobojlen]

My comments here are about the idea of making Scylla optional in the first place. The BLUF is that I think it's a strategic mistake¹.

My argument is that it's one thing to build software that works for small deployments, and a very different thing to build and operate something that works at scale. For Coop, ROOST currently has experience with the first part, and low-to-none with the second part. I am assuming that we want Coop to work at scale (let's say up to 1000 events/second or so).

Because we don't provide a hosted version of Coop, ROOST will not naturally gain the operational experience of running Coop at scale, and this limits our ability to support adopters. That's a risk that we can mitigate by running our own instance that ingests some high-volume public data. But the more disparate adopters' infrastructure is, the harder it will be for us to gain the right operational knowledge to support them.

One of the biggest lessons I've taken away from building data-intensive applications is that the shape of data on disk is hugely important, and that operationalizing your datastore(s) is half the battle. Supporting N data backends for the same thing means we have N battles! It also means that our application ends up supporting only the lowest common denominator features that would otherwise be enabled by the database. For example, Scylla supports per-table and per-row TTLs while native Postgres does not, so we'd need to either not use those features or hand-roll support for that in the Postgres adapter. Every new feature, migration, schema change, performance problem, etc. would need to be designed and validated against both (or all) backends.

As a small team, I think we have to prioritize, and I don't think the benefit of this outweighs the high (and ongoing) costs. I understand the benefit of making deployment simpler, but I wonder if a) has the Scylla dependency actually stopped anyone from using Coop, and b) what else could we do to make that deployment simpler?

Also, note that I'm not saying that we necessarily want to support Scylla forever, but that we should only support one datastore (for each kind of data) at a time.

Footnotes

1. I also think that supporting multiple different data warehouses is probably not a good idea for similar reasons, but that's a separate discussion. ↩

[ThisIsMissEm]

None of those sound particularly like workloads PostgreSQL couldn't handle? Might be worth testing assumptions about scaling needs & checking if scylla is actually needed for answering these questions

like scylla may be necessary at very high volumes of data, but maybe most customers aren't needing quite that scale yet?

[taobojlen]

true! i had some painful scaling-postgres experiences when i worked for a T&S vendor, hence an instinctual aversion to saving all incoming coop items in it 😅 but ofc it should depend on what our actual scaling goals are for coop!

AFAICT we haven't defined those anywhere but i'd aim for something like: coop should be able to handle 1000 incoming events or items per second. if that's the case then saving all those items to postgres gets painful pretty quickly. but this is dependent on the exact logic for deciding when to save vs. when not to ofc!

[ThisIsMissEm]

I'm trying to get some information on message throughput, but I suspect for many coop users it's well below 1000 events / second. maybe half that?

e.g., hachyderm with 45k users runs mastodon with about 200 req/s peak 500 req/s. The AT Protocol relays hosted by Bluesky are apparently low hundreds to ~2000 events/s, with Bryan being quoted with this is 2025:

More recently, as of April 2025, peak firehose throughput on a random day was around 600 events/second, though sustained rates of 2,000 events/sec have been seen in the past.
— https://whtwnd.com/bnewbold.net/3lo7a2a4qxg2l

That may be higher now, given growth and remember: that's entire network data throughput, not just a single application's lexicon.

[juanmrad]

Our goal is that Coop should be able to handle at least 10K events per second and we know this is possible from the SaaS days. providing an abstraction here is more on the can we have a more simplified deployment with 1 data store ( AKA Postgres ) that adopters can easily use and work with.

Also offer the scaled option with more specific data stores like Clickhouse and Scylla. The plan is also not to balloon to support multiple data stores for each service, but to be targeted. As to the point made before having too many data stores means multiple migrations if they are not compatible and also multiple interface implementations.

Moving to something like SlateDB or any other datastore doesn't solve the problem as that is still a new service that adopters will need to have, and we would like to instead offer something more widespread as we have heard from some companies that they would like to not have Scylla or Clickhouse as that is not something they use on their infrastructure, and don't want to maintain a single EC2 deployment or service of something they are not familiar with.

We could also consider building the Postgres Adapter with an ORM that allows to abstract migrations, but that will bring yet another dependency and more complexity to the code.

[taobojlen]

Cool, 10k events/second is a good goal and somewhat high, which is a good thing!

I guess my core argument is that datastores, in general, don't really abstract in a good way if you want to do anything beyond the very basics. Even within the same class of DB, say columnar/OLAP, this holds true! For example I've spent a long time migrating from AWS Redshift to Clickhouse in the past, and it was quite painful. And even longer trying to migrate things off Postgres once the scale got to a certain level. If you're building for 10k events/second then the implementation details of your datastore start to really matter.

Of course, my experience might not be universal, but hopefully it demonstrates where I'm coming from. I'd argue that even with only 2 datastores for e.g. analytics, we're doubling the work and friction for making changes to that part of the product.

we would like to instead offer something more widespread as we have heard from some companies that they would like to not have Scylla or Clickhouse as that is not something they use on their infrastructure, and don't want to maintain a single EC2 deployment or service of something they are not familiar with.

I sympathise with this! And I don't want to imply that Scylla is trivial to operate at scale. But it's also a spectrum, right -- there will always be adopters who don't have experience running Node apps, or with Postgres, or Clickhouse. I think we have to pick sensible and scalable technologies for the job. I can't think of any serious data-intensive apps that are datastore-agnostic -- I know that PostHog used to offer a self-hosted Postgres-only version and retired it because it was just too much work for them to maintain. And they're VC-funded with loads of engineers!

As an aside: I only mention SlateDB as one example of something that's very operationally simple and therefore could be desirable to build on. But it's really the "operationally simple" part that's key.