Avoiding sending the same value multiple times

Question

js
await connection
  .query(sql`
    INSERT INTO sale
    (
      product_id,
      person_id,
      price
    )
    VALUES ${sql.valueList(values)}
  `);

and the values that are:

js
[
  [
    1,
    1,
    100
  ],
  [
    2,
    1,
    100
  ],
  [
    3,
    1,
    100
  ]
]

Currently, this would result in a generated query such as:

sql
INSERT INTO sale
(
  product_id,
  person_id,
  price
)
VALUES
(
  $1,
  $2,
  $3
),
(
  $4,
  $5,
  $6
),
(
  $7,
  $8,
  $9
)

and bound values:

js
[1, 1, 100, 2, 1, 100, 3, 1, 100]

Instead, we could detect the duplicate values and bind them to the same positional parameters.

The desired result would be:

sql
INSERT INTO sale
(
  product_id,
  person_id,
  price
)
VALUES
(
  $1,
  $1,
  $2
),
(
  $3,
  $1,
  $2
),
(
  $4,
  $1,
  $2
)

and bound values:

js
[1, 100, 2, 3]

该提问来源于开源项目：gajus/slonik

Answer 1

Some thoughts:

1. Client is a distributed cost and usually a lot easier to scale horizontally than the database; so any workload that you can offload onto the client is good.
2. The main cost for the client is identifying duplicate values; which for strings at least, is a cheap operation
3. Does pg protocol already have compression mechanism that handles this?