Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: How batch processing works
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
Date: Sat, 21 Sep 2024 06:21:12 +0200
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <4178E73A-24F5-4E3C-92F6-1532D8102C3E@kleczek.org>
References: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
To: Lok P <loknath.73@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/4178E73A-24F5-4E3C-92F6-1532D8102C3E%40kleczek.org>
Precedence: bulk

Hi,

> On 19 Sep 2024, at 07:30, Lok P <loknath.73@gmail.com> wrote:
>=20
[snip]
>=20
> Method-4
>=20
> INSERT INTO parent_table VALUES  (1, 'a'), (2, 'a');
> INSERT INTO child_table VALUES   (1,1, 'a'), (1,2, 'a');
> commit;

I=E2=80=99ve done some batch processing of JSON messages from Kafka in =
Java.
By far the most performant way was to:

1. Use prepared statements
2. Parse JSON messages in Postgres
3. Process messages in batches

All three can be achieved by using arrays to pass batches:

WITH parsed AS (
  SELECT msg::json FROM unnest(?)
),
parents AS (
  INSERT INTO parent SELECT =E2=80=A6 FROM parsed RETURNING ...
)
INSERT INTO child SELECT =E2=80=A6 FROM parsed=E2=80=A6

Not the single parameter that you can bind to String[]

Hope that helps.

--
Michal=