6 ms·

Pgsql is great in many ways, but not really superior to sql server, it really depends on what you need, e.g. columb encryption, data compression etc. We had ind

Pgsql is great in many ways, but not really superior to sql server, it really depends on what you need, e.g. columb encryption, data compression etc. We had indexing issues on postgres changing underlying OS bc postgres uses a lot of C libraries of the OS, and that is just wrong for a (multiplatform) RDBMS, it makes it less predictable.

speedgoose 3y ago
I don’t know your requirements but wouldn’t using software containers with a reproductible operating system (like nix), or even a simple image like Debian PostgreSQL, fix the predictability problem?
- albertopv 3y ago
  Things are not carved in stone, we had to change the OS and something quite unforeseeable for a RDBSM happened. Imho DBMS should be a sort of self sufficient OS in itself.
- dogma1138 3y ago
  No, at least not in a heavily regulated industry because then other obligations e.g. patching become a much bigger issue. The big benefit of MSSQL and Oracle SQL is that your database server is almost completely detached from the operating system the likelihood of a system update changing how your database works is nill. With Postgres and other databases it’s not a given. Ironically on Windows you get to extract quite a bit of that back since Postgress on Windows comes with most of its own libraries, however that’s also part of the problem where you can easily have material differences between running Postgress on Windows and Linux.
  - speedgoose 3y ago
    I don’t know about your industry but patching and software containers aren’t incompatible from my point of view. I am guessing that you don’t want to validate PostgreSQL on all operating systems, but you could always stick to one using software containers.
    - dogma1138 3y ago
      Containers don’t solve anything, it’s not that the base image doesn’t need to get patched. Postgres is far more dependent on OS libraries which makes it far less predictable.
      - speedgoose 3y ago
        Alright, but if you need to patch a dependency of the database does it matter whether the change is in a shared library or not? Of course you could skip patching the dependencies and ship unsafe software the oracle db way.
        dogma1138 3y ago
        Yes, MSSQL for example is self contained OS or container image updates aren’t going to impact it or at least it’s extremely unlikely that they will. Postgres is dependent on a lot of OS level C libraries that can materially change how things work. This means that there will have to be more testing with Postgres and there will be higher uncertainty between different deployments. All of these can be mitigated and for many organizations the benefits of Postgres might outweigh these downsides but they do exist.
throwaway2990 3y ago
4 years running postgresql on arm in production with 0 issues. (2tb database so kinda small, a lot of indexes tho, a lot of which are on jsonb columns) I can’t think of any feature in sql server I would ever need that PostgreSQL doesn’t have. But sql servers lack of JSON support is a deal breaker for me.
- greggyb 3y ago
  SQL Server has had JSON column types (and supporting functions) since 2016. I don't know you or your workload, so I can't comment on its suitability for you. I would hazard a guess, though, that your last proper due diligence knowledge is seven years out of date, or more.
  - throwaway2990 3y ago
    No. Sql server has json functions. It does not have a json type. It does not support indexing of json. Edit: it’s kinda ironic you think my knowledge of sql server is outdated when you don’t understand the features supported in sql server to begin with.
    - greggyb 3y ago
      You index JSON columns in SQL Server by creating an index on a computed column. I am very open to the strict argument that this is a workaround if you'd like to make that more verbosely. Nevertheless it is very effective and adds minimal IO overhead and is dispatched with one extra line of code (more if you prefer newline-heavy formatting). From a practical perspective this has never been a sticking point for any of numerous clients I have seen using the features, and the performance is as good as any other btree index. You are correct that there is no formal SQL type in SQL Server for JSON. And I am sorry for implying otherwise. Type safety requires a constraint on the column intended to hold JSON. There is no equivalent to Postgres's GIN indices which would allow indexing on an array in the JSON column. Such a requirement would need a normalized table holding the array's values in SQL Server. Whether this is a limitation or a lack of support for JSON, full stop, seems to me a matter open to debate. I have seen many successful projects (and participated in quite a few of those) that utilize JSON in SQL Server databases. I will amend my former statement, though, because it obviously lacked nuance: SQL Server's JSON functionality has covered all use cases that I have had and personally seen, but my experience is obviously much lesser than some others', so you can take this experience with as much salt as you like (:

speedgoose 3y ago

I don’t know your requirements but wouldn’t using software containers with a reproductible operating system (like nix), or even a simple image like Debian PostgreSQL, fix the predictability problem?

albertopv 3y ago
Things are not carved in stone, we had to change the OS and something quite unforeseeable for a RDBSM happened. Imho DBMS should be a sort of self sufficient OS in itself.
dogma1138 3y ago
No, at least not in a heavily regulated industry because then other obligations e.g. patching become a much bigger issue. The big benefit of MSSQL and Oracle SQL is that your database server is almost completely detached from the operating system the likelihood of a system update changing how your database works is nill. With Postgres and other databases it’s not a given. Ironically on Windows you get to extract quite a bit of that back since Postgress on Windows comes with most of its own libraries, however that’s also part of the problem where you can easily have material differences between running Postgress on Windows and Linux.
- speedgoose 3y ago
  I don’t know about your industry but patching and software containers aren’t incompatible from my point of view. I am guessing that you don’t want to validate PostgreSQL on all operating systems, but you could always stick to one using software containers.
  - dogma1138 3y ago
    Containers don’t solve anything, it’s not that the base image doesn’t need to get patched. Postgres is far more dependent on OS libraries which makes it far less predictable.
    - speedgoose 3y ago
      Alright, but if you need to patch a dependency of the database does it matter whether the change is in a shared library or not? Of course you could skip patching the dependencies and ship unsafe software the oracle db way.
      - dogma1138 3y ago
        Yes, MSSQL for example is self contained OS or container image updates aren’t going to impact it or at least it’s extremely unlikely that they will. Postgres is dependent on a lot of OS level C libraries that can materially change how things work. This means that there will have to be more testing with Postgres and there will be higher uncertainty between different deployments. All of these can be mitigated and for many organizations the benefits of Postgres might outweigh these downsides but they do exist.

throwaway2990 3y ago

4 years running postgresql on arm in production with 0 issues. (2tb database so kinda small, a lot of indexes tho, a lot of which are on jsonb columns) I can’t think of any feature in sql server I would ever need that PostgreSQL doesn’t have. But sql servers lack of JSON support is a deal breaker for me.

greggyb 3y ago
SQL Server has had JSON column types (and supporting functions) since 2016. I don't know you or your workload, so I can't comment on its suitability for you. I would hazard a guess, though, that your last proper due diligence knowledge is seven years out of date, or more.
- throwaway2990 3y ago
  No. Sql server has json functions. It does not have a json type. It does not support indexing of json. Edit: it’s kinda ironic you think my knowledge of sql server is outdated when you don’t understand the features supported in sql server to begin with.
  - greggyb 3y ago
    You index JSON columns in SQL Server by creating an index on a computed column. I am very open to the strict argument that this is a workaround if you'd like to make that more verbosely. Nevertheless it is very effective and adds minimal IO overhead and is dispatched with one extra line of code (more if you prefer newline-heavy formatting). From a practical perspective this has never been a sticking point for any of numerous clients I have seen using the features, and the performance is as good as any other btree index. You are correct that there is no formal SQL type in SQL Server for JSON. And I am sorry for implying otherwise. Type safety requires a constraint on the column intended to hold JSON. There is no equivalent to Postgres's GIN indices which would allow indexing on an array in the JSON column. Such a requirement would need a normalized table holding the array's values in SQL Server. Whether this is a limitation or a lack of support for JSON, full stop, seems to me a matter open to debate. I have seen many successful projects (and participated in quite a few of those) that utilize JSON in SQL Server databases. I will amend my former statement, though, because it obviously lacked nuance: SQL Server's JSON functionality has covered all use cases that I have had and personally seen, but my experience is obviously much lesser than some others', so you can take this experience with as much salt as you like (: