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

Re: Things I hate about PostgreSQL (2020)

#121

Post by candiddevmike » Tue, Apr 06, 2021, 2:27 PM UTC

drewbug01 wrote: post #105

candiddevmike wrote: post #64

Only thing I really hate about PostgreSQL (probably not specific to it) is the lack of visibility into triggers. Give me jaeger style tracing for each trigger with per statement durations and I would be a very happy dev.

Your statement intrigued me, so I fired up the ol' googles and started looking to see if anyone had tried this. And within the first page of results I found a comment from you a few months ago saying the same thing! :) This seems really interesting - at least for debugging (I worry that it would tank performance under load). Have you considered trying to work on it? My googling suggest that you seem rather interested…

I keep posting it on HN hoping a Postgres dev hears my plea :)

I've never programmed in C for anything serious, so I'm not sure where I'd even start. I _think_, based on my limited knowledge of postgres extensions, you'd have to bake the jaeger sampling into PG proper--I don't think extensions can intercept/inspect triggers.

Re: Things I hate about PostgreSQL (2020)

#122

Post by rwmj » Tue, Apr 06, 2021, 2:27 PM UTC

orthoxerox wrote: post #88

barrkel wrote: post #51

My single biggest beef about PG is the lack of query planner hints. Unplanned query plan changes as data distribution shifts can and does cause queries to perform orders of magnitude worse. Queries that used to execute in milliseconds can start taking minutes without warning. Even the ability to freeze query plans would be useful, independent of query hints. In practice, I've used CTEs to force query evaluation order…

ClickHouse is the opposite: it has no optimizer, so your SQL must be structured the way you want it to run: deeply nested subqueries with one JOIN per SELECT. But at least you can be sure your query runs the way you intended.

An interesting approach and I'm not sure if I'd prefer it (I happen to like my queries being optimized automatically for my very tiny databases). But wouldn't it be possible to modify PostgreSQL to work this way too? It's unclear why you'd want to switch to a whole new DBMS for this.

Re: Things I hate about PostgreSQL (2020)

#123

Post by zaarn » Tue, Apr 06, 2021, 2:28 PM UTC

slt2021 wrote: post #120

zaarn wrote: post #65

Earlier quoted context omitted.

Don't do anything obviously complex with your RDBMS and migrations are free. If all you need is a few views, tables and FKs, then migration between RDBMS' should be low effort if you have a decent RSM or ORM to plug behind it. And even with more efforted things, I've written low-effort migrations from and to various RDBMS', it's not black magic. The little time upfront is "use pgsql unless there is a good reason not…

if you dont change schema dramatically, then it doesnt make much sense to migrate to another RDBMS, because most engines have pretty much similar query planner (if you not doing "anything obviously complex"). if you do migrate due to scaling issues, then the schema must evolve, for example: add in-memory db for caching, db sharding/partitioning, table partitioning, hot/cold data split, OLTP/OLAP split, etc.

Scaling issues can present themselves in numerous ways which may not require an in-memory DB, sharding/partitioning, hot/cold or such to be changed, they may even be already present.

In a lot of cases, these can be used and added without locking you out of migration since parts of these are deeper application level or just DB side. The query planner isn't the end-all of performance, there is plenty of differences between MySQL and PgSQL performance behaviour that might force you to switch even though the query planner won't drastically change things.

Re: Things I hate about PostgreSQL (2020)

#124

Post by holtalanm » Tue, Apr 06, 2021, 2:32 PM UTC

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 think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach

I'll echo what another commenter said. Tons of data != tons of profit.

Tons of data just means tons of data.

Source: Worked on an industrial operations workflow application that handled literally _billions_ of records in the database. Sure, the companies using the software were highly profitable, but I wouldn't have called the company I worked with 'top 1%' considering it was a startup.

Re: Things I hate about PostgreSQL (2020)

#125

Post by meritt » Tue, Apr 06, 2021, 2:35 PM UTC

dijit wrote: post #78

joana035 wrote: post #75

My only complain about PostgreSQL is COUNT() being quite slow compared with MySQL. Everything else is pretty good, MySQL has compressed tables, but in PostgreSQL the same amount of data already takes less space by default. Pghero/pg_stat_statements are also very handy. But "hate"? No, no hate here :)

just so you're aware, COUNT() on mysql can lie. Basically it's fetching metadata on the table, which can in some cases not be updated (yet), where as in pg it actually counts entries in the index.

That's only for MyISAM which sees very little use today. The InnoDB engine on MySQL does a full row count and is also relatively slow.

Re: Things I hate about PostgreSQL (2020)

#126

Post by Foobar8568 » Tue, Apr 06, 2021, 2:39 PM UTC

oauea wrote: post #106

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…

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.

MySQL is as advanced as Oracle on this topic (DDL in transaction), unless Oracle has changed in the recent years.

Re: Things I hate about PostgreSQL (2020)

#127

Post by icedchai » Tue, Apr 06, 2021, 2:41 PM UTC

otikik wrote: post #2

I will add one minor point to this list: The name. To this day I am convinced that the Hazapard UpperCASE usage is what has granted us: - A database called PostgreSQL - A library called libpostgres - An app folder called postgres - An executable called psql - A host of client libraries which chose to call themselves Pg or a variation.

This is true. Many people get confused by the name. I've met several developers who refer to it as "Postgray" or some variation.

Re: Things I hate about PostgreSQL (2020)

#128

Post by caioariede » Tue, Apr 06, 2021, 2:46 PM UTC

rattray wrote: post #86

joana035 wrote: post #75

My only complain about PostgreSQL is COUNT() being quite slow compared with MySQL. Everything else is pretty good, MySQL has compressed tables, but in PostgreSQL the same amount of data already takes less space by default. Pghero/pg_stat_statements are also very handy. But "hate"? No, no hate here :)

You may be interested in this technique, or some of the others in the article: https://www.citusdata.com/blog/2016/10/12/count-performance/... EDIT: I'm also curious what version of Postgres you've experienced this on? Sounds like there may have been improvements to COUNT (DISTINCT in v11+

If you're not making a lot of writes, that may be good approach since AFAIK the counts will only be updated after the table is ANALYZE'd

Re: Things I hate about PostgreSQL (2020)

#129

Post by mpolun » Tue, Apr 06, 2021, 2:49 PM UTC

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

Re: Things I hate about PostgreSQL (2020)

#130

Post by assface » Tue, Apr 06, 2021, 2:55 PM UTC

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/...