6 ms·

1. High performance read/write of Scylla/Cassandra with high availability[1]. It has some limitations for OLTP workloads and require careful planning. Postgres

1. High performance read/write of Scylla/Cassandra with high availability[1]. It has some limitations for OLTP workloads and require careful planning. Postgres without Citus is not really HA and even then companies would often invent custom sharding/cluster management system. 2. Powerful graph query language like Cypher. It might not perform well in real life, but my personal experience left me amazed[2]. There is a number of issues with SQL that could be addressed[3], but current standard is way too much prevalent. 3. Zero impedance mismatch between database and application representation. In database like Smalltalk GemStone it is really seamless experience for developer to write database code and application code[4]. To some extent, MongoDB success can be attributed to this aspect. 4. Datomic temporal capabilities[5]. It is hard to maintain temporal tables in postgres. There are some use cases where you really want query in point of time. Strictly not an OLTP feature, but I can see this be usefully in many scenarios. [1] https://www.youtube.com/watch?v=Fo1dPRqbF-Q [2] https://www.youtube.com/watch?v=pMjwgKqMzi8&t=726s [3] https://www.edgedb.com/blog/we-can-do-better-than-sql [4] https://www.youtube.com/watch?v=EyBkLbNlzbM [5] https://www.youtube.com/watch?v=7lm3K8zVOdY Edited: independence -> impedance

pas 5y ago
> independence mismatch you probably mean impedance mismatch, right? https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
zozbot234 5y ago
Postgres comes with the building blocks for both sharding and HA out of the box, and they're extensively discussed in the docs. You don't need proprietary addons other than as pure convenience.
- zem 5y ago
  don't underestimate the importance of convenience. I'm convinced one of the reasons MySQL had so much more mindshare than postgres back in the day was that it was far easier to get up and running, even if postgres might have been easier to use once everything was set up correctly.
  - BarryMilo 5y ago
    I don't even remember choosing MySQL when I started (15 years ago). It was just so dominant, we didn't question it. Nowadays I would still use it because I assume it is the dumbest database system and that's exactly what I need for my 1-5 user app.
    - 5e92cb50239222b 5y ago
      It still has some features over PostgreSQL that pushed me to choose it (actually MariaDB) for a new project about a year ago, namely multi-master replication. Yes, I know, terrible database and horrible feature, but it really helped in that particular domain. I couldn't find anything decent for postgres, while MariaDB/MySQL have that built-in, with some differences in implementation. Especially for a customer who refuses to pay for his software, because there are some commercial solutions.
  - bostik 5y ago
    That's funny, I must have been an outlier then. I've been using Postgres since 1998, and I tried getting MySQL up first. There was more documentation available for the latter, so it should have been simple. Failed. It just didn't work. Out of frustration I then tried Postgres, because I just wanted a decent database for my project. It was surprisingly easy, I only had to learn about pg_hba.conf to get to a functional state. Everything else was in place out of the box. I've been a happy user ever since. MySQL may have had the mindshare (thanks to prevalence of LAMP) but everything outside the magic happy path was confusing and fragile.
    - ahoka 5y ago
      No one cares what was in 1998, that's the whole point of this discussion. Postgres devs kept digging their heads into sand for many years, saying that high availability is somehow not the task of the database. In reality only the datastores need to be HA/durable in an otherwise stateless architecture.
- jjirsa 5y ago
  Sharding is not the same as natural clustering, because eventually you’ll need to reshard and then you’ll be writing a lot more code.
charlysl 5y ago
> Zero impedance mismatch between database and application representation Does this include informacion hiding/encapsulation? (to prevent saved objects' internal representation from being exposed). Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. This is important because it is a good practice for client code to make no assumptions about the internal representation, accessing data only via the a public interface. If it happens to be exposed by the database, the clients can use it in their queries. If the internal representation changed later on, such clients would be broken. Of course, this can be solved by only allowing data access via, say, well designed restful apis (that don't expose internal details), but this would still provide no guarantees. How about another reason for impedance mismatch, that of storing objects that belong to a class hierarchy?
- vaughan 5y ago
  > good practice for client code to make no assumptions about the internal representation If your internal representation and API start to differ then it adds complexity fast. Its far better to have as close to a 1-1 mapping for your backend and frontend data models as possible.
  - deleted 5y ago
    [deleted]
- takeda 5y ago
  > Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. It actually does, those are views and functions. The real problem with impedance mismatch is that SQL is declarative (you say what you want and database figures out how to get it) when most programming languages are iterative (you say what should be done). The issue is that you have two very different languages. For one you have powerful IDE with type checking auto completion and refactoring capabilities, the SQL often is sent as a string and don't have these benefits. The various ORM are attempts to use iterative and object oriented language to access relational objects using a declarative language. I think JetBrains is addressing the problem the right way. They added Data Grip functionality to their IDEs like PyCharm for example. What it does is that if you connect the IDE to a database and let it download the schema you get the same functionality for the data. Basically it will detect SQL statements in the string and offer the same capability for it as you have with the primary language. At that point the impedance mismatch no longer feels like a mismatch. You basically have two languages, one to obtain data you need and another to process/present it. You can get database to return exact fields you need for your projects and even the object mapping starts feeling unnecessary. Why data is stored in a relational way? Because that's most optimal way to store the data and the way it is stored allows multiple applications access the same data differently. For example with NoSQL you need to know how the data will be used so you correctly plan how it will be stored. If application changes you might need to restructure the entire data. Ultimately the data is the most important thing businesses and it stays, while applications that use it come and go.
vaughan 5y ago
> Zero impedance mismatch I see the problem here being that too many manual optimizations need to be done when implementing a schema. You start with a logical schema (ERD diagram) and then implement it via a physical schema with denormalizations added for efficiency (usually because the relational model has scaling limits with number of joins, or because db migrations are too difficult, or handling unstructured data without introducing a ton of new tables). The db should do the denormalization automatically, allowing the user to interface with their logical schema directly. Another reason is we can't use SQL in the browser - we have to go through many caching layers and API layers which complicate things.
jasfi 5y ago
Postgres has many HA solutions, and it's getting better all the time. Postgres has good performance, benchmarks need to show the various trade-offs systems make so that informed decisions can be made. Feel free to post a link. The Graph model can be made available through extensions. See AGE: https://age.incubator.apache.org/ https://age.incubator.apache.org/ They plan to support OpenCypher. The JSONB type allows for No-SQL like development if that's what you really want.
- zeroc8 5y ago
  Just because you can use JSONB, it doesn't mean that it is as easy to work with as it is the case with MongoDB. So if you really need a JSON store, just use Mongo.
cwp 5y ago
+1 to the application/database mismatch. GemStone is really amazing. I never got to do commercial work with it, but damn is it a great environment. I've never seen anything like it.

pas 5y ago

> independence mismatch you probably mean impedance mismatch, right? https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...

zozbot234 5y ago

Postgres comes with the building blocks for both sharding and HA out of the box, and they're extensively discussed in the docs. You don't need proprietary addons other than as pure convenience.

zem 5y ago
don't underestimate the importance of convenience. I'm convinced one of the reasons MySQL had so much more mindshare than postgres back in the day was that it was far easier to get up and running, even if postgres might have been easier to use once everything was set up correctly.
- BarryMilo 5y ago
  I don't even remember choosing MySQL when I started (15 years ago). It was just so dominant, we didn't question it. Nowadays I would still use it because I assume it is the dumbest database system and that's exactly what I need for my 1-5 user app.
  - 5e92cb50239222b 5y ago
    It still has some features over PostgreSQL that pushed me to choose it (actually MariaDB) for a new project about a year ago, namely multi-master replication. Yes, I know, terrible database and horrible feature, but it really helped in that particular domain. I couldn't find anything decent for postgres, while MariaDB/MySQL have that built-in, with some differences in implementation. Especially for a customer who refuses to pay for his software, because there are some commercial solutions.
- bostik 5y ago
  That's funny, I must have been an outlier then. I've been using Postgres since 1998, and I tried getting MySQL up first. There was more documentation available for the latter, so it should have been simple. Failed. It just didn't work. Out of frustration I then tried Postgres, because I just wanted a decent database for my project. It was surprisingly easy, I only had to learn about pg_hba.conf to get to a functional state. Everything else was in place out of the box. I've been a happy user ever since. MySQL may have had the mindshare (thanks to prevalence of LAMP) but everything outside the magic happy path was confusing and fragile.
  - ahoka 5y ago
    No one cares what was in 1998, that's the whole point of this discussion. Postgres devs kept digging their heads into sand for many years, saying that high availability is somehow not the task of the database. In reality only the datastores need to be HA/durable in an otherwise stateless architecture.
jjirsa 5y ago
Sharding is not the same as natural clustering, because eventually you’ll need to reshard and then you’ll be writing a lot more code.

charlysl 5y ago

> Zero impedance mismatch between database and application representation Does this include informacion hiding/encapsulation? (to prevent saved objects' internal representation from being exposed). Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. This is important because it is a good practice for client code to make no assumptions about the internal representation, accessing data only via the a public interface. If it happens to be exposed by the database, the clients can use it in their queries. If the internal representation changed later on, such clients would be broken. Of course, this can be solved by only allowing data access via, say, well designed restful apis (that don't expose internal details), but this would still provide no guarantees. How about another reason for impedance mismatch, that of storing objects that belong to a class hierarchy?

vaughan 5y ago
> good practice for client code to make no assumptions about the internal representation If your internal representation and API start to differ then it adds complexity fast. Its far better to have as close to a 1-1 mapping for your backend and frontend data models as possible.
- deleted 5y ago
  [deleted]
takeda 5y ago
> Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. It actually does, those are views and functions. The real problem with impedance mismatch is that SQL is declarative (you say what you want and database figures out how to get it) when most programming languages are iterative (you say what should be done). The issue is that you have two very different languages. For one you have powerful IDE with type checking auto completion and refactoring capabilities, the SQL often is sent as a string and don't have these benefits. The various ORM are attempts to use iterative and object oriented language to access relational objects using a declarative language. I think JetBrains is addressing the problem the right way. They added Data Grip functionality to their IDEs like PyCharm for example. What it does is that if you connect the IDE to a database and let it download the schema you get the same functionality for the data. Basically it will detect SQL statements in the string and offer the same capability for it as you have with the primary language. At that point the impedance mismatch no longer feels like a mismatch. You basically have two languages, one to obtain data you need and another to process/present it. You can get database to return exact fields you need for your projects and even the object mapping starts feeling unnecessary. Why data is stored in a relational way? Because that's most optimal way to store the data and the way it is stored allows multiple applications access the same data differently. For example with NoSQL you need to know how the data will be used so you correctly plan how it will be stored. If application changes you might need to restructure the entire data. Ultimately the data is the most important thing businesses and it stays, while applications that use it come and go.

vaughan 5y ago

> Zero impedance mismatch I see the problem here being that too many manual optimizations need to be done when implementing a schema. You start with a logical schema (ERD diagram) and then implement it via a physical schema with denormalizations added for efficiency (usually because the relational model has scaling limits with number of joins, or because db migrations are too difficult, or handling unstructured data without introducing a ton of new tables). The db should do the denormalization automatically, allowing the user to interface with their logical schema directly. Another reason is we can't use SQL in the browser - we have to go through many caching layers and API layers which complicate things.

jasfi 5y ago

Postgres has many HA solutions, and it's getting better all the time. Postgres has good performance, benchmarks need to show the various trade-offs systems make so that informed decisions can be made. Feel free to post a link. The Graph model can be made available through extensions. See AGE: https://age.incubator.apache.org/ https://age.incubator.apache.org/ They plan to support OpenCypher. The JSONB type allows for No-SQL like development if that's what you really want.

zeroc8 5y ago
Just because you can use JSONB, it doesn't mean that it is as easy to work with as it is the case with MongoDB. So if you really need a JSON store, just use Mongo.

cwp 5y ago

+1 to the application/database mismatch. GemStone is really amazing. I never got to do commercial work with it, but damn is it a great environment. I've never seen anything like it.