6 ms·

> Yeah the fact that they forbid benchmarks pretty much tells you all you need to know. I don't think this is the case. Oracle and MS SQL have the same clause

> Yeah the fact that they forbid benchmarks pretty much tells you all you need to know. I don't think this is the case. Oracle and MS SQL have the same clause and it's pretty hard to believe they aren't competitive with postgres and mysql given the resources behind the former. More so it's just annoying of them to have this restriction.

nullc 5y ago
How is that hard to believe? Clearly Oracle itself doesn't believe it. :P
athrowaway3z 5y ago
Ha. This is extremely easy to believe.
- cameronh90 5y ago
  I was on a team that migrated an app from SQL Server to Postgres a some years ago, and the performance dropped by around 30%. Of course, due to the benchmark rules, I'm not allowed to post about it... ;) That being said, in our application it came down to three main issues: - Postgres doesn't allow optimizer hints, and the optimizer generally produced worse plans than Microsoft's (e.g. CTE were an optimization fence in Postgres). - Postgres at the time did not support parallel queries. - Postgres connection handling is terrible. Pgbouncer helps but introduces its own problems. - Database bloat / vacuum issues Generally I found SQL Server would perform very well with no tuning and minimal maintenance. And generally, if it doesn't do the right thing, a query hint will fix it. Postgres required a lot more configuration and testing to obtain acceptable performance. We still switched to Postgres. It has lots of other benefits - and none of the licensing.
  - darksaints 5y ago
    The first two are already fixed, the third is fixed in v14. The fourth will likely never be fixed as it is a byproduct of the extremely fine grained transactional capabilities in Postgres (which brings the advantage of fewer deadlocks). However, if you are disinclined to make that tradeoff, I believe that there is a backend in development that is designed to reduce bloat, using the new pluggable storage engine API.
    - eatonphil 5y ago
      Where can I read more about improved connection handling in v14?
      - darksaints 5y ago
        https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-improvements-for-handling-large-number-of-connections/ https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-...
    - cameronh90 5y ago
      I'm aware some of these things have changed, but equally SQL server has likely developed since then too. That said for #1 are you saying PG now allows query hints or just the specific example is fixed? There are a lot of cases still where the optimiser does the wrong thing and with PG you're powerless to fix it. With #3, do you have any more information about that? I can't find any on a brief Google.
      - darksaints 5y ago
        I was specifically referring to the optimization fence on CTEs. PG still doesn't have query hints, and it seems like the core team has an ideological opposition to building query hint capabilities. Their stance has been that query hints often lead to suboptimal behavior because queries often don't change over time, but data sets do, so relying on statistics allows your query plans to change with different data. A query hint, on the other hand, may lead to an optimal query execution today, but a terrible one tomorrow. Over the years Postgres has steadily chipped away at almost all of the reasons why I would ever want query hints. Only two frustrations remain: * the optimizer is not aware of TOAST storage. This means that for tables with lots of large objects (geospatial in my case), the optimizer only sees the costs of the table with a pointer to the large object, as opposed to the cost of pulling table with the large object. * The optimizer only has singular statically configured costs for functions and aggregates, but costs can actually dramatically vary based on the size of the data inputs, the size of the aggregate "window", etc. Overall, these are rarely a big enough deal for me to not choose Postgres. I see the default planner behavior as better than the default planners for both Oracle and SQL Server, but it is still possible that the lack of query hints could be a deal breaker. If you get a chance, give it another try...I think you might be surprised at how far Postgres has come since the 9.X days. As far as number 3, I guess I should clarify that they've dramatically reduced the connection overhead, but I shouldn't have said "fixed". It may or may not be reduced enough for your requirements. Here is a blog post with some links to various commits: https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-improvements-for-handling-large-number-of-connections/ https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-... Some rudimentary benchmark comparisons here: https://github.com/digoal/blog/blob/master/202008/20200817_01.md https://github.com/digoal/blog/blob/master/202008/20200817_0... Also, wrt #4, I found one of the projects to reduce bloat with a different storage engine: https://cybertec-postgresql.github.io/zheap/ https://cybertec-postgresql.github.io/zheap/
  - jiggawatts 5y ago
    > Generally I found SQL Server would perform very well Every database engine makes different trade-offs, or does some things better than others. I've found that SQL Server handles complex queries over large data sets fantastically well, easily outperforming Oracle or open-source DBMS systems in like-for-like scenarios. It often papers over missing indexes shockingly well, because it automatically uses temporary hash indexes that it can build in parallel. Up to about ten million rows this can be fast enough that developers don't even notice that they forgot an index! However, it has very high minimum latency that cannot be fixed through any mechanism. Not via RDMA, shared memory, named pipes, local loopback, or any other means. It always takes SQL Server about 125-150 microseconds to respond to a query, not matter how trivial, such as "SELECT 1". This makes it terrible as a KV store, and makes some types of ORM very sluggish when chasing references in code. Similarly, there have been several articles published saying that while Postgres is well rounded and feature-full, it has high write-amplification that makes it an absolute no-go past a certain scale. Many orgs have written long blogs about how they started with PG and were forced to migrate to MySQL to get to the required write throughput. I feel that the DBMS space is still immature, and there are very fundamental things missing. E.g.: There are no enterprise-ready open source pure in-memory relational database engines that have capabilities comparable to SAP HANA. I'm also yet to see a DBMS with decent programmability, so that developers don't need to write App->ORM->DB layers over and over.
- defaultname 5y ago
  No one who has done serious work across the database platforms believes they restrict benchmarks because they're behind or uncompetitive. They both make very capable, competitive database systems. The problem, as cited in the linked page, is that "It takes much more work to refute bad benchmarks than to produce them". We see on HN with regularity where people post egregiously flawed benchmarks, usually to demonstrate some preconceived notion or other. And while that can usually be ascribed to ignorance or sloppiness, it's trivial for vendors to contrive benchmarks that are purpose-suited to make their own product look good and the competition look bad, however completely artificial and unrealistic the scenario is. DeWitt clauses are an abomination. They should not exist. But I get why they exist and are threatened, even if they are basically never actually enforced (seriously though -- are they ever actually enforced?).
  - still_grokking 5y ago
    I think even flawed benchmarks are good benchmarks. Because real world code isn't optimized to the max. Because real world code makes bad or even wrong assumptions. Because there are usually much more requirements to real world code than "be as fast as you can". Additionally, those flawed benchmarks make you think about a lot of interesting details. Details you usually don't think about when producing real world code. Also it's a little bit alike as with statistics. "Don't trust any statistics that you haven't fabricated yourself". Still statistics are useful in general.
    - aidenn0 5y ago
      There's flawed benchmarks and there are completely wrong benchmarks. I've seen so many benchmarks where the timing or loop overhead dominated to the point that they weren't measuring anything but the cost of taking timing values I've seen benchmarks where they run a single time without warming up the disk cache, so whichever benchmark they happen to run first shows as 2 orders of magnitude slower. I've seen benchmarks where the standard deviation was much larger than the difference between the times reported, but they didn't bother to check for this. I've seen benchmarks where they don't even use the same algorithm, and the difference in the algorithm chosen dominates any other concerns.
eitland 5y ago
> Oracle and MS SQL have the same clause and it's pretty hard to believe they aren't competitive with postgres and mysql given the resources behind the former. I'm not trying to refute you here, just add some nuance. I've used Oracle extensively for 3 years: installation, configuration, tuning, writing code against, querying for reporting, updating customer configuration etc. There are things you can do in Oracle that can't be done in Postgres yet. But I've never ever experienced a practical performance problem on Postgres that would magically disappear with Oracle. I assume they are there (I'm not into running huge DBs with thousands of subscribers). Also in my opinion it all comes down to knowing your database: In 2009 I saw a Sybase Adaptive Server Anywhere -> MSSQL at the time migration fail miserably at the first migration attempt. Everything was ported correctly but it turns out Sybase ASA had "implicit indexes" that we needed to replicate. In most cases I'd choose Postgres (or even SQLite) myself and in the rest I wouldn't immediately choose Oracle or MSSQL but rather stop and think carefully, do some tests and only then decide, I do not take it for granted that commercial databases perform better and they absolutely are more painful to manage.
- eatonphil 5y ago
  > I do not take it for granted that commercial databases perform better and they absolutely are more painful to manage. Oh for sure. It took me forever to figure out how to get Oracle running in a docker image as a new Oracle user. I'm not sure if I ever even figured out how to create a table. Postgres was hard to learn initially coming from the simplicity of MySQL and Oracle feels another step harder. I didn't say I expect commercial software to perform better though, just that I expect them to be competitive. I also wouldn't choose a commercial db personally but dunking on them doesn't make sense either. The engineers behind them are just as good as those behind OSS.

nullc 5y ago

How is that hard to believe? Clearly Oracle itself doesn't believe it. :P

athrowaway3z 5y ago

Ha. This is extremely easy to believe.

cameronh90 5y ago
I was on a team that migrated an app from SQL Server to Postgres a some years ago, and the performance dropped by around 30%. Of course, due to the benchmark rules, I'm not allowed to post about it... ;) That being said, in our application it came down to three main issues: - Postgres doesn't allow optimizer hints, and the optimizer generally produced worse plans than Microsoft's (e.g. CTE were an optimization fence in Postgres). - Postgres at the time did not support parallel queries. - Postgres connection handling is terrible. Pgbouncer helps but introduces its own problems. - Database bloat / vacuum issues Generally I found SQL Server would perform very well with no tuning and minimal maintenance. And generally, if it doesn't do the right thing, a query hint will fix it. Postgres required a lot more configuration and testing to obtain acceptable performance. We still switched to Postgres. It has lots of other benefits - and none of the licensing.
- darksaints 5y ago
  The first two are already fixed, the third is fixed in v14. The fourth will likely never be fixed as it is a byproduct of the extremely fine grained transactional capabilities in Postgres (which brings the advantage of fewer deadlocks). However, if you are disinclined to make that tradeoff, I believe that there is a backend in development that is designed to reduce bloat, using the new pluggable storage engine API.
  - eatonphil 5y ago
    Where can I read more about improved connection handling in v14?
    - darksaints 5y ago
      https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-improvements-for-handling-large-number-of-connections/ https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-...
  - cameronh90 5y ago
    I'm aware some of these things have changed, but equally SQL server has likely developed since then too. That said for #1 are you saying PG now allows query hints or just the specific example is fixed? There are a lot of cases still where the optimiser does the wrong thing and with PG you're powerless to fix it. With #3, do you have any more information about that? I can't find any on a brief Google.
    - darksaints 5y ago
      I was specifically referring to the optimization fence on CTEs. PG still doesn't have query hints, and it seems like the core team has an ideological opposition to building query hint capabilities. Their stance has been that query hints often lead to suboptimal behavior because queries often don't change over time, but data sets do, so relying on statistics allows your query plans to change with different data. A query hint, on the other hand, may lead to an optimal query execution today, but a terrible one tomorrow. Over the years Postgres has steadily chipped away at almost all of the reasons why I would ever want query hints. Only two frustrations remain: * the optimizer is not aware of TOAST storage. This means that for tables with lots of large objects (geospatial in my case), the optimizer only sees the costs of the table with a pointer to the large object, as opposed to the cost of pulling table with the large object. * The optimizer only has singular statically configured costs for functions and aggregates, but costs can actually dramatically vary based on the size of the data inputs, the size of the aggregate "window", etc. Overall, these are rarely a big enough deal for me to not choose Postgres. I see the default planner behavior as better than the default planners for both Oracle and SQL Server, but it is still possible that the lack of query hints could be a deal breaker. If you get a chance, give it another try...I think you might be surprised at how far Postgres has come since the 9.X days. As far as number 3, I guess I should clarify that they've dramatically reduced the connection overhead, but I shouldn't have said "fixed". It may or may not be reduced enough for your requirements. Here is a blog post with some links to various commits: https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-improvements-for-handling-large-number-of-connections/ https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-... Some rudimentary benchmark comparisons here: https://github.com/digoal/blog/blob/master/202008/20200817_01.md https://github.com/digoal/blog/blob/master/202008/20200817_0... Also, wrt #4, I found one of the projects to reduce bloat with a different storage engine: https://cybertec-postgresql.github.io/zheap/ https://cybertec-postgresql.github.io/zheap/
- jiggawatts 5y ago
  > Generally I found SQL Server would perform very well Every database engine makes different trade-offs, or does some things better than others. I've found that SQL Server handles complex queries over large data sets fantastically well, easily outperforming Oracle or open-source DBMS systems in like-for-like scenarios. It often papers over missing indexes shockingly well, because it automatically uses temporary hash indexes that it can build in parallel. Up to about ten million rows this can be fast enough that developers don't even notice that they forgot an index! However, it has very high minimum latency that cannot be fixed through any mechanism. Not via RDMA, shared memory, named pipes, local loopback, or any other means. It always takes SQL Server about 125-150 microseconds to respond to a query, not matter how trivial, such as "SELECT 1". This makes it terrible as a KV store, and makes some types of ORM very sluggish when chasing references in code. Similarly, there have been several articles published saying that while Postgres is well rounded and feature-full, it has high write-amplification that makes it an absolute no-go past a certain scale. Many orgs have written long blogs about how they started with PG and were forced to migrate to MySQL to get to the required write throughput. I feel that the DBMS space is still immature, and there are very fundamental things missing. E.g.: There are no enterprise-ready open source pure in-memory relational database engines that have capabilities comparable to SAP HANA. I'm also yet to see a DBMS with decent programmability, so that developers don't need to write App->ORM->DB layers over and over.
defaultname 5y ago
No one who has done serious work across the database platforms believes they restrict benchmarks because they're behind or uncompetitive. They both make very capable, competitive database systems. The problem, as cited in the linked page, is that "It takes much more work to refute bad benchmarks than to produce them". We see on HN with regularity where people post egregiously flawed benchmarks, usually to demonstrate some preconceived notion or other. And while that can usually be ascribed to ignorance or sloppiness, it's trivial for vendors to contrive benchmarks that are purpose-suited to make their own product look good and the competition look bad, however completely artificial and unrealistic the scenario is. DeWitt clauses are an abomination. They should not exist. But I get why they exist and are threatened, even if they are basically never actually enforced (seriously though -- are they ever actually enforced?).
- still_grokking 5y ago
  I think even flawed benchmarks are good benchmarks. Because real world code isn't optimized to the max. Because real world code makes bad or even wrong assumptions. Because there are usually much more requirements to real world code than "be as fast as you can". Additionally, those flawed benchmarks make you think about a lot of interesting details. Details you usually don't think about when producing real world code. Also it's a little bit alike as with statistics. "Don't trust any statistics that you haven't fabricated yourself". Still statistics are useful in general.
  - aidenn0 5y ago
    There's flawed benchmarks and there are completely wrong benchmarks. I've seen so many benchmarks where the timing or loop overhead dominated to the point that they weren't measuring anything but the cost of taking timing values I've seen benchmarks where they run a single time without warming up the disk cache, so whichever benchmark they happen to run first shows as 2 orders of magnitude slower. I've seen benchmarks where the standard deviation was much larger than the difference between the times reported, but they didn't bother to check for this. I've seen benchmarks where they don't even use the same algorithm, and the difference in the algorithm chosen dominates any other concerns.

eitland 5y ago

> Oracle and MS SQL have the same clause and it's pretty hard to believe they aren't competitive with postgres and mysql given the resources behind the former. I'm not trying to refute you here, just add some nuance. I've used Oracle extensively for 3 years: installation, configuration, tuning, writing code against, querying for reporting, updating customer configuration etc. There are things you can do in Oracle that can't be done in Postgres yet. But I've never ever experienced a practical performance problem on Postgres that would magically disappear with Oracle. I assume they are there (I'm not into running huge DBs with thousands of subscribers). Also in my opinion it all comes down to knowing your database: In 2009 I saw a Sybase Adaptive Server Anywhere -> MSSQL at the time migration fail miserably at the first migration attempt. Everything was ported correctly but it turns out Sybase ASA had "implicit indexes" that we needed to replicate. In most cases I'd choose Postgres (or even SQLite) myself and in the rest I wouldn't immediately choose Oracle or MSSQL but rather stop and think carefully, do some tests and only then decide, I do not take it for granted that commercial databases perform better and they absolutely are more painful to manage.

eatonphil 5y ago
> I do not take it for granted that commercial databases perform better and they absolutely are more painful to manage. Oh for sure. It took me forever to figure out how to get Oracle running in a docker image as a new Oracle user. I'm not sure if I ever even figured out how to create a table. Postgres was hard to learn initially coming from the simplicity of MySQL and Oracle feels another step harder. I didn't say I expect commercial software to perform better though, just that I expect them to be competitive. I also wouldn't choose a commercial db personally but dunking on them doesn't make sense either. The engineers behind them are just as good as those behind OSS.