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

91–100 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#91

Post by collyw »

vemv wrote: post #7

Like many developers, I've used postgresql unquestioningly for many years, say 10. But if you ask me, I've rarely have had to face stringent scaling or availability requirements. Many deployments were a variation of AWS RDS or a similarly managed offering for HA. And they weren't exactly flawless. So this article is good food for thought. Why are we using postgresql? Because of the features, developer-friendliness, a…

Do you have any good examples of Graphql increasing complexity (as opposed to REST)? A dev at my work is pushing for it, and I am against it because it's another tech that isn't really solving any problems that we currently have (too much complexity is our number one problem). It would be good to have some examples to show them.

Re: Things I hate about PostgreSQL (2020)

#92

Post by refenestrator »

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.

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.

Re: Things I hate about PostgreSQL (2020)

#94

Post by marcus_holmes »

webreac wrote: post #84
marcus_holmes wrote: post #25

Earlier quoted context omitted.

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.

It's not even tech debt. It's like a "tech short" - assuming you'll have this specific scaling problem in the future, and paying the cost now.

Re: Things I hate about PostgreSQL (2020)

#95

Post by paulryanrogers »

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.

Re: Things I hate about PostgreSQL (2020)

#96

Post by brianwawok »

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…

It's pretty hard to fix a complex black-box query planner with an estimate of when another black box analyze command will fix it.

That said, if you have good monitoring, you can hopefully find out when a query gets hosed and at least have a chance to fix it.. it's not terribly often.

Re: Things I hate about PostgreSQL (2020)

#98

Post by agentultra »

Gosh I remember when Postgres didn't have any streaming replication. That was a huge pain point. You had to manually ship the WAL files to the standby and use a trigger for fail-over... and pray that your standby is actually up to date.

The code in Postgres is written in a pragmatic, no-nonsense style and overall I'm quite happy with it. I've been bitten at times by run-away toast table bloat and the odd query plan misfire. But over all it's been a really solid database to work with.

Re: Things I hate about PostgreSQL (2020)

#99

Post by ollysb »

ganomi wrote: post #73
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…

Take a look at this Postgres Extension: http://pghintplan.osdn.jp/pg_hint_plan.html I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.

This looks very interesting. I had real difficulty where I needed both a btree and gin(pg_trgm) index on the same column. When using `like` postgres would consistently choose the btree index which resulted in performance that was something like 15secs as opposed to the 200ms or so I'd see if the gin index were used. In the end I added two separate columns, one for each index so that I could force the correct one to be used for a particular query.

Re: Things I hate about PostgreSQL (2020)

#100

Post by spaetzleesser »

I_am_tiberius wrote: post #26
theshrike79 wrote: post #19

Earlier quoted context omitted.

I think I've heard a saying about this, something about premature optimisation...

Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future?

There are a lot of dimensions to scaling. It's hard to predict where you really will have to scale up.
Post reply on HN