Hacker News Reader

Comment by lwansbrough | Hacker News Reader

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?

View on HN

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.

Do you use the column storage stuff?

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

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.

Understood. Different use cases. We like schema, gives us an opportunity to keep data in check. We also communicate through protobuf so there is no benefit in schema-free database for us.

I know that Heap Analytics is using jsonb to have schema-free setup on citus and I think it's working well for them.

That's the second time you've thought I was talking about schema free. Column stores have nothing to do with schema free. Completely orthogonal.

Column-oriented stores normally have just as much schema as row-oriented stores. What they're faster at is scans where the full row tuple is not required to compute the result. Storing the data in columnar format rather than row also means much better compression: similar data is brought closer together.

You're correct, I misunderstood you.

Column stores are great, for some tasks and have disadvantages in others. Redshift was I believe the first implementation at top of PG so it's doable, however not sure that PG itself is a good engine for it.

In our case, where we need to have access to multiple columns at once, column stores (including the ones that clump them as families/groups) proved to be slower and required more resources than the row-oriented stores.

I think this really goes case by case based on your needs. If we would benefit from column-oriented DB we would not chose PG/Citus but something else (probably would stay still on Hbase).

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). 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.

What is the largest production instance of CockroachDB ?

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.)

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

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).

Cool but how much data was it storing and what was the performance?

Reply on news.ycombinator.com