191–200 of 255 posts
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 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 worked at a company that had some IOT devices logging "still alive and working fine" every couple of minutes. There was no point to holding onto that data. You only needed to know when the status changed or it stopped reporting in, as that's all anyone cared about.
Earlier quoted context omitted.
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?
It's not a book, but Christophe Pettus' blog ( https://thebuild.com/blog ) has a lot of really good information. In particular, his talk "Breaking PostgreSQL at Scale" goes through the problems you run into as you hit different levels of scale ( https://thebuild.com/presentations/2019-fosdem-broken.pdf )
It looks like the video for that talk is here: https://www.youtube.com/watch?v=XUkTUMZRBE8
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.
https://www.postgresql.org/docs/13/runtime-config-query.html...
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…
Query plan freezing seems like an independently useful feature.
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 t…
Would you say the primary problem that you have with the planner is a misestimate of the number of rows input/output from a subplan? Or are you encountering other problems, too?
PostgreSQL's Imperfections - https://news.ycombinator.com/item?id=22775330 - April 2020 (134 comments)
Other things someone else hated:
Things I Hate About PostgreSQL (2013) - https://news.ycombinator.com/item?id=12467904 - Sept 2016 (114 comments)
Earlier quoted context omitted.
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.
Till v8.0.16 mysql used to accept and then just ignore check constraints I've never been so offended by a technology as the day I discovered that; it's not a misfeature and its not a bug -- only pure malice could have driven such a decision
Is the process per connection issue the reason why Digital Ocean etc. have so low limits on their concurrent connection settings? Even on my test database sometimes I run out of connections.