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)?
PostgreSQL's Imperfections
121–130 of 139 posts
Re: PostgreSQL's Imperfections
#122> Every time an on-disk database page (4KB) needs to be modified by a write operation, even just a single byte, a copy of the entire page, edited with the requested changes, is written to the write-ahead log (WAL). Physical streaming replication leverages this existing WAL infrastructure as a log of changes it streams to replicas. First, the PostgreSQL page size is 8KB and has been that since the beginning. The remai…
That is correct. And neither is a full page write logged if the page is initialized from scratch. And even without WAL compression, the "hole" in the middle of the page if the page is not full, is "compressed" out.
That's not to say that FPWs are not a problem. The increase in WAL volume they can cause can be seriously problematic.
One interesting thing is that they actually can often very significantly increase streaming replication / crash recovery performance. When replaying the incremental records the page needs to be read from the os/disk if the page is not in the postgres' page cache. But with FPWs we can seed the page cache contents with the page image. For the pretty common case where the number of pages written between two checkpoints fits into the cache, that can be a very serious performance advantage.
Re: PostgreSQL's Imperfections
#123Earlier quoted context omitted.
I think it's a bad idea for a database to start implementing third-part vendor related features. That's the type of feature that should be implemented as a plugin.
You could just call it "ldap authentication". AD comes with an LDAP interface.
Or Kerberos authentication (which AD also supports) https://www.postgresql.org/docs/current/gssapi-auth.html
Re: PostgreSQL's Imperfections
#124Earlier quoted context omitted.
I think it's a bad idea for a database to start implementing third-part vendor related features. That's the type of feature that should be implemented as a plugin.
You could just call it "ldap authentication". AD comes with an LDAP interface.
Postgres does have ldap based auth, and also can authenticate against AD using sspi/gssapi.
The problem with that is that it requires users to have been created inside postgres first, and that you can't manage group membership inside AD.
Re: PostgreSQL's Imperfections
#125Criticism is valid, but he talks about cases of millions connections to a single db, that is a significant scale many companies will never see. In addition to that, probably no database can serve under significant load without careful tuning, preferably with understanding of DB internals and knowing compromises DB authors took when designin it. PostgreSQL is constantly improving. At least some of the problems with sc…
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 overhead.
If the workload has a lot of quick queries it's pretty easy to hit scalability issues around snapshots (the metadata needed to make visibility determinations). It's not that bad on a single-socket server, but on 2+ sockets with high core counts it can be significant.
We're working on it (I'm polishing the patch right now, actually :)). Here's an example graph https://twitter.com/AndresFreundTec/status/12346215343642419...
My local 2 socket workstation doesn't have enough cores to show the problem to the same degree unfortunately, so the above is from an azure VM. The odd dip in the middle is an issue with slow IPIs on azure VMs, and is worse when the benchmark client and server run on the same machine.
> It's terrifying to watch a couple hundred connections take a moderately sized server (~100 threads) down the native_queued_spin_lock_slowpath path to ruin. That's just sad.
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.
Re: PostgreSQL's Imperfections
#126Earlier 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…
One more thing: There's definitely a significant overhead implied by the process-per-connection model - I don't want to deny that.
In my opinion it's at the moment not the most urgent issue wrt connection scalability (the snapshot scalability is independent from process v threads, and measurably the bottleneck), and the amount of work needed to change to a different model is larger.
But I do think we're gonna have to change to threads, in the not too far away future. We can work around all the individual problems, but the cost in complexity is bigger than the advantages of increased isolation. We had to add too much complexity / duplicated infrastructure to e.g. make parallelism work (which needs to map additional shared memory after fork, and thus addresses differ between processes).
Re: PostgreSQL's Imperfections
#127Earlier 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…
Bad hints can always be dropped. Every system is prone to abuse, I don't think hints are too out there in this aspect
> leading to months of developers rebuilding every query, despite having no schema changes.
But doing so is not free. You may be on a completely different scale than they were.
Re: PostgreSQL's Imperfections
#128Earlier quoted context omitted.
No dump/reload, but you do need to shut down the server cleanly, enable checksums[1], and start it back up. Note that enabling checksums is expensive, but not as expensive as dump/reload. [1] https://www.postgresql.org/docs/current/app-pgchecksums.html
That tool is only available in the latest versions of Postgresql (11 and 12), which very few stable shops are running in production. Checksums were introduced in 9.3, yet not made the default until 9.6. Converting it requires lengthy downtime, back to the original author's point of major version upgrade pain. Everything worthwhile in PG is introduced over a long time, requires a lot of pain to adopt, and if not adopt…
There is a fork/spoon here, which supports 9.4+: https://github.com/credativ/pg_checksums
It is also available as a Debian package for the above versions via apt.postgresql.org.
Re: PostgreSQL's Imperfections
#129Earlier quoted context omitted.
You could just call it "ldap authentication". AD comes with an LDAP interface.
Postgres does have ldap based auth, and also can authenticate against AD using sspi/gssapi. The problem with that is that it requires users to have been created inside postgres first, and that you can't manage group membership inside AD.
yup, that's what I meant by authorisation, keeping the roles and groups in pgsql up to date.
Re: PostgreSQL's Imperfections
#130Criticism is valid, but he talks about cases of millions connections to a single db, that is a significant scale many companies will never see. In addition to that, probably no database can serve under significant load without careful tuning, preferably with understanding of DB internals and knowing compromises DB authors took when designin it. PostgreSQL is constantly improving. At least some of the problems with sc…
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…
pgbouncer is also a speed optimisation, not just scaling, all agreed, pgsql lags in this currently.
but the default 150ish connections ouf of the box mean 150 workers which means 20ish 8 core VMs for your e.g. django app (1 worker/core), which is a lot of scaling already and a good business problem to have, not just an app demo. Most internal projects never make it even there.