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

221–230 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#221

Post by Tostino »

datavirtue wrote: post #80

This blog post answered a lot of questions related to the internals, allowing me to make a better (real) comparison between SQL Server and PostgreS. For all of these issues he pointed out it is simply done differently in SQL Server and suffers none of the stated pitfalls. Well, you can't get the source code, and it is not free.

Yeah, and SQL Server has its own set of warts and tradeoffs. As there are with any design. Just the nature of these things. Lots of these issues are getting focus of some sort from the hackers.

Re: Things I hate about PostgreSQL (2020)

#222

Post by gshulegaard »

rattray wrote: post #216
gshulegaard wrote: post #213

Earlier quoted context omitted.

Sorry for the long, rambling comment. After I wrote it I wasn't sure it added much, but since I invested so much time writing it I figured someone might find something in it useful so in that off chance I am posting it. --- Those were really interesting reads, and it's obvious to me that the author is well experienced even if I find myself at odds with some of the points and ultimate conclusion. To be explicit, there…

Thanks :) FWIW I find this level of detail useful. What do you mean by "Index miss" – index cache miss (ie not in RAM)?

Oh that is a self-coined term...sorry! Basically (as I understand it as a layman) the query planner can "choose" to perform a sequential scan instead of use an index when executing a query. For "normal" sized workloads on "traditional" hardware (like 5400 rpm disk drives) this is a performance gain, but for large data sets it can cripple performance and even lock up a DB.

Re: Things I hate about PostgreSQL (2020)

#223

Post by nomel »

runeks wrote: post #140
jbluepolarbear wrote: post #113

Earlier quoted context omitted.

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.

The purpose of a query engine is so I don't have to think about this stuff. Computers are supposed to do work for me, no the other way around, dangit!

Re: Things I hate about PostgreSQL (2020)

#224

Post by nomel »

sitkack wrote: post #131
refenestrator wrote: post #92

Earlier quoted context omitted.

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.

I have time series of 2d 64x64 sensor data, resulting in a few billion values that I'm trying to cram into some custom parquet format. I'm often surprised that it's 2021 and we're still stuck in tabular data, with n-dimensional arrays often not even considered.

Re: Things I hate about PostgreSQL (2020)

#225

Post by latch »

watermelon0 wrote: post #155
latch wrote: post #109

Earlier quoted context omitted.

Yes, but no. I'm a staunch believer that multi-tenant hardware and managed services are _obvious_ no-gos for privacy reasons. But, having done B2B where i had to deal with security procedures/questionnaires/documentation/checklists from large customers, no one else agrees.

From the point of view of those large customers, would you really trust people working in company X more than AWS/Azure/GCP? Especially since those customers already use other SaaS providers, that probably use at least on the big cloud providers. There definitely are companies that employ great engineers, follow best practices, and can be on par with big cloud providers, but generally you shouldn't really expect that…

Both SaaS and multi-tenant hardware have massive surface area.

For multi-tenancy, it isn't about trusting AWS/Azure/GCP, it's about trusting everyone you're sharing hardware with.

Cloud products are difficult to setup (AWS in particular). If you can't setup PostgreSQL properly, why are we assuming you can setup AWS properly? Look at the recent Endgame pen testing tool (1)

(1) - https://news.ycombinator.com/item?id=26154038

Re: Things I hate about PostgreSQL (2020)

#226

Post by davidrowley »

jhgb wrote: post #220
davidrowley wrote: post #217

Earlier quoted context omitted.

Index Only Scans are a thing in PostgreSQL, however, they may still need to visit the heap if the visibility map bit for the heap page indicates that the not all tuples on the heap page are visible to all transactions. When a high percentage of pages are marked as "allvisible" then Index Only Scans can give a good boost to performance.

So this "visibility map" is a little bit like Netfrastructure/Falcon in-memory versioning, then? I see.

The visibility map is just 1 bit per page. Vacuum sets these bits to "1" when it sees that all tuples on the page are visible to all transactions. i.e. all tuple xmins are The visibility map is stored on-disk as a different fork of the filenode for the table. Two bits are actually stored per page, 1 for visibility and another to mark if the page only contains only frozen tuples. The frozen bit helps reduce the cost of vacuuming the table for transaction wraparound, which is also mentioned in the blog post.

The query planner does not count these bits to determine if it should perform an Index Only Scan vs an Index Scan. An approximate value is stored in pg_class.relallvisible.

Re: Things I hate about PostgreSQL (2020)

#227

Post by yarcob »

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…

> Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability.

I don't understand this mindset. Every tiny startup thinks they need zero downtime migrations.

At the same time, major banks and government institutions just announce maintenance windows. They just pick a time when few people use the service and then shut the whole system off for a few hours.

Sure, it's nice if your service is never down. But I'm also pretty sure that most customers prefer paying for new features rather than preparing for zero downtime migrations.

Also, considering how long PostgreSQL versions are supported, you only need to do major version upgrades every five years or so.

Re: Things I hate about PostgreSQL (2020)

#228

Post by latch »

yarcob wrote: post #227
latch wrote: post #23

Earlier quoted context omitted.

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…

> Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability. I don't understand this mindset. Every tiny startup thinks they need zero downtime migrations. At the same time, major banks and government institutions just announce maintenance windows. They just pick a time when few people use the service and then shut the whole syst…

We're a small transport company, trying to get employees to and from work. No matter the time of day, there are always people commuting (or planning to commute). When's a good time for outage?

Imagine it's 3:30am, you just got off a shift, you can't afford a cab and the nearest subway is 10KM away. How fun is it that the transport app you rely on it down for maintenance?

Maybe that helps you understand the mindset?

Re: Things I hate about PostgreSQL (2020)

#229

Post by Corrado »

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.

Agreed! My personal bias against a system depends on if it failed at it's primary purpose or not. I've had a lot of things fail in a lot of ways and I'm generally pretty forgiving, but there are some things that are just inexcusable. Some examples:

* A popular (at the time) version control system started to silently overwrite files when it ran out of disk space. We discovered this when we retrieved source code that had parts of another file in it.

* A UPS vendor had a product that tested the battery by turning off the power. When the battery ultimately failed our servers were powered off automatically. This happened randomly about every 3 weeks (usually on a weekend evening). It took us months to find the cause.

I can't come back from these problems. So, if the system is a bit slow or crashes sometimes or has other weird problems, I'm OK with that. If it bites me specifically where it's supposed to protect me, it's out, forever.

Re: Things I hate about PostgreSQL (2020)

#230

Post by hvidgaard »

jrockway wrote: post #204
paulryanrogers wrote: post #95

Earlier quoted context omitted.

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.

I'm personally guilty of this mindset, but it's something I'm working on. After you get burned by a system, you know of the bug, and you can fix it. But the instinct is to switch to a new system, or to rewrite the system. That does get rid of all the bugs in the old system! But, in the process you've replaced them with brand new bugs that nobody has seen or heard from, until they decide to crawl into your mouth while…

In reality, the smart engineering choice is to know exactly what you are expecting from a specific system, and test it. Bugs, mistakes and even cosmic rays happen, so you must specifically test that it works. If you have a backup power system, test it.
Post reply on HN