3 ms·

> ...postgres server process listens to one network port, the default port is 5432 > Whenever receiving a connection request from a client, it starts a backend

> ...postgres server process listens to one network port, the default port is 5432 > Whenever receiving a connection request from a client, it starts a backend process. (And then, the started backend process handles all queries issued by the connected client.) > To achieve this [server] starts ("forks") a new process for each connection. From that point on, the client and the new server process communicate without intervention by the original postgres process. Thus, the master server process is always running, waiting for client connections, whereas client and associated server processes come and go. [1] So, Postgres is using process-per-connection model. Can some explain why this is? And why not something like thread-per-connection? [1] https://www.postgresql.org/docs/9.6/tutorial-arch.html https://www.postgresql.org/docs/9.6/tutorial-arch.html

outworlder 5y ago
> So, Postgres is using process-per-connection model. Can some explain why this is? And why not something like thread-per-connection? Thread vs process under Linux is kind of a potato potato thing. They are essentially the same thing - based on the flags provided when you create the process, you can indicate if you want things like shared memory or not. But they are ultimately the same construct, with similar overhead. Plus, fork() is incredibly useful. It makes sense, for such an important piece of software, to minimize shared memory.
- mike_hock 5y ago
  Also, Postgres predates CLONE_THREAD, so when Postgres was created, the distinction was even more potato-potahto than it is today.
- boomer918 5y ago
  Are you certain? I thought threads are much more lightweight and also access memory faster because there is no CoW overhead (mutexes permitting). E.g. why nginx is much faster and lighter than Apache and MySQL connections are cheaper and faster to create than Pg.
10000truths 5y ago
The other reply mentioned that threads and processes aren’t much different on Linux. But on some other POSIX systems, privilege separation and resource limits tend to operate on a per-process basis - both of which are important features for a mature RDBMS. If you want to limit the CPU/memory/IO usage of a connection or ensure that a connection never accesses a privileged resource, and you need it done in a portable manner, then each connection needs a dedicated process.
dmw_ng 5y ago
It originally used a per-process model primarily because of the age of the codebase. It predates commodity SMP hardware (1986ish onwards, starting with Sequent) and standardized threading APIs (1993): > DBMS code must run as a separate process from the application programs that access the database in order to provide data protection. The process structure can use one DBMS process per application program (i.e., a process-per-user model [STON81]) or one DBMS process for all application programs (i.e., a server model). The server model has many performance benefits (e.g., sharing of open file descriptors and buffers and optimized task switching and message send- ing overhead) in a large machine environment in which high performance is critical. However, this approach requires that a fairly complete special-purpose operating system be built. In constrast, the process-per-user model is simpler to implement but will not perform as well on most conventional operating systems. We decided after much soul searching to implement POSTGRES using a process-per-user model architecture because of our limited programming resources. POSTGRES is an ambitious undertaking and we believe the additional complexity introduced by the server architecture was not worth the additional risk of not getting the system running. Our current plan then is to implement POSTGRES as a process-per-user model on Unix 4.3 BSD. (THE DESIGN OF POSTGRES, 1986, https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf ) > In POSTGRES they are run as subprocesses managed by the POSTMASTER. A last aspect of our design concerns the operating system process structure. Currently, POSTGRES runs as one process for each active user. This was done as an expedient to get a system operational as quickly as possible. We plan on converting POSTGRES to use lightweight processes available in the operating systems we are using. These include PRESTO for the Sequent Symmetry and threads in Version 4 of Sun/OS. (The implementation of POSTGRES, 1990, https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.93.4391&rep=rep1&type=pdf https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.93... )
hans_castorp 5y ago
> So, Postgres is using process-per-connection model. Can some explain why this is? And why not something like thread-per-connection? This is explained in their Wiki "Things we do NOT want" https://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want https://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Wan... > All backends running as threads in a single process (not wanted) > This eliminates the process protection we get from the current setup. Thread creation is usually the same overhead as process creation on modern systems, so it seems unwise to use a pure threaded model, and MySQL and DB2 have demonstrated that threads introduce as many issues as they solve. Threading specific operations such as I/O, seq scans, and connection management has been discussed and will probably be implemented to enable specific performance features.
- dmw_ng 5y ago
  You can actually find some dissent from one of the core team in the linked thread: https://www.postgresql.org/message-id/CA%2BTgmobuHESqa%3Db5ssoKmoNhHXRQ5KHsfAB0RknvZh%2Bf_Rbt2g%40mail.gmail.com https://www.postgresql.org/message-id/CA%2BTgmobuHESqa%3Db5s... Modern PG is already threaded out of necessity for many kinds of query, there's really no other way to fully exploit modern hardware (or indeed, much of the hardware released in the past 30 years).

outworlder 5y ago

> So, Postgres is using process-per-connection model. Can some explain why this is? And why not something like thread-per-connection? Thread vs process under Linux is kind of a potato potato thing. They are essentially the same thing - based on the flags provided when you create the process, you can indicate if you want things like shared memory or not. But they are ultimately the same construct, with similar overhead. Plus, fork() is incredibly useful. It makes sense, for such an important piece of software, to minimize shared memory.

mike_hock 5y ago
Also, Postgres predates CLONE_THREAD, so when Postgres was created, the distinction was even more potato-potahto than it is today.
boomer918 5y ago
Are you certain? I thought threads are much more lightweight and also access memory faster because there is no CoW overhead (mutexes permitting). E.g. why nginx is much faster and lighter than Apache and MySQL connections are cheaper and faster to create than Pg.

10000truths 5y ago

The other reply mentioned that threads and processes aren’t much different on Linux. But on some other POSIX systems, privilege separation and resource limits tend to operate on a per-process basis - both of which are important features for a mature RDBMS. If you want to limit the CPU/memory/IO usage of a connection or ensure that a connection never accesses a privileged resource, and you need it done in a portable manner, then each connection needs a dedicated process.

dmw_ng 5y ago

It originally used a per-process model primarily because of the age of the codebase. It predates commodity SMP hardware (1986ish onwards, starting with Sequent) and standardized threading APIs (1993): > DBMS code must run as a separate process from the application programs that access the database in order to provide data protection. The process structure can use one DBMS process per application program (i.e., a process-per-user model [STON81]) or one DBMS process for all application programs (i.e., a server model). The server model has many performance benefits (e.g., sharing of open file descriptors and buffers and optimized task switching and message send- ing overhead) in a large machine environment in which high performance is critical. However, this approach requires that a fairly complete special-purpose operating system be built. In constrast, the process-per-user model is simpler to implement but will not perform as well on most conventional operating systems. We decided after much soul searching to implement POSTGRES using a process-per-user model architecture because of our limited programming resources. POSTGRES is an ambitious undertaking and we believe the additional complexity introduced by the server architecture was not worth the additional risk of not getting the system running. Our current plan then is to implement POSTGRES as a process-per-user model on Unix 4.3 BSD. (THE DESIGN OF POSTGRES, 1986, https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf ) > In POSTGRES they are run as subprocesses managed by the POSTMASTER. A last aspect of our design concerns the operating system process structure. Currently, POSTGRES runs as one process for each active user. This was done as an expedient to get a system operational as quickly as possible. We plan on converting POSTGRES to use lightweight processes available in the operating systems we are using. These include PRESTO for the Sequent Symmetry and threads in Version 4 of Sun/OS. (The implementation of POSTGRES, 1990, https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.93.4391&rep=rep1&type=pdf https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.93... )

hans_castorp 5y ago

> So, Postgres is using process-per-connection model. Can some explain why this is? And why not something like thread-per-connection? This is explained in their Wiki "Things we do NOT want" https://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want https://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Wan... > All backends running as threads in a single process (not wanted) > This eliminates the process protection we get from the current setup. Thread creation is usually the same overhead as process creation on modern systems, so it seems unwise to use a pure threaded model, and MySQL and DB2 have demonstrated that threads introduce as many issues as they solve. Threading specific operations such as I/O, seq scans, and connection management has been discussed and will probably be implemented to enable specific performance features.

dmw_ng 5y ago
You can actually find some dissent from one of the core team in the linked thread: https://www.postgresql.org/message-id/CA%2BTgmobuHESqa%3Db5ssoKmoNhHXRQ5KHsfAB0RknvZh%2Bf_Rbt2g%40mail.gmail.com https://www.postgresql.org/message-id/CA%2BTgmobuHESqa%3Db5s... Modern PG is already threaded out of necessity for many kinds of query, there's really no other way to fully exploit modern hardware (or indeed, much of the hardware released in the past 30 years).