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

Re: Things I hate about PostgreSQL (2020)

#161

Post by gkop » Tue, Apr 06, 2021, 6:27 PM UTC

natmaka wrote: post #85

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…

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.

Re: Things I hate about PostgreSQL (2020)

#162

Post by andoriyu » Tue, Apr 06, 2021, 6:39 PM UTC

latch wrote: post #23

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…

PostgreSQL is great, but I don't think your statement is particularly true. Process per connection is pretty easy to accidentally run into, even at small scale. So now you need to manage another piece of infrastructure to deal with it. Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability. Replication: see point above. Genera…

> Process per connection is pretty easy to accidentally run into, even at small scale. So now you need to manage another piece of infrastructure to deal with it.

Most places I saw this as an issue, are where developers think that by tweaking the number of connections will give them a linear boost in performance. Those are the same people that think adding more writers in RWLock will improve writing performance.

I agree that it's easy to run into and pretty silly concurrency pattern for today's time. At the same time, it's just a thing you need to be aware of when using PostgreSQL and design your service with that in mind.

Re: Things I hate about PostgreSQL (2020)

#163

Post by thornygreb » Tue, Apr 06, 2021, 6:41 PM UTC

forinti wrote: post #153

I would love it if PostgreSQL had packages. I have tons of PL/SQL that I would like to move from Oracle to PostgreSQL.

I used to miss packages but I mainly use schemas now to organize. Not the same I know but as good as it gets. I also add https://github.com/okbob/plpgsql_check so that I can find bugs earlier since plpgsql is not compiled like pl/sql.

Re: Things I hate about PostgreSQL (2020)

#164

Post by pillefitz » Tue, Apr 06, 2021, 6:41 PM UTC

theshrike79 wrote: post #20

nicoburns wrote: post #11

Earlier quoted context omitted.

I think Postgres is great for many of the same reasons that people often (wrongly) tout NoSQL systems for. It's flexible, featureful, simple and quick to get started. And unlike most NoSQL systems, it has full ACID compliance and can scale well past MVP stage to the point that most businesses will never hit its limitations. If you do hit really huge scale then you will need to start looking beyond Postgres to solutio…

PostgreSQL is better at being MongoDB than Mongo is. You can just add a JSON column and do queries on the content, index the table on individual values etc.

One of the benefits of using Mongo is its horizontal scalability, not necessarily its ability to store documents.

Re: Things I hate about PostgreSQL (2020)

#165

Post by tandr » Tue, Apr 06, 2021, 6:42 PM UTC

jeltz wrote: post #42

oblio wrote: post #32

Maybe there are some core PostgreSQL hackers here: I know this probably sounds silly but for the transaction ID thing, it does seem like a big deal, is it really insurmountable to make it a 64 bit value? It would probably push this problem up to a level where only very, very few companies would ever hit it and from a (huge) distance the change shouldn't be a huge problem.

There have been several discussion about this and I if I recall correctly the main issue is that this would bloat the tuple size even more (PostgreSQL already has a large per-tuple overhead). The most promising proposal I have seen is to have 64-bit XIDs but only store the lower 32-bits per tuple but have a per-page epoch for the upper bits.

store it (something) like protobuf does - the smaller the number, the less bytes it takes?

Re: Things I hate about PostgreSQL (2020)

#166

Post by hobs » Tue, Apr 06, 2021, 6:45 PM UTC

holtalanm wrote: post #124

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,…

It's really not that hard to have billions of rows in a modern data ingest situation, especially if you allow soft/deleting/versioning.

Honestly anything that fits on one hard drive shouldnt be called "tons of data."

Re: Things I hate about PostgreSQL (2020)

#167

Post by Foobar8568 » Tue, Apr 06, 2021, 6:48 PM UTC

jeltz wrote: post #141

Foobar8568 wrote: post #126

Earlier quoted context omitted.

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

Which means equally useless. I agree with him about transactional DDL. Having worked both with it and without it I would never want to go back to MySQL.

I have never said that it was useful :o I work with SQL Server, and I have been always amazed that DDL aren't transactional in Oracle. And it's supposed to be a "serious" database. That and the empty string being equals to NULL, but I think they 180 on that point in the recent years.

Re: Things I hate about PostgreSQL (2020)

#168

Post by dls2016 » Tue, Apr 06, 2021, 6:53 PM UTC

neolog wrote: post #160

dls2016 wrote: post #136

Earlier quoted context omitted.

I'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.

How frequently do they sample?

500Hz

Re: Things I hate about PostgreSQL (2020)

#169

Post by wosc » Tue, Apr 06, 2021, 6:54 PM UTC

icedchai wrote: post #127

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.

Recently I saw "post grayskull" on twitter, that's now my favourite. ;)

Re: Things I hate about PostgreSQL (2020)

#170

Post by rattray » Tue, Apr 06, 2021, 6:54 PM UTC

fdr wrote: post #147

I think this is a reasonable list of weaknesses, with a few quibbles. I guess since I've built parts of Heroku Postgres, and Citus Cloud, and now Crunchy Bridge...maybe I'd know. On the other hand...on the whole...maintaining Postgres is probably among the cheapest pieces of software on which I have to do so, which is why the cloud business model works. Something less stable (in all senses of the word) would chew up…

I'd be very curious to hear your quibbles!