5 ms·

Native partitioning, as well as the advancements on the replication and scaling side of things look like good first steps for a distributed SQL system. Can any

Native partitioning, as well as the advancements on the replication and scaling side of things look like good first steps for a distributed SQL system. Can anyone speak to how much closer this brings Postgres to being able to work like Spanner/CockroachDB? Partitioning is great but having to define my own partitions ahead of time isn’t a great solution for us folks who want to be able to spin up new databases on the fly (without the overhead of assigning new ranges for the tables on these new instances.) Obviously CockroachDB has a long way to go before it works as well as a single instance Postgres DB. But how far does Postgres have before it works as well as a multi-instance Cockroach DB?

qaq 9y ago
What is the largest production instance of CockroachDB ?
- lwansbrough 9y ago
  No idea! It’s still in its infancy as a DB. Functional, but probably not worth the switch, unless you really really don’t want to deal with scaling a DB manually (which is where I’m at.)
  - qaq 9y ago
    Right but to have a meaningful answer to your question one would need to know specifics right? As example a spanner setup that will perform on par with the largest reasonable PG setup you could have would be in 100K-150K/month range
- MycroftH 9y ago
  I can't talk about the largest production database, but our largest internal cluster we've tested on so far is 128 machines and hit each one with our own continuous load generators and a chaos monkey (who turns on and off machines at random).
  - qaq 9y ago
    Cool but how much data was it storing and what was the performance?
awoods187 9y ago
Hi all--My name is Andy and I'm a PM here at Cockroach labs. We are currently working through partitioning as a key feature of our 1.2 release scheduled for April 2018. Our solution can be found at this RFC (https://github.com/cockroachdb/cockroach/pull/18683 https://github.com/cockroachdb/cockroach/pull/18683). One thing to note, we don't require you to define your partitions up front. We'd love to get your feedback on this feature--please feel free to comment on the RFC or reach out to me directly at andy@cockroachlabs.com.
doh 9y ago
I think PG is already there and beyond if you consider using Citus. We've pretty large deployment in our production with 45TB of data, across 1,280 cores 4.8TB of RAM. Most of our tables have hundreds of billions of rows that are under write-heavy pressure. Citus is currently handling around 60k writes per second. Most of our SELECT queries run under 200ms, in some cases those are fairly complex. The current setup is a result of long tests done across many different database solutions. We never considered CockroachDB seriously for our production, but we did use or test MongoDB, Cassandra (including ScyllaDB), HBase (including Big Table) and Spanner. We struggled with most, but we used HBase for a year before we moved to PG/Citus. Our expenses dropped by half once we done it as PG is just better suited for our setup. We tested out Spanner fairly heavily as GCP folks tried to convince us to give it a chance. The performance however wasn't even comparable. Citus has its own quirks and it's not perfect, so definitely do your research before you decide to use it. The best part however that you the best SQL engine underneath with all the powerful features and tools that come with it. We for instance utilize heavily triggers and pub/sub directly from PG. Huge portion of our application logic is done on the DB level through UDF. The part we like about citus the most though is the team behind it. They are incredibly helpful and go beyond the scope of a traditional support even if they don't need to.
- barrkel 9y ago
  Do you use the column storage stuff?
  - doh 9y ago
    You mean cstore[0]? No. We've some jsonb but very little. We know what our data look like, so we don't have to. [0] https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw
    - barrkel 9y ago
      That's the bit. Not schema free, but column oriented instead of row oriented. I evaluated Citus a while back comparing to Impala w/ Parquet on HDFS and a couple of other systems. Citus was underwhelming in that context, and the syntax for the fdw awkward owing to keeping base Postgres stock.

qaq 9y ago

What is the largest production instance of CockroachDB ?

lwansbrough 9y ago
No idea! It’s still in its infancy as a DB. Functional, but probably not worth the switch, unless you really really don’t want to deal with scaling a DB manually (which is where I’m at.)
- qaq 9y ago
  Right but to have a meaningful answer to your question one would need to know specifics right? As example a spanner setup that will perform on par with the largest reasonable PG setup you could have would be in 100K-150K/month range
MycroftH 9y ago
I can't talk about the largest production database, but our largest internal cluster we've tested on so far is 128 machines and hit each one with our own continuous load generators and a chaos monkey (who turns on and off machines at random).
- qaq 9y ago
  Cool but how much data was it storing and what was the performance?

awoods187 9y ago

Hi all--My name is Andy and I'm a PM here at Cockroach labs. We are currently working through partitioning as a key feature of our 1.2 release scheduled for April 2018. Our solution can be found at this RFC (https://github.com/cockroachdb/cockroach/pull/18683 https://github.com/cockroachdb/cockroach/pull/18683). One thing to note, we don't require you to define your partitions up front. We'd love to get your feedback on this feature--please feel free to comment on the RFC or reach out to me directly at andy@cockroachlabs.com.

doh 9y ago

I think PG is already there and beyond if you consider using Citus. We've pretty large deployment in our production with 45TB of data, across 1,280 cores 4.8TB of RAM. Most of our tables have hundreds of billions of rows that are under write-heavy pressure. Citus is currently handling around 60k writes per second. Most of our SELECT queries run under 200ms, in some cases those are fairly complex. The current setup is a result of long tests done across many different database solutions. We never considered CockroachDB seriously for our production, but we did use or test MongoDB, Cassandra (including ScyllaDB), HBase (including Big Table) and Spanner. We struggled with most, but we used HBase for a year before we moved to PG/Citus. Our expenses dropped by half once we done it as PG is just better suited for our setup. We tested out Spanner fairly heavily as GCP folks tried to convince us to give it a chance. The performance however wasn't even comparable. Citus has its own quirks and it's not perfect, so definitely do your research before you decide to use it. The best part however that you the best SQL engine underneath with all the powerful features and tools that come with it. We for instance utilize heavily triggers and pub/sub directly from PG. Huge portion of our application logic is done on the DB level through UDF. The part we like about citus the most though is the team behind it. They are incredibly helpful and go beyond the scope of a traditional support even if they don't need to.

barrkel 9y ago
Do you use the column storage stuff?
- doh 9y ago
  You mean cstore[0]? No. We've some jsonb but very little. We know what our data look like, so we don't have to. [0] https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw
  - barrkel 9y ago
    That's the bit. Not schema free, but column oriented instead of row oriented. I evaluated Citus a while back comparing to Impala w/ Parquet on HDFS and a couple of other systems. Citus was underwhelming in that context, and the syntax for the fdw awkward owing to keeping base Postgres stock.