5 ms·
Now that Cockroach supports the "PostgreSQL Wire Protocol", how does it compare to CitusDB for scaling a relational data model in a distributed environment? ht
by jimbokun 9y ago
Now that Cockroach supports the "PostgreSQL Wire Protocol", how does it compare to CitusDB for scaling a relational data model in a distributed environment?
https://www.cockroachlabs.com/docs/stable/architecture/sql-layer.html#postgresql-wire-protocol https://www.cockroachlabs.com/docs/stable/architecture/sql-l...
- ozgune 9y ago(Ozgun from Citus) Citus extends PostgreSQL to make it a distributed database. In comparison, Cockroach builds the entire database from scratch and provides over the wire compatibility. For our users, this means that they get the following benefits. 1. PostgreSQL Features I can think of the following features that our customers started using as they landed into core Postgres (there's probably many more): * Rich data types. For example, jsonb, hstore, XML, and HyperLogLog * Declarative partitioning. You can shard your tables and then partition them * Performance improvements. Postgres 10 improves analytical query performance by 30-40%, and PG 11 will add another 3x. For Citus customers, they will get these performance benefits at scale. 2. Application Integration If you already have an app built on Postgres, migrating to Citus should be significantly easier than moving to a new database. Some benefits I can think of are: * You can pg_dump your Postgres schema and data. You can then load this data into Citus. * If you need live migrations, you can use Citus Warp to stream changes from your Postgres database to Citus * Native integration with hundreds of tools already in the Postgres world - data visualization, monitoring, backups, upgrades, and app frameworks such as Rails and Django. 3. Organizational Skills Since Citus extends (it's practically) Postgres, you benefit from organizational skills created worldwide by running millions of Postgres applications. All know-how around how to configure your Postgres database, when to create indexes and database constraints, and how to tune your queries directly applies to Citus. I hope this helps. If this doesn't help clarify things, could you let me know?
- jimbokun 9y agoThat is a very useful answer, thanks!
- MuffinFlavored 9y ago> * Performance improvements. Postgres 10 improves analytical query performance by 30-40%, and PG 11 will add another 3x. For Citus customers, they will get these performance benefits at scale. When is that to be expected or even usable in beta?
- manigandham 9y agoPostgres 10 is out now, that's what's running when you start a new Citus Cloud cluster. Postgres 11 is in progress, you can always follow the official releases on the main site: https://www.postgresql.org https://www.postgresql.org
- anarazel 9y agoPlease note that in both cases it obviously depends on where your bottleneck is. E.g. if you're IO bound, you're not going to see a lot of improvements. Similarly if most of your time is spent doing index scans there'll not be that much efficiency gains. Although in both cases parallelism can help. If instead you're doing a bunch of aggregates over table, and the bottleneck is the computation of the transition values, computation of hashes, tuple deforming, etc... you might see quite some benefits (first two some in v10 already, all three hopefully in v11).
- andreimatei1 9y agoOzgun has done a good job describing some of Citus' benefits; allow me to write the CockroachDB-biased answer. Btw, congrats to all the Citus people on your launch. The highest-level architectural difference is, in my opinion, the fact that CockroachDB aims to not trade any features of single-node SQL databases for the distributed nature of our clusters. In contrast to Citus, you get ACID (serializable) transactions regardless of how the data ends up being distributed - so touching more than one shard/node in a transaction does not weaken any consistency guarantees. Similarly, DML statements can modify distributed data with no fuss. The sharding is transparent in crdb; one doesn't need to explicitly declare any partitioning on a table or choose a shard count. We do give you, however, control over the data locality should you need it. The other big architectural difference has to do with our availability/durability guarantees. In crdb, all data is replicated, and the replication is done through a consensus protocol. This means that data appears to be written at once on multiple replicas. Losing any one replica at any point in time is not a big deal; all the data that had previously been written is still accessible. You'll never lose any writes because of a machine failure. This is in contrast to most master-slave type architectures, where generally data is not "atomically" written across the master and the slave. Whenever a slave is promoted to be a master, you will probably incur some data loss. It is my understanding that Citus falls in a flavor of such a master-slave architecture. Now, this all doesn't speak about the current blog post in particular; crdb does not currently have a managed service offering.
- mslot 9y agoIt's always about trade-offs. Citus has ACID transactions within nodes, and ACD transactions across nodes. It does not have distributed snapshot isolation, but that means it also avoids the latency and concurrency penalties that come with distributed snapshot isolation. Because clients talk to Citus using the synchronous postgres protocol, latency is one of the most important factors in performance. A single connection can never do more than 1/latency transactions per second. While you could use more connections, that is often complex, not always possible (e.g. long transaction blocks), and also comes with a significant performance penalty (fewer prepared statements, high SSL overhead, ...). It's important to optimise for latency. We find the Citus transaction model is sufficient for most purposes. If you think of the typical balance transfer example, then there is no way to double spend or overspend. Distributed transactions are atomic and locks in postgres will serialise distributed transactions that update the same rows and ensure constraints are preserved. Importantly, Citus has distributed deadlock detection, which allows generous concurrency at almost no cost to the application (apart from maybe having to reorder transaction blocks). Postgres supports different modes of replication, including synchronous replication to avoid losing any data on node failure. Many users actually prefer asynchronous replication, to avoid the extra round-trip and the broader performance implications that would have. It's also the reason we use streaming replication over other replication mechanisms that might give higher availability. Streaming replication supports higher concurrency and thus gives better throughput. At the end of the day, people use Citus because a single node postgres (or MySQL) database is not performant enough to keep up with their workload. Performance is key. Citus is not a silver bullet for every postgres performance problem, but it has two particular strengths: Minimal pain scaling out for multi-tenant applications that can shard by tenant. - Any SQL query is supported when filtering by partition column, including CTEs, window functions, complex joins, etc, - N times more CPU, memory and I/O bandwidth to process queries, and 1/N as much data to read - High throughput, low-latency transactions, including UPDATE/DELETE with subqueries - Citus Warp can do live migration from postgres (e.g. RDS) using logical decoding - Major ORMs are supported after small data model changes Superpowers for real-time analytics apps (e.g. dashboard). - Bulk loading, up to millions of rows/sec - Massively parallel SQL with subqueries containing outer joins, lateral joins, etc. across all shards when joining by partition column - Massively parallel roll-ups (again with joins) using INSERT...SELECT - Massively parallel DELETE and UPDATE - Integration with other Postgres extensions, such as HLL, PostGIS, pg_partman - Advanced index types, including GIN, GiST, partial indexes - Table partitioning (for bulk delete, faster scans) Citus has a laser-sharp focus on addressing these use cases and makes certain trade-offs because of that.