5 ms·

You know if Postgres can just get some better distributed/elastic support it could do some damage. I switched from Cassandra to citus + pipelinedb (and I'm JVM

by agentgt 10y ago

You know if Postgres can just get some better distributed/elastic support it could do some damage. I switched from Cassandra to citus + pipelinedb (and I'm JVM guy). Postgres is such an awesome platform. I'm planning on looking into some logical decoding.

manigandham 10y ago
Postgres is a completely different type of database, there's not much comparison with cassandra/scylla at all. It also has a way to go with just scale-up performance before it even gets to scale-out.
- agentgt 10y ago
  I wanted a db for realtime analytics. I agree on scale-out but scale-up performance I will say Cassandra is not even close to cost to performance ratio of Postgres w/ extensions for a real time analytics. I have legitimate 6months experience that I wasted on Driud/Cassandra and could not match Postgres in terms of performance. I don't want to hear CAP this and that when I can do all sorts of stream processing a priori. Besides real SQL is easy to understand and hack with then many proprietary query languages. I only tell people so they don't was time like I did.
  - manigandham 10y ago
    > realtime analytics This doesn't mean anything. The only thing that matters is what (and how much) data you have and what queries you want to run. If a relational database can do that for you then cassandra/scylla isn't a good choice.
    - agentgt 10y ago
      > This doesn't mean anything. And neither does your practically trolling comment. You could say that about anything. > The only thing that matters is what (and how much) data you have and what queries you want to run. If a relational database can do that for you then cassandra/scylla isn't a good choice. Theory aside what matters is what I can get to work... so prior knowledge is a big deal. All I said is it would be interesting if Postgres had a better story for elasticity and that it might be a good fit for many. I think many are in my camp and a relational db would fit but are all to often pushed towards to NoSQL. Again I don't want to hear about CAP this and that. You can totally take either system and make it have many of the properties that our touted for each one. People take relational databases and turn them into schemaless or columnar stores all the time with eventually consistency (often with mysql). Particularly with Postgres as it is a platform (it has a powerful extension model). Regardless Cassandra is touted for analytics. The marketing really pushes it for that. Eventually consistency and columnar store is a good fit for real time analytics. Lots of data points, lots of aggregation, get to play with various queries in realtime, elasticity... etc. We will probably hit a wall with Postgres. All and all I think Cassandra is pretty good for what it does. Other than Redis, and maybe RethinkDB I think its my third NoSQL favorite (and yes I know each of those guys has a sweet spot for what they are good at requirement wise).
      - manigandham 10y ago
        It's not trolling - it's the fact that "realtime analytics" are just buzzwords and don't really mean anything. Analytics is just another word for queries and realtime is different for everyone. You can use anything from some in-memory code to redis to postgres or other exotic databases for this and I've yet to come across a situation where those words have helped provide much clarity. Cassandra is wide-column (which is just another buzzword for key/value), not columnar (as in storing data in a column-oriented format) so it's actually not great with aggregations and barely supports queries like that. It is good for range scans across data in a single partition and for spreading load around the cluster if your data is also spread evenly into these partitions, but ultimately my point was that in a thread about cassandra/scylla, it doesnt make much sense to bring up a relational db because it's completely different in every way. If it does work better for you, that's great - and it means is that cassandra/scylla was never a good fit to begin with. The multi-master global replication is a key feature that will likely never be reached by postgres (which is just starting to get scale-up and some logical replication features now) and even mysql only just released the group-replication for multi-master which still only supports the concept of a single total cluster.
        agentgt 10y ago
        Perhaps if you had elaborated like you have now I would not have thought it was trolling :) There is a general consensus what realtime analytics is ( memsql.com apparently tries to define it). It certainly is less nebulous than "big data". Our biggest problem was continuous aggregates. Continuous aggregates are tough for databases (particularly for Postgres since it is MVCC). So it isn't the relational model that is the problem but the algorithms needed for consistency that conflict with constant read and write speed. I did goof by saying Cassandra was column oriented (that is a loaded and confusing term) but people do use it all the time for aggregates (see Druid). Druid by the way is apparently column oriented (going back to my point how you can most data stores into something else). Saying Cassandra is completely different than Postgres isn't really saying something terribly useful. I bring up Postgres not because it is a relational database but because it has some nice features and extensions that seem to be cost effective (compared to just loading everything in memory ala redis which is not cheap). Cassandra certainly does try to offer familiar things to old school SQL guys like myself (namely CQL and various options for consistency)... again it isn't completely different. > If it does work better for you, that's great - and it means is that cassandra/scylla was never a good fit to begin with. You are also assuming some stuff like that we didn't have to compromise. We will still need something like Cassandra as we do want to collect more data points and we do need a place to effectively warehouse this stuff across regions. Also plain Postgres is not a good choice for continuous aggregates as I mentioned before (again I'm going to ignore theory of the relational model... the relational model fits for us because we make it fit... not the other way around). It was one of the reasons why we investigated other technologies. > The multi-master global replication is a key feature that will likely never be reached by postgres I'm sensing some bias here... never... maybe never for postgres core but certainly someone could build an extension or add on.
  - ericfrenkiel 10y ago
    I'm one of the founders at MemSQL, which is designed for real-time analytics. Take it for a spin and see if it works for you.
    - agentgt 10y ago
      Thanks for the recommendation! I will check it out for sure!
      - manigandham 10y ago
        We use memsql, if you're using citus + pipelinedb then memsql will likely solve your problem with a single better solution. Not open-source and uses mysql dialect instead of postgresql but definitely highly recommended.
faizshah 10y ago
Do you mean you use pipelinedb and citus or do you mean you use citus to cluster pipelinedb? I never thought to try mixing the two since pipelinedb has their own commercial product for clustering. You should write a blog post on that if you were able to cluster pipelinedb with citus.
- agentgt 10y ago
  I mix the two. Citus for offline warehousing and pipelinedb for realtime. I'm very pleased with the results. I mix the two by using a message bus (Kafka + RabbitMQ) I spent a lot of time with Cassandra. It is probably great tech I just don't have petabyte data yet. And I know all the stats I want a prior.
  - no1youknowz 10y ago
    This is why I love HN. People give their real-world scenarios. Thank you for recommending pipelinedb, I haven't come across them before. I have kafka and postgres and in need of real-time analytics, this may be the solution I'm looking for. I've used citus in the past, whilst it's excellent for data-warehousing. Unless you scale up the servers, then it's not suitable for real-time counts. I found that lacking for my use-case. If you see this, can you reach out at my name @ gmail dot com, I'd like to chat about issues you have come across.
    - manigandham 10y ago
      If you need both citus + pipelinedb then memsql.com will solve your problem with a much more polished solution. MySQL dialect instead of PostgreSQL but that's rarely a problem for a data warehouse.
      - no1youknowz 10y ago
        Thanks. I actually have scheduled a demo. In the meantime will download the community version and have a play.

by agentgt 10y ago

manigandham 10y ago

Postgres is a completely different type of database, there's not much comparison with cassandra/scylla at all. It also has a way to go with just scale-up performance before it even gets to scale-out.

agentgt 10y ago
I wanted a db for realtime analytics. I agree on scale-out but scale-up performance I will say Cassandra is not even close to cost to performance ratio of Postgres w/ extensions for a real time analytics. I have legitimate 6months experience that I wasted on Driud/Cassandra and could not match Postgres in terms of performance. I don't want to hear CAP this and that when I can do all sorts of stream processing a priori. Besides real SQL is easy to understand and hack with then many proprietary query languages. I only tell people so they don't was time like I did.
- manigandham 10y ago
  > realtime analytics This doesn't mean anything. The only thing that matters is what (and how much) data you have and what queries you want to run. If a relational database can do that for you then cassandra/scylla isn't a good choice.
  - agentgt 10y ago
    > This doesn't mean anything. And neither does your practically trolling comment. You could say that about anything. > The only thing that matters is what (and how much) data you have and what queries you want to run. If a relational database can do that for you then cassandra/scylla isn't a good choice. Theory aside what matters is what I can get to work... so prior knowledge is a big deal. All I said is it would be interesting if Postgres had a better story for elasticity and that it might be a good fit for many. I think many are in my camp and a relational db would fit but are all to often pushed towards to NoSQL. Again I don't want to hear about CAP this and that. You can totally take either system and make it have many of the properties that our touted for each one. People take relational databases and turn them into schemaless or columnar stores all the time with eventually consistency (often with mysql). Particularly with Postgres as it is a platform (it has a powerful extension model). Regardless Cassandra is touted for analytics. The marketing really pushes it for that. Eventually consistency and columnar store is a good fit for real time analytics. Lots of data points, lots of aggregation, get to play with various queries in realtime, elasticity... etc. We will probably hit a wall with Postgres. All and all I think Cassandra is pretty good for what it does. Other than Redis, and maybe RethinkDB I think its my third NoSQL favorite (and yes I know each of those guys has a sweet spot for what they are good at requirement wise).
    - manigandham 10y ago
      It's not trolling - it's the fact that "realtime analytics" are just buzzwords and don't really mean anything. Analytics is just another word for queries and realtime is different for everyone. You can use anything from some in-memory code to redis to postgres or other exotic databases for this and I've yet to come across a situation where those words have helped provide much clarity. Cassandra is wide-column (which is just another buzzword for key/value), not columnar (as in storing data in a column-oriented format) so it's actually not great with aggregations and barely supports queries like that. It is good for range scans across data in a single partition and for spreading load around the cluster if your data is also spread evenly into these partitions, but ultimately my point was that in a thread about cassandra/scylla, it doesnt make much sense to bring up a relational db because it's completely different in every way. If it does work better for you, that's great - and it means is that cassandra/scylla was never a good fit to begin with. The multi-master global replication is a key feature that will likely never be reached by postgres (which is just starting to get scale-up and some logical replication features now) and even mysql only just released the group-replication for multi-master which still only supports the concept of a single total cluster.
      - agentgt 10y ago
        Perhaps if you had elaborated like you have now I would not have thought it was trolling :) There is a general consensus what realtime analytics is ( memsql.com apparently tries to define it). It certainly is less nebulous than "big data". Our biggest problem was continuous aggregates. Continuous aggregates are tough for databases (particularly for Postgres since it is MVCC). So it isn't the relational model that is the problem but the algorithms needed for consistency that conflict with constant read and write speed. I did goof by saying Cassandra was column oriented (that is a loaded and confusing term) but people do use it all the time for aggregates (see Druid). Druid by the way is apparently column oriented (going back to my point how you can most data stores into something else). Saying Cassandra is completely different than Postgres isn't really saying something terribly useful. I bring up Postgres not because it is a relational database but because it has some nice features and extensions that seem to be cost effective (compared to just loading everything in memory ala redis which is not cheap). Cassandra certainly does try to offer familiar things to old school SQL guys like myself (namely CQL and various options for consistency)... again it isn't completely different. > If it does work better for you, that's great - and it means is that cassandra/scylla was never a good fit to begin with. You are also assuming some stuff like that we didn't have to compromise. We will still need something like Cassandra as we do want to collect more data points and we do need a place to effectively warehouse this stuff across regions. Also plain Postgres is not a good choice for continuous aggregates as I mentioned before (again I'm going to ignore theory of the relational model... the relational model fits for us because we make it fit... not the other way around). It was one of the reasons why we investigated other technologies. > The multi-master global replication is a key feature that will likely never be reached by postgres I'm sensing some bias here... never... maybe never for postgres core but certainly someone could build an extension or add on.
- ericfrenkiel 10y ago
  I'm one of the founders at MemSQL, which is designed for real-time analytics. Take it for a spin and see if it works for you.
  - agentgt 10y ago
    Thanks for the recommendation! I will check it out for sure!
    - manigandham 10y ago
      We use memsql, if you're using citus + pipelinedb then memsql will likely solve your problem with a single better solution. Not open-source and uses mysql dialect instead of postgresql but definitely highly recommended.

faizshah 10y ago

Do you mean you use pipelinedb and citus or do you mean you use citus to cluster pipelinedb? I never thought to try mixing the two since pipelinedb has their own commercial product for clustering. You should write a blog post on that if you were able to cluster pipelinedb with citus.

agentgt 10y ago
I mix the two. Citus for offline warehousing and pipelinedb for realtime. I'm very pleased with the results. I mix the two by using a message bus (Kafka + RabbitMQ) I spent a lot of time with Cassandra. It is probably great tech I just don't have petabyte data yet. And I know all the stats I want a prior.
- no1youknowz 10y ago
  This is why I love HN. People give their real-world scenarios. Thank you for recommending pipelinedb, I haven't come across them before. I have kafka and postgres and in need of real-time analytics, this may be the solution I'm looking for. I've used citus in the past, whilst it's excellent for data-warehousing. Unless you scale up the servers, then it's not suitable for real-time counts. I found that lacking for my use-case. If you see this, can you reach out at my name @ gmail dot com, I'd like to chat about issues you have come across.
  - manigandham 10y ago
    If you need both citus + pipelinedb then memsql.com will solve your problem with a much more polished solution. MySQL dialect instead of PostgreSQL but that's rarely a problem for a data warehouse.
    - no1youknowz 10y ago
      Thanks. I actually have scheduled a demo. In the meantime will download the community version and have a play.