Date: Sun, 22 Sep 2024 21:36:12 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: How batch processing works
Message-ID: <20240922193612.6q4f6w2gzf7ruu3l@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
 <4178E73A-24F5-4E3C-92F6-1532D8102C3E@kleczek.org>
 <CAKna9Vbt1VJu7Oa8FTWasgby+-kJn7omOhbfmWzkdpVwBiqNzQ@mail.gmail.com>
 <20240921143629.t2x37xfczeeunpnf@hjp.at>
 <2fe0be15-f64e-465c-9dd2-b55c559ac7e2@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="y6tai4dfwyfdctpt"
Content-Disposition: inline
In-Reply-To: <2fe0be15-f64e-465c-9dd2-b55c559ac7e2@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/20240922193612.6q4f6w2gzf7ruu3l%40hjp.at>
Precedence: bulk


--y6tai4dfwyfdctpt
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-21 12:15:44 -0700, Adrian Klaver wrote:
> FYI, this is less of problem with psycopg(3) and pipeline mode:
>=20
[...]
> with db.pipeline():
>     for i in range(1, num_inserts+1):
>         csr.execute("insert into parent_table values(%s, %s)", (i, 'a'))
>         if i % batch_size =3D=3D 0:
>             db.commit()
> db.commit()
[...]
>=20
> For remote to a database in another state that took the  time from:
>=20
> Method 2: Individual Inserts with Commit after 50  Rows: 2.42e+02 seconds
>=20
> to:
>=20
> Method 2: Individual Inserts(psycopg3 pipeline mode) with Commit after 50
> Rows: 9.83 seconds

Very cool. I'll keep that in mind.

I've been using psycopg 3 for newer projects, but for throwaway code
I've been sticking to psycopg2, simply because it's available from the
repos of all my usual distributions. It's now in both Debian and Ubuntu,
so that will change.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--y6tai4dfwyfdctpt
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbwcaUACgkQ8g5IURL+
KF3VbRAAoHO/gBtjmD9ZVv518AIWC+HX2ktlqmtfPQ6CzuLPFDJsGRwuaGG3WcCe
2EN4ntZwYCl2Gq7u0DdOvEivMILAvJVIeMU6KCr7d7wEZrc47kgVSzAOlRCrP6x5
76iEkEOs4igbs1Y2bi5Tsx2W+HsQ8oU0hjhT4wUck+Elb7LG1ujyUENTZczfPrjp
MnUWLoXIclpbfD5Uq9WyjpJ/HXkkICLgSbz71rc1pxgheVIiOPEEbqZGGWo0sHaa
avDHuY9qe6+IPZtZerZQfKwI1tKLCjaqzOUbn1mE7+ZhMvX66YHM0pF3zpU6n1VP
zDIYyK+XIKyVyQuIXjS3fSn6Sl3lQLHqzMM+fjr7nmBWjzX+93EE4R2d/xs3IwnU
/I4khfaDfO+gfd/jCAvsj4zGbaWjqalbWm2XibvCTzHbm9vlPIo/cPQFzAlRw8b5
spVvUj9s9Fnqoi8/FXzRG0JHXOHEhjYvxgNXTnDFIrs9Y9TnGZC17Dd0/u4X3N0v
kZRBeDkQads8hE9B2lrp9s/fuIIcG5idXxrBdu6e409dzqIBRIhrzGHmvN7P+NRj
7Vaf0TTCICoP5n8Wr3PCNDeSiEfHhRwwuoWUSojs0iPaAE7zkRpyewMA9stdG1qw
9d4ZautiT2ouabaTvDsqusNM2fjT72B0igXOoBGLuh1LBbBPfHs=
=HM2x
-----END PGP SIGNATURE-----

--y6tai4dfwyfdctpt--