9 ms·
How does PostgreSQL 10 compare with Cassandra for BigData requirements?
by davedx 9y ago
How does PostgreSQL 10 compare with Cassandra for BigData requirements?
- SEJeff 9y agoDefine "big data"? You can buy normal tier 1 server vendor hardware with 6T of memory. If it fits into 1 machine or a few, it isn't "big data".
- deleted 9y ago[deleted]
- willtim 9y agoAnd the postgres maximum table size is 32T.
- pulam 9y agoI've heard this 6TB number thrown around a bit recently, but no one can actually provide me with links to hardware I can buy - has anyone successfully setup a machine with this much ram who can post links to what they used?
- qaq 9y agohttp://www3.lenovo.com/us/en/data-center/servers/mission-critical/System-x3950-X6/p/WMD00000002 http://www3.lenovo.com/us/en/data-center/servers/mission-cri...
- okaram 9y agoDon't know about hardware you can buy, but AWS will rent you a machine with 3TB Ram by the hour :) https://aws.amazon.com/ec2/instance-types/ https://aws.amazon.com/ec2/instance-types/
- runako 9y agohttp://www.thinkmate.com/systems/servers/rax#max-ram:min-6-tb http://www.thinkmate.com/systems/servers/rax#max-ram:min-6-t...
- wiremine 9y agoMaybe. I recently updated some of my company's internal training material, and discovered there still isn't a single (good) definition of "Big Data". Most definitions talk evaluating data on four dimensions: volume, variety, velocity and veracity. That's still a vague, but it's more helpful than looking at volume alone. Besides, if we picked machine size as _the_ definition, it would change every year. And that feels like a bad way to talk about big data. (BTW, I'm not a huge fan of the term in general: I've seen a lot of companies sold a bill of good because some sales guy convinced them they needed a Big Data solution instead of something like Postgres)
- wjossey 9y agoI've also struggled with the definition of Big Data. I typically see "size" as being the line of demarcation people throw around when thinking about Big Data, but I think it's it's actually size + latency that's the real definition. If I have a 12TB dataset, and I need to be able to find a single record in that dataset, and return the results to a process in less than one second, I don't believe that needs to be considered "big data". The dataset is "large", but the latency / access pattern surrounding that data is low enough that you can solve that with a handful of hard drives and your data sorted on disk. If I have a 12TB dataset and I need < 10ms latency on random lookups, that is now probably big data. To guarantee those sort of lookup times for random IO, I either need very expensive hardware, or a software based solution to distribute my data to multiple machines. If I have 10GB of census data, and I need to run an analysis that shows what are the 100 most similar cities (non approximation) to Los Angeles in terms of demographic make up, and I need an answer in less than 1 second, that is also probably big data. The analysis is easily parallelized, but if done serially on a single machine is a "slow" process. I realize this is a moderately unconventional way of approaching Big Data, but it's how I've started to structure some of my thinking and explanations around why it's not just size, but size + latency (which is inherently a question of what you're trying to do with the data).
- deleted 9y ago[deleted]
- imtringued 9y agoTo be honest 6 TB of data isn't that much. I'd say big data starts at around one petabyte because one petabyte requires 3 servers with 45 [1] 8TB hdds. At this point your data is so large it's impossible to store it on a single physical machine. "big data" usually doesn't require transactions though or at least no transactions across servers so you could probably get away with sharding postgresql via citus. [1] http://www.45drives.com/products/storage/ http://www.45drives.com/products/storage/
- SEJeff 9y agoI agree with this response. Those aren't very big either though. The Dell DSS7000 series servers[1] support 90 x 8TB drives (We run a ceph cluster on 4 of the DSS7500 storage nodes). [1] http://www.storagereview.com/dell_announces_availability_of_its_dss_branded_servers http://www.storagereview.com/dell_announces_availability_of_...
- Thaxll 9y agoExcept that it's just stupid to do that, you don't want to store that much data in one host, it goes against any sane architecture design, good luck when you're mb / cpu dies. Splitting data on smaller / cheap hosts is the way to go for ops / scaling / backups.
- deleted 9y ago[deleted]
- Chickenosaurus 9y agoI am interested in the answer to this question, too.
- noncoml 9y agoBy no means an expert, but isn’t it Apples and Oranges? Cassandra is an eventual consistent database. Postgres is ACID. My 2c: you don’t need Cassandra or other BigData databases. If you did, you probably wouldn’t ask the question.
- peatmoss 9y ago> you don’t need Cassandra or other BigData database. If you did, you wouldn’t ask the question probably. ...and by the time you do, Postgres improvements will have rendered your Big Data medium-sized anyway :-)
- fortytw2 9y agoyou can comfortably fit 8TB of data on a single box running postgres, more dependent on your hardware :)
- kstrauser 9y agoThat's about 1/250th of our working set size. :) Of course, that's in a Spark cluster running massively parallel queries and not on a single (however large) node.
- ibotty 9y agoYou did not ask that question. I assume you knew before that PostgreSQL is not the tool for your workload.
- kstrauser 9y agoI didn’t ask a question. I love PostgreSQL for small relational stuff (where “small” is up to a few TB) and use it any time it’s appropriate. I wouldn’t classify it as a Big Data store, though. That’s no criticism at all. I wouldn’t use Spark for a transactional DB because it’s not the best tool for the job. Same for PostgreSQL when the dataset grows far beyond what a single instance can reasonably process.
- snissn 9y agoDoes Cassandra allow aggregate functions like sum() or avg() yet?
- Chickenosaurus 9y agoYes, Cassandra supports common aggregation functions as well as user-defined aggregators which have to be created in Java.
- super_mario 9y agoYes in newest versions it does. But for any non-trivial analytics you would use Apache Spark (most likely Spark/SQL). Cassandra's CQL is very limited in terms of types of queries it supports.
- gshulegaard 9y agoUltimately it depends on your use case and how much tuning/schema design/explicit management you are willing to do. My team lead did a talk at NGINX Conf 2017 which touched on how we tuned Postgres to handle 1 Billion metrics per day for NGINX Amplify (https://www.nginx.com/products/nginx-amplify/ https://www.nginx.com/products/nginx-amplify/). I would link it, but it seems it hasn't been published by our Conf team yet. We did this on 9.4 so the changes outlined here regarding native partitioning, parallel queries, replication improvements, etc. would likely make doing a similar scaling structure easier and just straight up more performant. I wouldn't classify 1 Billion metrics a day as "Big Data". I also would point out that our use case is unfairly biased to "recent" data rather than "all historical" data...which allows us to make some query optimizations that wouldn't be acceptable in the latter use case. But we are using Postgres to host a constant, write-heavy workload that conventional wisdom dictates it is unsuited for. Moreover, it was/is fairly simple to implement, understand, and scale indefinitely. Personally, I think both Redshift and CitusData are various degrees of proof that scaling Postgres to handle these types of workloads is possible. For me, the reason you want to consider using a Big Data focused tool (such as Cassandra) is at some point you end up fighting your tool to make a square peg fit in a round hole. At some point it makes more sense to adopt a specialized tool rather than work to specialize your implementation of a specific tool. In all fairness, we are considering just that. Our road map actually has Scylla in the near future. It's not that we don't think Postgres could handle the load, but rather we think it will be easier to move specific data to Scylla and in the long run will save us resources (primarily in man hours and potentially in compute requirements as well). Even if we move all our timeseries data to a specialized tool, we won't be getting rid of Postgres. For most/if not all our other persistence requirements Postgres is more than adequate. This is one of the reasons we chose to start with Postgres: even once we hit a scale that specialized tools made sense, Postgres would still be useful for everything else that doesn't require a specialized tool. Moral of the story: Postgres will get you a lot farther than many give it credit for...and if you are just starting out I would highly encourage you to just pick Postgres and see how far it can take you. If you hit the limit, replace that specific part. Edit: Also, there appears to be some movement on Columnar indexes which is worth keeping an eye on: https://blog.2ndquadrant.com/postgresql-10-roadmap/ https://blog.2ndquadrant.com/postgresql-10-roadmap/
- manigandham 9y agoThey are completely different: Postgres - Solid single-node relational database with SQL standards support, JSON, fulltext search, custom data types, ACID transactions, foreign data access, and more. HA/scaling through replication and some 3rd party offerings for automatic sharding, failover and multi-master if you need it. Great as the operational source-of-truth for your core data. Cassandra - Distributed wide-column (basically advanced key/value) meant to run as a cluster of machines with native support for multiple data centers. Limited "SQL" support which is really only used for defining tables. Data access is very different (everything is an upsert or delete) with variable per-query consistency settings. Great if you need data spread globally, 100% availability, eventual consistency fast key/value, and some interesting data access patterns. If you want high-performance Cassandra, look at ScyllaDB first. If you need something in between Cassandra and Postgres, look at CockroachDB.