5 ms·

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

by 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.
    - doh 9y ago
      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.
      - barrkel 9y ago
        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.
        doh 9y ago
        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).

by doh 9y ago

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.
  - doh 9y ago
    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.
    - barrkel 9y ago
      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.
      - doh 9y ago
        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).