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

Re: Things I hate about PostgreSQL (2020)

#81

Post by joana035 » Tue, Apr 06, 2021, 12:55 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.

Doesn't this happens only when using sql_calc_found_rows?

Re: Things I hate about PostgreSQL (2020)

#82

Post by rattray » Tue, Apr 06, 2021, 12:56 PM UTC

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 slightly wrong can utterly tank your performance or availability. … Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.

He wrote that shortly after chasing down a gnarly bug caused by an obscure Django/Postgres crossover: https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-...

Personally, I'd still opt for Postgres every time – the featureset is incredible, and while it may have scary footguns, it's better to have footguns than bugs – at least you can do something about them.

Still, I absolutely wish the official Postgres docs did a better job outlining How Things Can Go Wrong, both in general and on the docs page for each given feature.

Re: Things I hate about PostgreSQL (2020)

#83

Post by remus » Tue, Apr 06, 2021, 12:59 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 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.

Re: Things I hate about PostgreSQL (2020)

#84

Post by webreac » Tue, Apr 06, 2021, 1:01 PM UTC

marcus_holmes wrote: post #25

I_am_tiberius wrote: post #18

Earlier quoted context omitted.

The problem is that you want to build something that can scale in the future.

ffs, this attitude causes massively more problems than it solves. 1. You can always change later. Uber switched from Postgres to MySQL when they had already achieved massive scale. 2. You don't know what scaling problems you're going to get until you've scaled. 3. Systems designed to scale properly sacrifice other abilities in order to do that. You're actively hurting your velocity with this attitude. 4. Every single…

I have not seen comments about technical debt. I think you are right: It is good to take shortcuts to ship faster. When you do that, you accumulate technical debt. I think it is important to identify it and to remain aware of this debt. I've seen too many people in denial who resist change.

Re: Things I hate about PostgreSQL (2020)

#85

Post by natmaka » Tue, Apr 06, 2021, 1:04 PM UTC

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…

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_analyze_threshold=Y;

Re: Things I hate about PostgreSQL (2020)

#86

Post by rattray » Tue, Apr 06, 2021, 1:05 PM UTC

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+

Re: Things I hate about PostgreSQL (2020)

#87

Post by natmaka » Tue, Apr 06, 2021, 1:08 PM UTC

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?

Re: Things I hate about PostgreSQL (2020)

#88

Post by orthoxerox » Tue, Apr 06, 2021, 1:10 PM UTC

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.

Re: Things I hate about PostgreSQL (2020)

#89

Post by I_am_tiberius » Tue, Apr 06, 2021, 1:13 PM UTC

taspeotis wrote: post #57

I_am_tiberius wrote: post #29

Are some of these problems solved by CitusData?

Microsoft is working hard to fix a lot of the problems e.g. connection scalability [1]. [1] https://techcommunity.microsoft.com/t5/azure-database-for-po...

That's great. Connection scalability is my biggest issue with Postgres currently. It sounds like they work on it in by commiting directly to Postgres and not only to CitusData.

Re: Things I hate about PostgreSQL (2020)

#90

Post by snuxoll » Tue, Apr 06, 2021, 1:16 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…

> expose the more stable part of the internals with a new language and let people roll their own query plans.

You’re basically talking about ISAM style access at this point. Even IBM started discouraging that on IBM i and is pushing developers to use embedded SQL instead.