MIME-Version: 1.0
References: <CAD=mzVUo9UpTw7F_8HKDK19ZmO6tE6Cfa4T-7i1J_QGfi6NpOw@mail.gmail.com>
In-Reply-To: <CAD=mzVUo9UpTw7F_8HKDK19ZmO6tE6Cfa4T-7i1J_QGfi6NpOw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 5 Jul 2024 09:03:15 -0400
Message-ID: <CANzqJaC419Dfe3xQHdOtQBkQPPWwPAUbNUvdfo=MWxf9eNWb3g@mail.gmail.com>
Subject: Re: Load a csv or a avro?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000078773f061c7fb002"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaC419Dfe3xQHdOtQBkQPPWwPAUbNUvdfo%3DMWxf9eNWb3g%40mail.gmail.com>
Precedence: bulk

--00000000000078773f061c7fb002
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jul 5, 2024 at 5:08=E2=80=AFAM sud <suds1434@gmail.com> wrote:

> Hello all,
>
> Its postgres database. We have option of getting files in csv and/or in
> avro format messages from another system to load it into our postgres
> database. The volume will be 300million messages per day across many file=
s
> in batches.
>
> My question was, which format should we chose in regards to faster data
> loading performance ?
>

What application will be loading the data?   If psql, then go with CSV;
COPY is *really* efficient.

If the PG tables are already mapped to the avro format, then maybe avro
will be faster.

> and if any other aspects to it also should be considered apart from just
> loading performance?
>

If all the data comes in at night, drop as many indices as possible before
loading.

Load each file in as few DB connections as possible: the most efficient
binary format won't do you any good if you open and close a connection for
each and every row.

--00000000000078773f061c7fb002
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jul 5, 2024 at 5:08=E2=80=AFAM su=
d &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; wrot=
e:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"auto"><p dir=3D"ltr">Hello all,=C2=A0</p>
<p dir=3D"ltr">Its postgres database. We have option of getting files in cs=
v and/or in avro format messages from another system to load it into our po=
stgres database. The volume will be 300million messages per day across many=
 files in batches. </p>
<p dir=3D"ltr"><span style=3D"font-size:1em">My question was, which format =
should we chose in regards to faster data loading performance ? </span></p>=
</div></blockquote><div><br class=3D"gmail-Apple-interchange-newline">What =
application will be loading the data?=C2=A0 =C2=A0If psql, then go with CSV=
; COPY is <i>really</i>=C2=A0efficient.<br></div><div>=C2=A0</div><div>If t=
he PG tables are already mapped to the avro format, then maybe avro will be=
 faster.</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"auto"><p dir=3D"ltr"><span style=3D"font-size:1em">and if any other aspect=
s to it also should be considered apart from just loading performance?</spa=
n></p></div></blockquote><div><br></div><div>If all the data comes in at ni=
ght, drop as many indices as=C2=A0possible before loading.</div><div><br></=
div><div>Load each file in as few DB connections as possible: the most effi=
cient binary format won&#39;t do you any good if you open and close a conne=
ction for each and every row.</div><div><br></div></div></div>

--00000000000078773f061c7fb002--