ScyllaDB is a C++ version of Cassandra so it looks like speed and scalability is a complete advantage over the Java based Cassandra and Discord is using ScyllaDB at scale too.
Scylla/Cassandra is exactly the database you want when you're serving the entire planet.
Postgres is exactly the database you want when you're desperately searching for product market-fit and just need things to work and code to get written.
Turns out it's much better to start simple and face scaling problems maybe than start complex and face scaling problems maybe.
The sorting by any column and pagination is really killer. Can't do cursor-based pagination so you get LIMIT/OFFSET which is terrible for performance, at least in this context. Indexes are often useless in this circumstance as well due to the filter by any column. We get lucky sometimes with a hard-coded WHERE clause as baseline filter for the entire report that hits an index, but not always. Just for fun, add in plenty of LIKE '%X%' because we limiting them to STARTS WITH is out of the question; it must be CONTAINS.
It's a constant source of frustration for the business and for the dev team.
If we could do away with ES and go back to MySQL for what are glorified data grids with 20+ column filtering, sorting, and no full text requirements, we would.
More pragmatically, just force bookmarked scans - limit the columns that can be sorted by - use a replica with indexes that the writer doesn’t have - pay for fast disks - use one of the new parallel query engines like Aurora or Vitess - just tell the business people to wait a dang minute, computer is thinking!
If you're launching an e-commerce site that sells shoes or something, yeah, you probably aren't going to sell a billion shoes every year so MySql is probably fine.
Finally, I use this example a lot: Github thought they could scale out MySql by writing their own Operator system that sharded MySql. They put thousands of engineering hours in it, and they still had global database outages that lost customer data.
You get that shit for free using Scylla/Cassandra (the ability to replicate across data centers and still fine grained control to do things like enforce local quorum to not impact write speed etc.)
They probably made the wrong choice.
Or just use Astra and not worry about scaling your own cluster.
https://github.blog/2021-09-27-partitioning-githubs-relation...
GitHub didn't write it, Google/YouTube did.
Everything is tradeoffs, you need to look at what you're trying to do and see which problems you are okay with. Some of these will be show-stoppers at larger scale. Some might be show-stoppers now.
But "just use __________" is never the right answer for any serious discussion.
Ouch, haha, that hurt
SELECT id FROM table1
SELECT * FROM table2
WHERE table1_id in (...ids from previous query...)
SELECT table2.*
FROM table1
INNER JOIN table2 ON (
table2.table1_id == table1.id
)The posters up thread are talking about novice Python / JavaScript / whatever code which issues queries in sequence: first a query to get the IDs. Then once that query comes back to the application, the IDs are passed back to the database for a single query of a different table.
The query optimizer can’t help here because it doesn’t know what the IDs are used for. It just sees a standalone query for a bunch of IDs. Then later, an unrelated query which happens to use those same IDs to query a different table.
SELECT * FROM table2
WHERE table1_id in (SELECT ids FROM table1 WHERE ...)
SELECT * FROM table2
WHERE table1_id in (452345, 529872, 120395, ...)
I hope there are better ways to design microservices.
it ends up with some pretty terrible performance.
It's often a lot faster to do something like
Insert into #tempTable ids
select from table JOIN #tempTable t on t.id = table.id
For the `IN` query the DB doesn't know how many elements are in the list. In MSSQL, it would default to assuming "well, probably a short list" which blows out performance when that's not the case.
When you first insert into the temp table, the DB can reasonably say "Oh, this table has n elements" and switch the query plan accordingly.
In addition, you can throw an index on the temp table which can also improve performance. Assuming the table you are querying against is indexed on ID, when you have another table with IDs that are indexed it doesn't have to assume random access as it pulls out each id. (Effectively, it just has to navigate the tree nodes in order rather than needing to do a full look into the tree).
If familiarity is the primary problem, it should be relatively easy to fix.
I've worked with a large Cassandra cluster at a pervious job, was involved with a Scylla deployment, and have a lot of experience with the architecture and operations of both databases.
I wouldn't consider Cassandra/Scylla a replacement for Postgres/MySQL unless you have a very specific problem, namely you need a highly available architecture that must sustain a high write throughput. Plenty of companies have this problem and Scylla is a great product, but choosing it when you are just starting out, or if you are unsure you need it will hurt. You lose a lot of flexibility, and if you model your data incorrectly or you need it in a way you didn't foresee most of the advantages of Cassandra become disadvantages.
1. If you delete data, tombstones are a problem that nobody wants to have to think about.
2. Denormalizing data instead of joining isn't always practical or feasible.
3. If you're not dealing with large amounts of data, Postgres and MySQL are just easier. There's less headache and it's easier to build against them.
4. One of the big advertised strengths of Cassandra is handling high write volume. Many applications have relatively few writes and large amounts of reads.
Besides that you need to keep in mind that scylla isn't a silver bullet. There are some workloads that it can't handle very well. Where I work we tried to use it in workload with high read and writes and it had trouble to keep up.
In the end we switched back to postgres + redis because it was cheaper.
Cassandra/Scylla come with a lot of limitations when compared to typical SQL DB. They were developed for specific use cases and aren't at all comparable to SQL DBs. Both are completely different beast compared to standard SQL DBs.
Data is the most important stuff for them and being able to read it is therefore very important.
A nosql DB structure can usually only be read by the code that goes with it and if nobody understands the code you're doomed.
In 2017, I had to extract data from an app from 1994. The app was built with a language that wasn't widely used at the time and had disappeared today. It disappeared before all the software companies put their doc on the internet. You can't run it on anything older than windows2k. There was only one guy in Montréal that knew how to code and run that thing.
As the DB engine was relational with a tool like sqlplus, I was able to extract all the data and we rebuilt the app based on the structure we found in the database.
If you want to do that with Nosql, you must maintain a separate documentation that explains your structure... And we all now how dev like documentation.
Postgres is super well supported, basically every tool out there has a connector to postgres, and most people have familiarity
If I am honest, I never even bother researching alternatives, I always go with postgres out of habit. I know it, and it has never let me down