On Thu, Jun 15, 2023 at 9:12 AM Konstantin Knizhnik <knizh...@garret.ru> wrote:
> There are three different but related directions of improving current > Postgres: > 1. Replacing processes with threads Here we could likely start with making parallel query multi-threaded. This would also remove the big blocker for parallelizing things like CREATE TABLE AS SELECT ... where we are currently held bac by the restriction that only the leader process can write. > 2. Builtin connection pooler Would be definitely a nice thing to have. And we could even start by integrating a non-threaded pooler like pgbouncer to run as a postgresql worker process (or two). > 3. Lightweight backends (shared catalog/relation/prepared statements caches) Shared prepared statement caches (of course have to be per-user and per-database) would give additional benefit of lightweight connection poolers not needing to track these. Currently the missing support of named prepared statements is one of the main hindrances of using pgbouncer with JDBC in transaction pooling mode (you can use it, but have to turn off automatic statement preparing) > > The motivation for such changes are also similar: > 1. Increase Postgres scalability > 2. Reduce memory consumption > 3. Make Postgres better fit cloud and serverless requirements The memory consumption reduction would be a big and clear win for many workloads. Also just moving more things in shared memory will also prepare us for move to threaded server (if it will eventually happen) > I am not sure now which one should be addressed first or them can be done > together. Shared caches seem like a guaranteed win at least on memory usage. There could be performance (and complexity) downsides for specific workloads, but they would be the same as for the threaded model, so would also be a good learning opportunity. > Replacing static variables with thread-local is the first and may be the > easiest step. I think we got our first patch doing this (as part of patches for running PG threaded on Solaris) quite early in the OSS development , could have been even in the last century :) > It requires more or less mechanical changes. More challenging thing is > replacing private per-backend data structures > with shared ones (caches, file descriptors,...) Indeed, sharing caches would be also part of the work that is needed for the sharded model, so anyone feeling strongly about moving to threads could start with this :) --- Hannu
