3 ms·

yes the easy setup with crdb is really attractive but then you realize that they hide a few very important features behind the paid option. No incremental backu

yes the easy setup with crdb is really attractive but then you realize that they hide a few very important features behind the paid option. No incremental backups and no partitions (I think of any kind...). Those are some big features to lose, obviously I'm not complaining, just comparing. That was the main reason why I started developing my project in MySQL because at least MySQL has a lot of different free providers. YugabyteDB exists for Postgres too but initially I was more impressed with Vitess and TiDB because TiDB is used by some really huge chinese companies and if they don't have a problem then it must be okay. Unfortunately TiDB essentially doesn't want you to run a single node, ever, and their smallest recommended deployment is 5 nodes. That's a lot. Citus also exists for Postgres but their docs basically tell you that it's only recommended for analytics so I don't know what to think of it. And it sounds like all it's doing is a basic master-slave postgres setup with quite a few manual things you have to do to even benefit (manually altering tables to make them sharded/partitioned)

geenat 3y ago
I totally hear you, been down a similar path. As for the "huge chinese" databases, not to sound like a conspiracy, but CockroachDB has outperformed them in my server load testing (stress test with very basic "SELECT FROM users" and "INSERT INTO users" on KC3000 + Ryzen 7950x)- so I'm beginning to seriously question all of the self-published benchmarks from mainland china. That, and when one considers they have a similarly rough deployment story compared to ScyllaDB, is it maybe worth just going directly to ScyllaDB in the first place? One other damming thing: I've also observed that all Spanner (Big Table) inspired databases have lower throughput compared to Postgres when under 5-ish servers: Aka you only begin to see throughput benefits after you have a sizeable cluster. ScyllaDB / Cassandra is a different architecture, though. Heck even Vitess, pretty rough deploy story as well (they really push k8s on you), but that's why PlanetScale exists, IMO.
- Nathanba 3y ago
  One nice thing I found today is that ScyllaDB has a pretty nice JSON feature, they let you insert and select directly via json strings. I can't do that in MySQL which is annoying, I could do something equivalent in Postgres. One of my biggest complaints about SQL in general is the datatype mapping / serialization and if I can simply use a json library in my language of choice then I save on a lot of work. https://www.scylladb.com/2019/01/10/json-support-in-scylla/ https://www.scylladb.com/2019/01/10/json-support-in-scylla/ But you are right about how important it is to have a simple deployment, it's true for all of the databases. Sure there is docker but that usually has its own problems, like with scylla where it wouldn't properly forward ports for whatever reason so I could never connect to it. Then I had to do all kinds of manual fixes and install it on a VM to edit configs to make it run. This is really the stuff that makes you want to use crdb and be done with it all, even though it's slower and eventually requires payment.
  - heipei 3y ago
    We're running ScyllaDB on Docker via Nomad and it's been an absolute breeze. One simple container with a handful of open ports. I don't get what's complicated about ScyllaDB deployment.
  - geenat 3y ago
    One strategy I've considered: Write Postgres in a way that's fully compatible with CockroachDB, with the intention of transitioning to CockroachDB if needed. Basically: 1. BIGINT or UUID or TEXT or composite Primary Keys only. 2. Limited use of sequential indexes. (Ex: timestamp. Not the end if the world but creates hotspot, will require a hash sharded index) 3. Avoid advanced features such as stored procedures. 4. Avoid or very light use of foreign keys. (Performance) Going straight to CockroachDB seems logical as well, but you'll hit the performance wall much sooner on 1-2 servers, and will require a cluster to match it.
    - Nathanba 3y ago
      if crdb had some kind of perpetual fallback license like Jetbrains then I would be far happier with the prospect of eventually switching to crdb as an option. But "contact us" pricing is not exactly unscary pricing. If you think about it, even the infamously opaque Microsoft SQLServer has a pricing table nowadays. Therefore I tried to calculate some kind of pricing comparison, I'm looking at the cpu alone now. I'm seeing that they ask for $0.46/h for m5.large and $6.34/h for their m5.8xlarge (managed dedicated offering) Using the aws price calculator and selecting these instance types directly it would cost x4 less to rent the aws instances directly. Paying 4 times your raw server cost for licensing is not exactly a dream come true. but scylladb really has so many weird footguns. I just discovered that you can't even query for NULL values. It just doesn't work, they have no support for WHERE x = NULL even with an index. That's crazy to me, if I ever make a mistake and have a few NULL'ed values then I can't even find them again so I can set them to an empty string to make them queryable. I just don't know, I almost feel like scylladb has too many tradeoffs.
      - geenat 3y ago
        Can confirm the inability to select null values. Have not yet found a reasonable pattern / workaround.. The reasoning seems to be that null values are not stored-- it's displayed as null in csql, but in reality, that data just does not exist. For the record, CockroachDB does not store null either, but it allows querying null. A workaround may be stepping through the full table manually using the PK (which can never be null) checking for null "in app" and cleaning them that way. Noticed INSERTS are treated as UPSERT unless you use IF NOT EXISTS... ScyllaDB doesn't give a f__k if it's overwriting an existing row, lol. I can live with the UPSERT footgun, though. Agreed the null footgun is far more annoying.
mulander 3y ago
Hi Nathanba, I am a technical program manager for the Citus extension at Microsoft. > Citus also exists for Postgres but their docs basically tell you that it's only recommended for analytics Could you point me at the docs that made you think that? We find Citus very good at multi-tenant SaaS apps (OLTP), IoT (HTAP) workloads and analytics (OLAP). > And it sounds like all it's doing is a basic master-slave postgres setup with quite a few manual things you have to do to even benefit (manually altering tables to make them sharded/partitioned) This is true for now. We are looking into ways to make onboarding easier. That said, the time spent on defining a good sharding model for your data often leads to very good perf characteristics. Regarding the architecture, I personally find Citus closer in spirit/design to what Vitess is doing. Additionally, every node in the cluster is able to take both writes and reads, so I don't see the parallel to a basic primary/secondary setup.
- Nathanba 3y ago
  I'm looking at this page: https://www.citusdata.com/use-cases https://www.citusdata.com/use-cases "Multi-Tenant SaaS" to me very clearly sounds like there are features specific towards isolating the database into different customer domains ("tenants"). Which to me implies that this is sort of a scaling trick that isn't going to provide performance for a regular application where I can't partition my data like that.

geenat 3y ago

I totally hear you, been down a similar path. As for the "huge chinese" databases, not to sound like a conspiracy, but CockroachDB has outperformed them in my server load testing (stress test with very basic "SELECT FROM users" and "INSERT INTO users" on KC3000 + Ryzen 7950x)- so I'm beginning to seriously question all of the self-published benchmarks from mainland china. That, and when one considers they have a similarly rough deployment story compared to ScyllaDB, is it maybe worth just going directly to ScyllaDB in the first place? One other damming thing: I've also observed that all Spanner (Big Table) inspired databases have lower throughput compared to Postgres when under 5-ish servers: Aka you only begin to see throughput benefits after you have a sizeable cluster. ScyllaDB / Cassandra is a different architecture, though. Heck even Vitess, pretty rough deploy story as well (they really push k8s on you), but that's why PlanetScale exists, IMO.

Nathanba 3y ago
One nice thing I found today is that ScyllaDB has a pretty nice JSON feature, they let you insert and select directly via json strings. I can't do that in MySQL which is annoying, I could do something equivalent in Postgres. One of my biggest complaints about SQL in general is the datatype mapping / serialization and if I can simply use a json library in my language of choice then I save on a lot of work. https://www.scylladb.com/2019/01/10/json-support-in-scylla/ https://www.scylladb.com/2019/01/10/json-support-in-scylla/ But you are right about how important it is to have a simple deployment, it's true for all of the databases. Sure there is docker but that usually has its own problems, like with scylla where it wouldn't properly forward ports for whatever reason so I could never connect to it. Then I had to do all kinds of manual fixes and install it on a VM to edit configs to make it run. This is really the stuff that makes you want to use crdb and be done with it all, even though it's slower and eventually requires payment.
- heipei 3y ago
  We're running ScyllaDB on Docker via Nomad and it's been an absolute breeze. One simple container with a handful of open ports. I don't get what's complicated about ScyllaDB deployment.
- geenat 3y ago
  One strategy I've considered: Write Postgres in a way that's fully compatible with CockroachDB, with the intention of transitioning to CockroachDB if needed. Basically: 1. BIGINT or UUID or TEXT or composite Primary Keys only. 2. Limited use of sequential indexes. (Ex: timestamp. Not the end if the world but creates hotspot, will require a hash sharded index) 3. Avoid advanced features such as stored procedures. 4. Avoid or very light use of foreign keys. (Performance) Going straight to CockroachDB seems logical as well, but you'll hit the performance wall much sooner on 1-2 servers, and will require a cluster to match it.
  - Nathanba 3y ago
    if crdb had some kind of perpetual fallback license like Jetbrains then I would be far happier with the prospect of eventually switching to crdb as an option. But "contact us" pricing is not exactly unscary pricing. If you think about it, even the infamously opaque Microsoft SQLServer has a pricing table nowadays. Therefore I tried to calculate some kind of pricing comparison, I'm looking at the cpu alone now. I'm seeing that they ask for $0.46/h for m5.large and $6.34/h for their m5.8xlarge (managed dedicated offering) Using the aws price calculator and selecting these instance types directly it would cost x4 less to rent the aws instances directly. Paying 4 times your raw server cost for licensing is not exactly a dream come true. but scylladb really has so many weird footguns. I just discovered that you can't even query for NULL values. It just doesn't work, they have no support for WHERE x = NULL even with an index. That's crazy to me, if I ever make a mistake and have a few NULL'ed values then I can't even find them again so I can set them to an empty string to make them queryable. I just don't know, I almost feel like scylladb has too many tradeoffs.
    - geenat 3y ago
      Can confirm the inability to select null values. Have not yet found a reasonable pattern / workaround.. The reasoning seems to be that null values are not stored-- it's displayed as null in csql, but in reality, that data just does not exist. For the record, CockroachDB does not store null either, but it allows querying null. A workaround may be stepping through the full table manually using the PK (which can never be null) checking for null "in app" and cleaning them that way. Noticed INSERTS are treated as UPSERT unless you use IF NOT EXISTS... ScyllaDB doesn't give a f__k if it's overwriting an existing row, lol. I can live with the UPSERT footgun, though. Agreed the null footgun is far more annoying.

mulander 3y ago

Hi Nathanba, I am a technical program manager for the Citus extension at Microsoft. > Citus also exists for Postgres but their docs basically tell you that it's only recommended for analytics Could you point me at the docs that made you think that? We find Citus very good at multi-tenant SaaS apps (OLTP), IoT (HTAP) workloads and analytics (OLAP). > And it sounds like all it's doing is a basic master-slave postgres setup with quite a few manual things you have to do to even benefit (manually altering tables to make them sharded/partitioned) This is true for now. We are looking into ways to make onboarding easier. That said, the time spent on defining a good sharding model for your data often leads to very good perf characteristics. Regarding the architecture, I personally find Citus closer in spirit/design to what Vitess is doing. Additionally, every node in the cluster is able to take both writes and reads, so I don't see the parallel to a basic primary/secondary setup.

Nathanba 3y ago
I'm looking at this page: https://www.citusdata.com/use-cases https://www.citusdata.com/use-cases "Multi-Tenant SaaS" to me very clearly sounds like there are features specific towards isolating the database into different customer domains ("tenants"). Which to me implies that this is sort of a scaling trick that isn't going to provide performance for a regular application where I can't partition my data like that.