Things I hate about PostgreSQL (2020) • Hacker News Board

Re: Things I hate about PostgreSQL (2020)

#251

Post by gkop » Thu, Apr 08, 2021, 6:20 PM UTC

natmaka wrote: post #247

gkop wrote: post #161

Earlier quoted context omitted.

Don’t blindly set stats to 10000, an intermediate value between the default of 100 and the max of 10000 may give you the best plans; experiment to find out.

I don't understand. At ANALYZE time isn't, all other parameters being equal and adequate (costs, GEQO at max, effective_cache_size ...), the probability of obtaining a representative set of columns better with a larger amount of randomly-selected values? Then at planning time isn't the the devised plan of better quality? Adding sampled values may be bad performance-wise, for example if the planner cannot take everyth…

First, your point about planning time is important, thanks for adding that.

Regarding my point, it's possible that the planner may provide a better (on average, faster executed) plan for a given key, if that key is not found in stats, and that keys for which this is true may fit a pattern within the middle of the stats distribution. It all depends on the database schema and stats distributions.

Re: Things I hate about PostgreSQL (2020)

#252

Post by gshulegaard » Thu, Apr 08, 2021, 6:39 PM UTC

barrkel wrote: post #233

gshulegaard wrote: post #213

Earlier quoted context omitted.

Sorry for the long, rambling comment. After I wrote it I wasn't sure it added much, but since I invested so much time writing it I figured someone might find something in it useful so in that off chance I am posting it. --- Those were really interesting reads, and it's obvious to me that the author is well experienced even if I find myself at odds with some of the points and ultimate conclusion. To be explicit, there…

Don't knock yourself. Doing this work of scaling increases your expertise substantially, and the journey and the hurdles you cross along the way move you several standard deviations beyond the crowd. Specialist is a different term; it's more exclusionary than it is necessarily denoting of expertise. You can specialize in small applications without gaining expertise in scale.

I greatly appreciate your positivity!

Experience is a great expertise builder for sure, although I find the more experience I get the more technical expertise I realize I don't have. A bit ironic now that I am thinking about it in those terms.

But I hope my comment made scaling PostgreSQL feel approachable for others who consider themselves non-experts in the area. The message I hoped to build was that non-experts can be successful without trivializing the effort. Which can be a somewhat difficult line to walk.

But thank you regardless.

Re: Things I hate about PostgreSQL (2020)

#253

Post by natmaka » Fri, Apr 09, 2021, 3:52 AM UTC

gkop wrote: post #251

natmaka wrote: post #247

Earlier quoted context omitted.

I don't understand. At ANALYZE time isn't, all other parameters being equal and adequate (costs, GEQO at max, effective_cache_size ...), the probability of obtaining a representative set of columns better with a larger amount of randomly-selected values? Then at planning time isn't the the devised plan of better quality? Adding sampled values may be bad performance-wise, for example if the planner cannot take everyth…

First, your point about planning time is important, thanks for adding that. Regarding my point, it's possible that the planner may provide a better (on average, faster executed) plan for a given key, if that key is not found in stats, and that keys for which this is true may fit a pattern within the middle of the stats distribution. It all depends on the database schema and stats distributions.

I understand and neglected this case, thank you!

Re: Things I hate about PostgreSQL (2020)

#254

Post by MasiUnpleasant » Fri, Apr 09, 2021, 7:23 AM UTC

> In terms of relational databases, Galera Cluster’s group replication is also imperfect, but closer to the ideal.

As a longtime Galera user, I have to admit this "closer to the ideal" has nothing in common with reality. It fails, it loses data, quorum kills healthy nodes, transactions add significant latency. The more nodes you have, the lower performance and fault tolerance. One mysql node could literally endure triple of load, which could be deadly for Galera cluster of 3 nodes. Also, it rollbacks transactions silently.

Re: Things I hate about PostgreSQL (2020)

#255

Post by Shorel » Fri, Apr 09, 2021, 12:51 PM UTC

jbluepolarbear wrote: post #158

runeks wrote: post #140

Earlier quoted context omitted.

Devil’s advocate: could it simply be that someone spent 5 months optimizing queries for MySQL before switching to Postgres? Such that Postgres performance isn’t “worse”, it just doesn’t plan queries in the same way that MySQL does.

It was definitely that. The queries were built to take advantage of MySQL features.I joined after they just switched and was told to optimize queries. Was a pretty toxic job, I was hired work on streaming media systems (like Hulu, ESPN, etc) but instead they had me doing query optimizations. I quit after 5 months. I was never able talk to my boss once, he avoided me and was always too busy.

That's the kind of job I enjoy doing.

It is just not sexy and some startup rejected me when I said that's what I do.