Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Things I hate about PostgreSQL (2020)

rbranson.medium.com

Post reply on HN
Print view View original

241–250 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#241

Post by rzwitserloot »

rattray wrote: post #82

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ Quoting his conclusion: > As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it s…

> it’s much worse than MySQL for operational footguns and performance cliffs

As wikipedians would say, [citation needed].

The post you link to concludes with:

> Operating PostgreSQL at scale requires deep expertise

and

> I hate performance cliffs

However, both of these statements are true for _any_ major SQL-based DB engine available, including MySQL.

As the post itself shows, psql is at least doing its job in guaranteeing consistency of the data, and has tools to figure out what is going on, which is absolutely crucial when 'operating at scale'.

In other words, yeah, you need deep expertise. However, no, it's not 'much worse' than MySQL for operational footguns. MySQL has a ton of footguns just the same.

Re: Things I hate about PostgreSQL (2020)

#242

Post by rattray »

skrebbel wrote: post #240
rattray wrote: post #183

Earlier quoted context omitted.

Among other potential issues, this would make it much harder to search for information related to the database. Starting out, it'd always make sense to google for eg "postgres ilike", but for new features you'd have to search for eg "NewNameSQL kindalike" (assuming a new ILIKE replacement called KINDALIKE comes along in pg15 aka newname3). Even years in to the rename, newcomers to NewNameSQL would need to be told tha…

I meant change the name from PostgreSQL to Postgres.

Ah! So sorry for misunderstanding. Yes that sounds like a straightforward, good idea!

Re: Things I hate about PostgreSQL (2020)

#243

Post by CaptainZapp »

assface wrote: post #130
roenxi wrote: post #60

Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Then Postgres can be NoSQL too and we can all be happy. I'm not hopeful that it would be technically feasible, but it isn't obvious that Postgres needs to only support SQL as an interface. The SQL language is so horrible I assume it is already tra…

> Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Those that are ignorant of history are doomed to repeat it. Go read Stonebraker's "What Goes Around Comes Around" https://15721.courses.cs.cmu.edu/spring2020/papers/01-intro/...

Thank you!

This paper (only started on it) looks fantastically interesting. And yes, I'm old enough to actually have worked on hierarchical databases.

Re: Things I hate about PostgreSQL (2020)

#244

Post by citrin_ru »

jrockway wrote: post #204
paulryanrogers wrote: post #95

Earlier quoted context omitted.

It's interesting how personal scars can entrench ones perspective. After MySQL 8's renaming-table-will-crash-server bug I'm reluctant to use it for new projects.

I'm personally guilty of this mindset, but it's something I'm working on. After you get burned by a system, you know of the bug, and you can fix it. But the instinct is to switch to a new system, or to rewrite the system. That does get rid of all the bugs in the old system! But, in the process you've replaced them with brand new bugs that nobody has seen or heard from, until they decide to crawl into your mouth while…

sendmail is not a good example of a bad rewrite: 1st qmail is no more rewritten sendmail than Linux is rewrittnen Unix - qmail/exim/postfix just different software for the same use case. 2dn many other MTA (e. g. Postfix) managed to maintain much lower amount of security vulnerabilities than sendmail.

Re: Things I hate about PostgreSQL (2020)

#245

Post by citrin_ru »

remus wrote: post #83
mikl wrote: post #8

I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach. I’ve been using PostgreSQL for over a decade without reaching any of the mentioned scaling-related problems. PostgreSQL is still the best general purpose database in my opinion, and you can then consider using something else for parts of your application if you have special needs. I’ve used Cassan…

I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.

> 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows

PosgreSQL is a great OLTP DB, but this looks like a good fit for ClickHouse or some time series DB.

Re: Things I hate about PostgreSQL (2020)

#246

Post by natmaka »

mpolun wrote: post #129
natmaka wrote: post #87

One thing I hate about such articles is this "((use)) a managed database service" hint. Many if not most readers' data are confidential and storing them on a machine managed by unknown people seems foolish to me. Am I paranoid?

I don't see how it's any different than using any hosting provider. It's probably worth encrypting your databases, but if you don't trust your hosting provider you're hosed -- managed service or not. If your paranoia is justified (which it may be, depending on your needs), you need to host the machines in your own datacenter

> If your paranoia is justified (which it may be, depending on your needs), you need to host the machines in your own datacenter

Indeed! That's the reason why the author shouldn't write "((use)) a managed database service", à la "whatever you have in hand, screws or nails, use a hammer!"

Re: Things I hate about PostgreSQL (2020)

#247

Post by natmaka »

gkop wrote: post #161
natmaka wrote: post #85

Earlier quoted context omitted.

Isn't the optimizer fooled by some inadequately set parameter, for example "effective_cache_size"? The planner may be fooled due to a too small data sample, you may try: ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 10000; Can't you use the autovacuumer in order to kick an ANALYZE whenever there is a risk of data distribution shift? ALTER TABLE table_name autovacuum_analyze_scale_factor=X, autovacuum…

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 everything into account due to some margin/interval effect, and therefore produces the same plan using a bigger set of values. The random selection process may also, sometimes, select less-representative data in the biggest analyzed set. But how may it never lead to the best plans (which may be produced using a smaller analyzed set) or lead to (on average) worse plans?

Re: Things I hate about PostgreSQL (2020)

#248

Post by natmaka »

latch wrote: post #225
watermelon0 wrote: post #155

Earlier quoted context omitted.

From the point of view of those large customers, would you really trust people working in company X more than AWS/Azure/GCP? Especially since those customers already use other SaaS providers, that probably use at least on the big cloud providers. There definitely are companies that employ great engineers, follow best practices, and can be on par with big cloud providers, but generally you shouldn't really expect that…

Both SaaS and multi-tenant hardware have massive surface area. For multi-tenancy, it isn't about trusting AWS/Azure/GCP, it's about trusting everyone you're sharing hardware with. Cloud products are difficult to setup (AWS in particular). If you can't setup PostgreSQL properly, why are we assuming you can setup AWS properly? Look at the recent Endgame pen testing tool (1) (1) - https://news.ycombinator.com/item?id=26…

This!

See also https://news.ycombinator.com/item?id=26725185

Re: Things I hate about PostgreSQL (2020)

#249

Post by spiralx »

setr wrote: post #181
oauea wrote: post #106

Earlier quoted context omitted.

As long as mysql can't run ddl statements in a transaction it's worthless as far as I'm concerned. Also the thing where they (used to?) silently truncate your data when it wouldn't fit a column is absolutely insane. I'll take operational footguns over losing half my data every damn time.

Till v8.0.16 mysql used to accept and then just ignore check constraints I've never been so offended by a technology as the day I discovered that; it's not a misfeature and its not a bug -- only pure malice could have driven such a decision

I remember reading the MySQL Gotchas page back in the 2000s and that leaping out as a particularly egregious issue. It fit in with their whole ethos of "databases don't need transactions and users don't need errors" around that time though, which put me off for life.

Re: Things I hate about PostgreSQL (2020)

#250

Post by nrdvana »

barrkel wrote: post #235
nrdvana wrote: post #211

Earlier quoted context omitted.

(not the OP but...) I have had 3 cases in the last year where a postgres instance with less than millions of rows per table has decided to join with fancy hash algorithms that result in tens of seconds per query instead of the 5ms that it would take when it uses nested loops (i.e. literally start with the table in the from clause, apply some where clause, join to next table, apply more where clause, join to next tabl…

The feature set from an application perspective is killer. I love window functions especially; all sorts of clever things can be done in a single query which would otherwise require painful self-joins or multiple iterated queries and application-side joins in less sophisticated dialects.

My favorite killer feature is jsonb_agg / jsonb_object_agg which let me pull trees of data in a single query without the exponential waste you’s get from cartesian products, and even deliver it to the frontend without needing to assemble the json myself.
Post reply on HN