How does PostgreSQL 10 compare with Cassandra for BigData requirements?
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.
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.
...and by the time you do, Postgres improvements will have rendered your Big Data medium-sized anyway :-)
Of course, that's in a Spark cluster running massively parallel queries and not on a single (however large) node.
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.
So, that person above may not need it today, but they may need it sooner rather than later.
If one is starting a new project, hence contemplating what DB to use, starting with an ACID db is a safe bet in most cases(unless of course they are already starting with a huge amount of data).
By the time the outgrow the ACID database they will have a better idea of what exactly they need and more importantly they will have the resources to make the switch.
BTW: riak is hopefully not dead yet, let's see what bet365 can do with it..
I'm co-founder of a company, doing all of the technical work, and I too am using a very vanilla stack: Postgres, Elasticsearch, Redis, Memcache, Rails. However, having worked on massive scale-out projects before, I recognize where my pain points are going to be with my tech choices.
[1] I'm storing calendar data in postgres. This data is primarily key/value based, needs to be persistent, and on a per user basis potentially is tens of thousands of records. This is a medium term problem, and I'll likely move this data out of postgres and into a scale out solution sooner rather than later. I need relatively fast lookup times, but this is primarily a "high write, low read" scenario, that Cassandra is well suited for.
[2] I'm storing a bunch of text from blog posts, news articles, etc., in Postgres. I really dont' need the data to be in postgres, it's just a handy place to store it to synchronize with Algolia & Elasticsearch, where the true work happens. However, I dont' want to hold onto TBs & TBs of news articles in a postgres system. As that system scales out, it'll be unnecessarily expensive, as I don't need all the bells and whistles I'm getting from Postgres, for what is effectively a form of cold storage. This is a long term problem, and I likely won't tackle it for well over a year.
So, you're right, I'm not contemplating what DB to use, but I'm definitely aware of why I won't just leveraging the same database in perpetuity. Being focused on delivering customer value is vital, and that's my intent; however, scalability and cost structure do matter over the long term, so an understanding of where one will go is good as well.
Citus is more focused on scalable OLTP rather than OLAP scenarios, giving you essentially an automatically sharding postgres database. Heap does use it for analytics but that's more so because of the JSON support and indexing in postgres itself.
Can you share some docs with me around how citus is more OLTP based? Looking to be educated.
BigQuery streaming ingest also uses in-memory buffering into BigTable until it's written to disk, just like Cassandra with memtables flushed into on-disk SSTables.
Citus is more manual work than MemSQL and doesnt support columnstores native, although you can use it with their cstore extension to get both. It's just a different design giving you a postgres database that automatically shards vs MemSQL's many optimizations around HA, replication, and very fast analytics performance.
Try the features page: http://www.memsql.com/features/
This article had a nice overview. Somewhat outdated but mostly still up to date. https://lnkd.in/g4anCf5
I’ll say it seems precarious to me to mix and match row store in memory data with a columnar store. Is it never a performance issue to have these two systems potentially in contention with one another for resources?
Version 6 is even faster, you can try out the beta: http://www1.memsql.com/beta-6.3-features.html
Based on what ? There is no evidence or logic behind this.
If you know that you are producing say 1GB a day of data that you must store then it is sensible and prudent to select a technology that can support your needs a year or two from now.
And this idea that you always have resources to make a switch later on is rarely the case. The more common scenario is that a team is established to implement something, it moves into production and then the team either disbands or moves onto something else. Why do you think so many people try and plan for the future ?
In most enterprise companies at least it would be considered a failure to have a project that in a year's time required another business case just to replace the database that the team should've appropriately selected in the first place.
Designing for the future is a horrible choice because where do you stop?
Eg. Why are you sure that Cassandra, or whatever you choose, will be good enough for the future? Maybe you will need a Spanner type database or something even more big/consistent/feature-full.
Yes, I understand this. I would not want to get bogged down on infrastructure when trying to deliver early versions of a product. At the same time, I think database choice is one of the more important tech stack decisions you should make early on.
Cassandra also sounds like a good bet for the type of data I'm interested in storing (ML model inputs, so lots of key -> numerical value data). But my experience is mostly around RDBMS and some NoSQL. Hence the question. Thanks for the answer!
How big are the numerical value data? AFAIK Cassandra does like large amount of data per value. https://docs.datastax.com/en/cql/3.3/cql/cql_reference/refLi...
Also what do you expect the total amount of data to be?
BTW, I just recommended to start with PostgreSQL because it just works and will have your back no matter what ways you like to use retrieve you data.
However if you are absolutely sure you don’t require secondary indexes, joins, etc, and just need a KV database, Cassandra will work fine too.
Citus specifically cite analytics and write throughput as their strengths. I believe them. They do seem to charge for shard rebalancing and some other features.
Greenplum comes with an inbuilt data science toolkit (MADlib) and has a long history of dealing with intensive workloads. I don't know if there is a commercial version with extra features. I don't think so, but I haven't looked closely.
Greenplum also gave birth to HAWQ, which uses the Greenplum distributed/parallel query planner to plan queries over Hadoop.
Disclosure: I work for Pivotal, we do most Greenplum development.
As for elasticsearch, I have found it to be temperamental. The "out of the box" experience was usually unhappy. For logging I've seen people move to other tools (splunk, stackdriver) more than once.
2 TB/day is fairly easy... as long as you have a bit of experience AND one of the database that can scale horizontally AND the company buys the amount of hardware that is required.
StackDriver/Sumologic/logentries are cloud solutions, so obviously one doesn't have to deal with the database ever.
On the other hand, if I have structured-enough data, I'm going to prefer relational tools for it. Hammers and algebraic nails and so on.
Did you miss this: "(unless of course they are already starting with a huge amount of data)"?
No. Cassandra is a database with selectable consistency.
You can set it to be strongly consistent (ALL or QUORUM) with the tradeoff being worse performance.
If it fits into 1 machine or a few, it isn't "big data".
"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.storagereview.com/dell_announces_availability_of_...
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)
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).
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/). 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://wiki.postgresql.org/wiki/PostgreSQL_derived_database...
Cassandra's CQL is very limited in terms of types of queries it supports.