Обсуждение: [PATCH] Better Performance for PostgreSQL with large INSERTs

Sometimes, storing documents (eg. PDFs) in a database
is much easier than using a separate storage (like S3, NFS, etc.).

(Because of issues like backup integrity, availability,
service dependencies, access rights, encryption of data, etc..)


With this patch:

```diff
diff --git i/src/backend/libpq/pqcomm.c w/src/backend/libpq/pqcomm.c
index e517146..936b073 100644
--- i/src/backend/libpq/pqcomm.c
+++ w/src/backend/libpq/pqcomm.c
@@ -117,7 +117,8 @@ static List *sock_paths = NIL;
   */

  #define PQ_SEND_BUFFER_SIZE 8192
-#define PQ_RECV_BUFFER_SIZE 8192
+#define PQ_RECV_BUFFER_SIZE 2097152
+

  static char *PqSendBuffer;
  static int     PqSendBufferSize;       /* Size send buffer */
```

ie. changing the network receive buffer size from 8KB to 2MB,
got 7% better INSERT performance when storing BLOBs.


The 2MB value is just what we tried, 128kB or 256kB works as well.
The main point is to reduce the number of syscalls for receiving data
to about half of what it is with 8KB.


Thank you for your consideration!


Regards,

Phil

Hi!

On Mon, 15 Sept 2025 at 18:16, Philipp Marek <philipp@marek.priv.at> wrote:
>
> Sometimes, storing documents (eg. PDFs) in a database
> is much easier than using a separate storage (like S3, NFS, etc.).
>
> (Because of issues like backup integrity, availability,
> service dependencies, access rights, encryption of data, etc..)
>
>
> With this patch:
>
> ```diff
> diff --git i/src/backend/libpq/pqcomm.c w/src/backend/libpq/pqcomm.c
> index e517146..936b073 100644
> --- i/src/backend/libpq/pqcomm.c
> +++ w/src/backend/libpq/pqcomm.c
> @@ -117,7 +117,8 @@ static List *sock_paths = NIL;
>    */
>
>   #define PQ_SEND_BUFFER_SIZE 8192
> -#define PQ_RECV_BUFFER_SIZE 8192
> +#define PQ_RECV_BUFFER_SIZE 2097152
> +

Changing this constant will result in an overwhelming increase of
memory consumption for instances that work with a large number of
connections (
max_connections ~ 1e4) for zero benefit.



-- 
Best regards,
Kirill Reshke

Hi Kirill,

> Changing this constant will result in an overwhelming increase of
> memory consumption for instances that work with a large number of
> connections (
> max_connections ~ 1e4) for zero benefit.

No, it's not that bad.

1) With this being allocated in the bss segment,
    the RAM will only be actually be provided on _first use_ --
    so processes that only ever work with small queries will see no 
difference.

2) With temp_buffers' default of 8MB, work_mem using 4MB, etc.,
    using an additional 256kB RAM to 2MB _when needed_ is a good deal
    for about 7% performance improvements.

Here's the patch again, this time with a 128kB buffer size.

This gives us nearly the same gains (~7%) for the blob INSERTs,
and the additional memory usage (120kB) shouldn't really matter,
with "temp_buffer"s 8MB and "work_mem" 4MB defaults.

Making it configurable would give a much more complex patch --
so I suggest just using this fixed size.


Thanks!


diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index 25f739a6..72d092b2 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -117,7 +117,7 @@ static List *sock_paths = NIL;
   */

  #define PQ_SEND_BUFFER_SIZE 8192
-#define PQ_RECV_BUFFER_SIZE 8192
+#define PQ_RECV_BUFFER_SIZE (128 * 1024)

  static char *PqSendBuffer;
  static int     PqSendBufferSize;       /* Size send buffer */