sql_insert output batching

[antipopp]

Hi all, I'm curious on how the sql_insert output processor works behind the curtain when batching the messages.

My current output is:

sql_insert:
          driver: postgres
          dsn: "${DSN}"
          table: production
          columns:
            [
              foo,
              bar,
            ]
          args_mapping: |
            root = [
              this.foo,
              this.bar
            ]
          batching:
            count: 100
            byte_size: 0
            period: 1m

When flushing the batch, what's the generated query? Will it be n INSERT queries, or one single insert into table values query?

I'm asking this because I'm trying to debug some issue with the size of the batches I'm having. Basically I'm stressloading my service and benthos (or the underlying pg engine) breaks with the following error:

time="2023-09-04T13:49:37Z" level=warning msg="Failed to send message: pq: bind message supplies 1000 parameters, but prepared statement \"\" requires 8" @service=production-service label="" path=root.output

My original insert statement doesn't even have 8 arguments (it actually has 10) and nothing gets processed beyond this error, that will be continuously printed.

[mihaitodor]

Hey @antipopp 👋

When flushing the batch, what's the generated query? Will it be n INSERT queries, or one single insert into table values query?

It will be the latter unless the driver supports transactions (only clickhouse and oracle for now). In the case of postgres, it should send a single INSERT query along with a list of rows of values, each row having the same length as the columns list in the config.

Here's a working demo:

> docker run --rm -it -e POSTGRES_USER=testuser -e POSTGRES_PASSWORD=testpass -e POSTGRES_DB=testdb -p 5432:5432 postgres
> pgcli postgres://testuser:testpass@localhost:5432/testdb
testuser@localhost:testdb> create table production(foo int, bar int);

Now create this yaml file and run Benthos with it:

input:
  generate:
    count: 10000
    interval: 0s
    mapping: |
      root.foo = 1
      root.bar = 2

output:
  sql_insert:
    driver: postgres
    dsn: postgres://testuser:testpass@localhost:5432/testdb?sslmode=disable
    table: production
    columns:
      - foo
      - bar
    args_mapping: |
      root = [
        this.foo,
        this.bar
      ]
    batching:
      count: 10000
      period: 10s

shutdown_timeout: 3s

You should get the following:

testuser@localhost:testdb> select count(*) from production;
+-------+
| count |
|-------|
| 10000 |
+-------+
SELECT 1
Time: 0.010s

Note: You can end up bumping into a message like "pq: got parameters but PostgreSQL only supports 65535 parameters" if your batches end up too big. More details about it here: https://klotzandrew.com/blog/postgres-passing-65535-parameter-limit and I guess it's enforced on the server side as well here.

[antipopp]

I understand and thanks for the clarification. Without batching the benthos works as expected and the load on the server is acceptable so far, I'll give it a try to optimize the batching or maybe aggregate the results differently.