Re:Wild claims about K performance
by cameronh90 on Tuesday August 31, 2021 @07:50PM
(#28375010)
I do actually use Postgres still, despite its limitations.
I dislike their ideological opposition to query hints, partially because I've been bitten enough by the optimiser deciding in production to change the query plan to something that kills performance. If anything, I'd rather be able to pin the entire query plan! Obviously a better optimiser is ideal, but query plan changes are a big risk to app stability that are hard to mitigate. AWS Aurora for PGSQL actually has a query plan management system which helps with this.
Anyway, the difference between PG and the others doesn't make up for the licensing costs. Even in the specific cases where MSSQL does far, far better, the licensing costs still eliminate most of the benefit.
Re:Wild claims about K performance
by AlisdairO on Wednesday September 01, 2021 @01:08AM
(#28376598)
The query hint thing is a serious issue. I get the concern that people won't report issues and so on, but on heavily loaded DBs having no decent operational response to a bad plan change is really scary. In an ideal world something like oracle's plan stability would be pretty helpful.
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-...
Some rudimentary benchmark comparisons here: 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/