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

131–140 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#131

Post by sitkack »

refenestrator wrote: post #92
remus wrote: post #83

Earlier quoted context omitted.

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.

In that specific case, you probably want to roll up that time-series data as it gets older, while keeping the full dataset in a flat file system for data science etc if you need it. You probably never need a millisecond-granularity data point from 6 months ago in your database.

They probably shouldn't be rows at all. They are effectively low frequency sound files. I'd probably store them in parquet and use a FDW wrapper in Postgres.

Re: Things I hate about PostgreSQL (2020)

#133

Post by rattray »

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…

Does anyone know of a quality, comprehensive book that enumerates all the things to watch out for and problems to proactively prevent when operating Postgres at scale?

Re: Things I hate about PostgreSQL (2020)

#134

Post by sitkack »

candiddevmike wrote: post #121
drewbug01 wrote: post #105

Earlier quoted context omitted.

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.

I think the solution would be to add triggers to the dtrace probes.

https://www.postgresql.org/docs/current/dynamic-trace.html

Re: Things I hate about PostgreSQL (2020)

#135

Post by 3pt14159 »

> many of the issues brought up in this post can be reduced or eliminated by using a managed database service like Heroku PostgreSQL, ...

They come with their own issues though. I was unable to change a ludicrously small variable (which I think was temp_buffers) on Heroku's largest Postgres option. There was no solution. I just had to accept that the cloud provider wouldn't let me use this tool and code around what otherwise would have worked.

That said, at least backups and monitoring are easy.

Re: Things I hate about PostgreSQL (2020)

#136

Post by dls2016 »

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.

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.

Re: Things I hate about PostgreSQL (2020)

#137

Post by aaron-santos »

paulryanrogers wrote: post #95
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 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.

In any sufficiently large dev team the technology choices trend toward zero. Because everyone has their "I will not work with this tech after dealing with its nasty bug."

Re: Things I hate about PostgreSQL (2020)

#139

Post by zepearl »

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…

> My single biggest beef about PG is the lack of query planner hints.

Same here.

I did evaluate if to use PG for my stuff, but not having any hint available at all makes dealing with problems super-hard and potential bad situations become super-risky (esp. for PROD environments where you'll need an immediate fix if things go wrong for any reason, and especially involving 3rd party software which might not allow you to change the SQLs that it executes).

Not saying that it should be as hardcore as Oracle (hundreds of hints available, at the same time a quite stubborn optimizer), but not having anything that can be used is the other bad extreme.

I'd like as well to add that using hints doesn't have to be always the result of something that was implemented in a bad way - many times I as a human just knew better than the DB about how many rows would be accessed/why/how/when/etc... (e.g. maybe just the previous "update"-sql could have changed the data distribution in one of the tables but statistics would not immediately reflect that change) and not being able to force the execution to be done in a certain way (by using a hint) just leaved me without any options.

MariaDB's optimizer can often be a "dummy" even with simple queries, but at least it provides some way (hints) to steer it in the right direction => in this case I feel like I have more options without having to rethink&reimplement the whole DB-approach each time that some SQL doesn't perform.

Re: Things I hate about PostgreSQL (2020)

#140

Post by runeks »

jbluepolarbear wrote: post #113
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…

They’re so right about performance gotchas. I worked on a large Java project a few years back and they were transitioning from MySQL to Postgres, after the upgrade performance was abysmal. I then spent the next 5 months optimizing queries. A lot of the issues were inner joins and how MySQL and Postgres handled lookups in inner joins differently. I would still pick Postgres over MySQL because the tools and features ar…

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.
Post reply on HN