6 ms·

Congratulations to the team. The replication/partition improvements are significant and much appreciated. My favorite improvements are full text search of JSON

by jph 9y ago

Congratulations to the team. The replication/partition improvements are significant and much appreciated. My favorite improvements are full text search of JSON & JSONB; this makes pg a full replacement for Mongo for my use cases.

zachmax 9y ago
I feel like this feature replaces almost all of Mongo's use cases!
- htsh 9y ago
  I'm interested. When you say almost, can you elaborate on any remaining use cases when you'd use Mongo?
  - byefruit 9y ago
    At my previous company we made heavy use of its lossy compression feature.
    - deleted 9y ago
      [deleted]
    - X86BSD 9y ago
      You would get compression with Postgres running on ZFS.
      - kinghajj 9y ago
        Lossy compression, not lossless.
      - crypto5 9y ago
        In random cloud provider you may not get FS with compression on your machine..
      - sqeaky 9y ago
        Database plus Copy-On-Write file systems sound like a bad idea. I am imagining a modest 100gb database being re-written for every change. I am sure there is some way to work around this, but wouldn't this be the default behavior with a typical database and typical COW file system?
        X86BSD 9y ago
        You might be interested in reading this paper: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
        sqeaky 9y ago
        I am interested and I appreciate the thought and link, however these appear to be the slides to a talk without the actual talk. If so they are of limited use because the slides never have all the information the presenter has, and that information is often just a summary. Is this the correct talk: https://youtu.be/dwMQXLOXUco?t=5380 https://youtu.be/dwMQXLOXUco?t=5380 ?
        X86BSD 9y ago
        Yes, sorry about that, that is the corresponding talk to the slides. Thanks for pointing that out.
        cryptonector 9y ago
        First, you have to make sure the page sizes for the FS and DB match. That's a critical requirement. But yes, you'll get write expansion twice.
      - adamzochowski 9y ago
        Postgresql has compression by default on, for all large text and other large fields that get great benefit of compression. From documentation -" The technique is affectionately known as TOAST (or "the best thing since sliced bread"). "- https://www.postgresql.org/docs/8.0/static/storage-toast.html https://www.postgresql.org/docs/8.0/static/storage-toast.htm...
    - pkhagah 9y ago
      What lossy compression? Were you guys throwing bits into /dev/null?
    - infogulch 9y ago
      Is that related to the cool "hash compression" technology I hear about? Apparently it can compress an arbitrarily large file into just a few bytes, amazing!
  - fnayr 9y ago
    I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much better than if I was forced to make a bunch of tables. This aides in understanding the representation of our game's data, and I believe (but haven't tested) it will be faster than a relational db for my use case, but that's an ancillary benefit anyway.
    - ssijak 9y ago
      But the posters above you said that JSON and JSONB types in Postgres, and functionality around them, eliminated the need to use other databases for document type data. What you are describing can be done with PostgreSQL. One thing that is missing is better client libraries that make use of those data types. Morphia wins for now in that regard.
      - fnayr 9y ago
        My claim wasn't that it didn't fulfill their needs, it was that it doesn't fulfill all needs (gamedev is one example that I'm familiar with). Postgres storing JSON types != All mongo functionality I'm sure I could achieve everything I'm doing in mongo by some roundabout way in Postgres, but if you're doing a large amount of reading/modifying partial fields within JSON structure, it's the exact use case for mongo.
        jerrysievert 9y ago
        https://www.github.com/JerrySievert/MongoLike https://www.github.com/JerrySievert/MongoLike that said, I actually use partial JSONB updates on a regular basis, but I tend to use PLV8 to do the heavy lifting.
        threeseed 9y ago
- jonknee 9y ago
  As soon as Postgres adds the "random data loss" feature they will have a full superset of Mongo.
  - colanderman 9y ago
    You can always turn off fsyncs…
    - knodi 9y ago
      Then you might as well use pg.
      - lima 9y ago
        I think he meant that you can turn off fsyncs in pg in order to add random data loss :-)
        X86BSD 9y ago
        You might be interested in this paper where the author touches on fsync. https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
Thaxll 9y ago
Except that it doesn't scale like MongoDB does. How sharding / cluster works? By default isn't Postgres a single master?
- gshulegaard 9y ago
  I would say that it isn't configured to scale like Mongo out of the box...but that doesn't mean it can't. You can go outside of Postgres core to get multi-master solutions with easy sharding and clustering...with the open sourcing of CitusDB and 2nd Quadrant's pglogical and BDR extensions there are options out there. You can also roll your own (if you really want)...and it is relatively approachable to do so using built-in features like partitioning. And, of course, with the 10 Beta it would seem that logical replication is being brought into core which sets the foundation for future replication features such as BDR to also be brought into core. I would also point out that since Mongo's BI connector fiasco, it would seem more and more Mongo users are finding more reasons to just use Postgres (where relational interfaces are desirable for BI): https://www.linkedin.com/pulse/mongodb-32-now-powered-postgresql-john-de-goes https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr...
  - threeseed 9y ago
    None of what you posted is built in and thus supported by the vendor. That may not matter to you but it's a deal breaker for those of us in enterprises. We can't just be rolling our own versions of PostgreSQL and we can't use CitusDB when it is not supported by other vendors for use with their products. The point still remains that after all these year PostgreSQL's scalability story is still a mess.
    - gshulegaard 9y ago
      I think you might be confused somewhat...PostgreSQL doesn't have "a vendor". The history of Postgres starts at UC Berkeley and now has the PostgreSQL Global Development Group which is a mixture of contributors both community and corporate sponsored: https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/ Of that group, both 2nd Quadrant and CitusDB are represented...so in a way you could say their support is "by the vendor". Not to mention EnterpriseDB which also has support options. > we can't use CitusDB when it is not supported by other vendors for use with their products. CitusDB is no longer a fork, it's an extension...this means so long as your other vendors products support Postgres, they support CitusDB. More over, CitusDB itself will sell you an Enterprise package. > The point still remains that after all these year PostgreSQL's scalability story is still a mess. If by mess you mean specifically there is no knob and dial arrangement in the core of Postgres I would agree. But within the core of PostgreSQL there are primitives which make scaling approachable (I myself am working on a data ingestion process that utilizes table partitioning and hand rolled sharding for PostgreSQL). And there are now a plethora of extensions and tools provided by core contributors such as 2nd Quadrant and CitusDB to offer somewhat out of the box solutions and they even come with support. PostgreSQL is not the right tool for every job and the replication/clustering area has been a a sore spot for Postgres in the past. But it certainly isn't bereft of options now...and the inclusion of logical replication in this beta is only the first step in bringing these options closer/into the core.
_Codemonkeyism 9y ago
Using MySQL/Mongo for some years, major problems with MongoDB Cloud Manager (several hours site down due to Cloud Manager removing the mongo binary), for some time now I use pg for new projects and think it's great. Two gripes: 1. Client libraries in Mongo work nicer with documents than Postgres libraries with JSONB (e.g. Scala Option[] mapping to non existing/existing fields) 2. Why is the Postgres JSON syntax so different? Why not just support SELECT document.field.field instead of (inconsistent) document->'field'. Imho pg JSON syntax is hard to read and new to learn.
- luhn 9y ago
  Re 2: That syntax is already in use by `SELECT schema.column`. I do agree that the syntax is a bit cumbersome and harder to learn, but I'm not sure if they could have done much better while being consistent with SQL.
  - _Codemonkeyism 9y ago
    Why can't pg same the same syntax? I'm sure it could detect if it's a table column or a document field - or am I missing something? Why not handle documents and columns the same, with documents a a kind of hierarchical columns.

by jph 9y ago

zachmax 9y ago

I feel like this feature replaces almost all of Mongo's use cases!

htsh 9y ago
I'm interested. When you say almost, can you elaborate on any remaining use cases when you'd use Mongo?
- byefruit 9y ago
  At my previous company we made heavy use of its lossy compression feature.
  - deleted 9y ago
    [deleted]
  - X86BSD 9y ago
    You would get compression with Postgres running on ZFS.
    - kinghajj 9y ago
      Lossy compression, not lossless.
    - crypto5 9y ago
      In random cloud provider you may not get FS with compression on your machine..
    - sqeaky 9y ago
      Database plus Copy-On-Write file systems sound like a bad idea. I am imagining a modest 100gb database being re-written for every change. I am sure there is some way to work around this, but wouldn't this be the default behavior with a typical database and typical COW file system?
      - X86BSD 9y ago
        You might be interested in reading this paper: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...
        sqeaky 9y ago
        I am interested and I appreciate the thought and link, however these appear to be the slides to a talk without the actual talk. If so they are of limited use because the slides never have all the information the presenter has, and that information is often just a summary. Is this the correct talk: https://youtu.be/dwMQXLOXUco?t=5380 https://youtu.be/dwMQXLOXUco?t=5380 ?
        X86BSD 9y ago
        Yes, sorry about that, that is the corresponding talk to the slides. Thanks for pointing that out.
      - cryptonector 9y ago
        First, you have to make sure the page sizes for the FS and DB match. That's a critical requirement. But yes, you'll get write expansion twice.
    - adamzochowski 9y ago
      Postgresql has compression by default on, for all large text and other large fields that get great benefit of compression. From documentation -" The technique is affectionately known as TOAST (or "the best thing since sliced bread"). "- https://www.postgresql.org/docs/8.0/static/storage-toast.html https://www.postgresql.org/docs/8.0/static/storage-toast.htm...
  - pkhagah 9y ago
    What lossy compression? Were you guys throwing bits into /dev/null?
  - infogulch 9y ago
    Is that related to the cool "hash compression" technology I hear about? Apparently it can compress an arbitrarily large file into just a few bytes, amazing!
- fnayr 9y ago
  I know it's pretty popular to hate on Mongodb now (even more so than it was to love on Mongodb 4 years ago), but there are still areas where it's better than a relational db. In game development, it's extremely helpful (especially as an "indie") to change the structure on a whim so easily. Also based on the design of the game I'm working on, I believe the document structure captures the structure of the data so much better than if I was forced to make a bunch of tables. This aides in understanding the representation of our game's data, and I believe (but haven't tested) it will be faster than a relational db for my use case, but that's an ancillary benefit anyway.
  - ssijak 9y ago
    But the posters above you said that JSON and JSONB types in Postgres, and functionality around them, eliminated the need to use other databases for document type data. What you are describing can be done with PostgreSQL. One thing that is missing is better client libraries that make use of those data types. Morphia wins for now in that regard.
    - fnayr 9y ago
      My claim wasn't that it didn't fulfill their needs, it was that it doesn't fulfill all needs (gamedev is one example that I'm familiar with). Postgres storing JSON types != All mongo functionality I'm sure I could achieve everything I'm doing in mongo by some roundabout way in Postgres, but if you're doing a large amount of reading/modifying partial fields within JSON structure, it's the exact use case for mongo.
      - jerrysievert 9y ago
        https://www.github.com/JerrySievert/MongoLike https://www.github.com/JerrySievert/MongoLike that said, I actually use partial JSONB updates on a regular basis, but I tend to use PLV8 to do the heavy lifting.
        threeseed 9y ago
jonknee 9y ago
As soon as Postgres adds the "random data loss" feature they will have a full superset of Mongo.
- colanderman 9y ago
  You can always turn off fsyncs…
  - knodi 9y ago
    Then you might as well use pg.
    - lima 9y ago
      I think he meant that you can turn off fsyncs in pg in order to add random data loss :-)
      - X86BSD 9y ago
        You might be interested in this paper where the author touches on fsync. https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p...

Thaxll 9y ago

Except that it doesn't scale like MongoDB does. How sharding / cluster works? By default isn't Postgres a single master?

gshulegaard 9y ago
I would say that it isn't configured to scale like Mongo out of the box...but that doesn't mean it can't. You can go outside of Postgres core to get multi-master solutions with easy sharding and clustering...with the open sourcing of CitusDB and 2nd Quadrant's pglogical and BDR extensions there are options out there. You can also roll your own (if you really want)...and it is relatively approachable to do so using built-in features like partitioning. And, of course, with the 10 Beta it would seem that logical replication is being brought into core which sets the foundation for future replication features such as BDR to also be brought into core. I would also point out that since Mongo's BI connector fiasco, it would seem more and more Mongo users are finding more reasons to just use Postgres (where relational interfaces are desirable for BI): https://www.linkedin.com/pulse/mongodb-32-now-powered-postgresql-john-de-goes https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr...
- threeseed 9y ago
  None of what you posted is built in and thus supported by the vendor. That may not matter to you but it's a deal breaker for those of us in enterprises. We can't just be rolling our own versions of PostgreSQL and we can't use CitusDB when it is not supported by other vendors for use with their products. The point still remains that after all these year PostgreSQL's scalability story is still a mess.
  - gshulegaard 9y ago
    I think you might be confused somewhat...PostgreSQL doesn't have "a vendor". The history of Postgres starts at UC Berkeley and now has the PostgreSQL Global Development Group which is a mixture of contributors both community and corporate sponsored: https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/ Of that group, both 2nd Quadrant and CitusDB are represented...so in a way you could say their support is "by the vendor". Not to mention EnterpriseDB which also has support options. > we can't use CitusDB when it is not supported by other vendors for use with their products. CitusDB is no longer a fork, it's an extension...this means so long as your other vendors products support Postgres, they support CitusDB. More over, CitusDB itself will sell you an Enterprise package. > The point still remains that after all these year PostgreSQL's scalability story is still a mess. If by mess you mean specifically there is no knob and dial arrangement in the core of Postgres I would agree. But within the core of PostgreSQL there are primitives which make scaling approachable (I myself am working on a data ingestion process that utilizes table partitioning and hand rolled sharding for PostgreSQL). And there are now a plethora of extensions and tools provided by core contributors such as 2nd Quadrant and CitusDB to offer somewhat out of the box solutions and they even come with support. PostgreSQL is not the right tool for every job and the replication/clustering area has been a a sore spot for Postgres in the past. But it certainly isn't bereft of options now...and the inclusion of logical replication in this beta is only the first step in bringing these options closer/into the core.

_Codemonkeyism 9y ago

Using MySQL/Mongo for some years, major problems with MongoDB Cloud Manager (several hours site down due to Cloud Manager removing the mongo binary), for some time now I use pg for new projects and think it's great. Two gripes: 1. Client libraries in Mongo work nicer with documents than Postgres libraries with JSONB (e.g. Scala Option[] mapping to non existing/existing fields) 2. Why is the Postgres JSON syntax so different? Why not just support SELECT document.field.field instead of (inconsistent) document->'field'. Imho pg JSON syntax is hard to read and new to learn.

luhn 9y ago
Re 2: That syntax is already in use by `SELECT schema.column`. I do agree that the syntax is a bit cumbersome and harder to learn, but I'm not sure if they could have done much better while being consistent with SQL.
- _Codemonkeyism 9y ago
  Why can't pg same the same syntax? I'm sure it could detect if it's a table column or a document field - or am I missing something? Why not handle documents and columns the same, with documents a a kind of hierarchical columns.