Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL's Imperfections

medium.com

Post reply on HN
Print view View original

131–139 of 139 posts

Re: PostgreSQL's Imperfections

#131

Post by karyon »

devit wrote: post #87
karyon wrote: post #57

There's also one inherent data privacy problem in MVCC that I've been running into. Suppose you have an app that lets people anonymously vote or comment on stuff, but only once. The vote in the DB must not have any connection to the person. So, you give the person a flag whether or not they voted already, and store the vote separately. Now, you'd want to set both values in the same transaction for obvious reasons. Bu…

The other problem is that PostgreSQL doesn't rewrite data in place, so an attacker can determine the order of the user votes from the physical order of data in the database, and the order of issue votes from constantly scraping your website, thus allowing to deanonimize everything. You need to use another database for this, specifically one designed to always overwrite data in place, and erase the WAL immediately aft…

Good points. We believe we have fixed the physical-order-and- scraping-issue by using random uuids as primary keys, showing stuff in the UI always ordered by PK, and periodically doing a CLUSTER, which physically rearranges the table after some index (pk in our case).

We haven't thought about higher-order storage layers. I guess we should do that... Thanks!

Re: PostgreSQL's Imperfections

#132

Post by scurvy »

anarazel wrote: post #125
scurvy wrote: post #88

Earlier quoted context omitted.

I'll trust postgresql more when it can support a few thousand connections without resorting to running middleware (pgbouncer) all over. That was his point. PostgreSQL is just abysmally bad in this area. The process per connection model works great for "my first rails project" so every developer brings it to $dayjob. Then they are caught off guard when they start getting real traffic. It's terrifying to watch a couple…

> I'll trust postgresql more when it can support a few thousand connections without resorting to running middleware (pgbouncer) all over. That was his point. PostgreSQL is just abysmally bad in this area. Depending on your workload it's entirely possible to run PG with 2000 connections. The most important thing is to configure postgres / the operating system to use huge pages, that gets rid of a good bit of the overh…

>> native_queued_spin_lock_slowpath path >Which spinlock was that on? I've seen a number of different ones over time. I've definitely hit ones in various drivers, and in both the generic parts of the unix socket and tcp stacks.

Not sure yet. It was on a server with 1000 stable connections. Things were fine for a while, then suddenly system would jump to 99% on all 104 threads and native_queued_spin_lock_slowpath was indicated by perf.

Ironically we cleared it up by having sessions disconnect when they were done. Boggled the mind that increasing connection churn improved things, but it did.

Re: PostgreSQL's Imperfections

#133

Post by ric2b »

viraptor wrote: post #49
hibikir wrote: post #17

Earlier quoted context omitted.

While I won't say that the Postgres ideosyncrasies are correct here, I can tell you that your experience is not necessarily typical. In my career, hint abuse has always been rampant. From telcos to biotech companies, a high percentage of complex queries I had to interact with had hints in them. In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristic…

> In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers I don't get this point. If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? After all, the hints were there to overcome such issues already. It sounds like t…

> If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration?

The hints can introduce wrong assumptions to the planner, which can lead to worse performance than if those wrong assumptions weren't there.

Re: PostgreSQL's Imperfections

#134

Post by int_19h »

Doctor_Fegg wrote: post #36

> The on-disk binary format is incompatible across major versions This is my major bugbear. If Postgres were able to upgrade its datastore on the fly (optionally, of course) that would make a massive difference. Instead I’ve had heart-in-mouth moments when Homebrew has decided that it wants to upgrade Postgres. (Yes, I do now use brew pin, until I transition off Homebrew for good.) #2 for me is inefficient enum stora…

This sounds like an issue with Homebrew, to some extent. On Debian, every major Postgres version gets its own package.

Re: PostgreSQL's Imperfections

#135

Post by int_19h »

thayne wrote: post #121

why does postgresql use a process per connection? Is there some advantage to doing this over using threads for each connection (or even asynchronous connection handling)?

Postgres dates back to the era when multiple processes were the normal way of doing this kind of thing on Unix-like OSes, and POSIX threads were often poorly supported on free ones.

Re: PostgreSQL's Imperfections

#136

Post by viraptor »

ric2b wrote: post #133
viraptor wrote: post #49

Earlier quoted context omitted.

> In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers I don't get this point. If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? After all, the hints were there to overcome such issues already. It sounds like t…

> If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? The hints can introduce wrong assumptions to the planner, which can lead to worse performance than if those wrong assumptions weren't there.

Same with lack of hints. We're not talking about simple queries which can be expected to just work. These were previously found to be badly optimised by the first engine, so we're in unknown territory either way.

Re: PostgreSQL's Imperfections

#137

Post by pas »

mrfusion wrote: post #80

Can I bounce this idea off you guys. Would there be a market for a dba to charge maybe 100-200. Just comes in, listens to your DB use cases, and recommends various config/setting changes, hardware, etc? It seems so much better than having a team of programmers study Postgres settings for a week. That was my last experience with it at least.

Percona does that, and they grew to a pretty decent size. If you can find enough clients and visit them every few weeks/months it can work.

Re: PostgreSQL's Imperfections

#138

Post by anarazel »

scurvy wrote: post #132
anarazel wrote: post #125

Earlier quoted context omitted.

> I'll trust postgresql more when it can support a few thousand connections without resorting to running middleware (pgbouncer) all over. That was his point. PostgreSQL is just abysmally bad in this area. Depending on your workload it's entirely possible to run PG with 2000 connections. The most important thing is to configure postgres / the operating system to use huge pages, that gets rid of a good bit of the overh…

>> native_queued_spin_lock_slowpath path >Which spinlock was that on? I've seen a number of different ones over time. I've definitely hit ones in various drivers, and in both the generic parts of the unix socket and tcp stacks. Not sure yet. It was on a server with 1000 stable connections. Things were fine for a while, then suddenly system would jump to 99% on all 104 threads and native_queued_spin_lock_slowpath was…

That sounds like you could have hit transparent hugepage / compaction related issues. They, IME, tend to hit more often with lots of long running processes, than when there's a lot of churn. It has gotten a lot better in more recent kernel versions, but if you're on an older kernel, it can be really bad.

Re: PostgreSQL's Imperfections

#139

Post by scurvy »

anarazel wrote: post #138
scurvy wrote: post #132

Earlier quoted context omitted.

>> native_queued_spin_lock_slowpath path >Which spinlock was that on? I've seen a number of different ones over time. I've definitely hit ones in various drivers, and in both the generic parts of the unix socket and tcp stacks. Not sure yet. It was on a server with 1000 stable connections. Things were fine for a while, then suddenly system would jump to 99% on all 104 threads and native_queued_spin_lock_slowpath was…

That sounds like you could have hit transparent hugepage / compaction related issues. They, IME, tend to hit more often with lots of long running processes, than when there's a lot of churn. It has gotten a lot better in more recent kernel versions, but if you're on an older kernel, it can be really bad.

THP => never. I thought about that, too.
Post reply on HN