Subject: Load a csv or a avro?

From: sud <[email-masked]>
Date: Fri, 5 Jul 2024 14:38:25 +0530

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 files
in batches.

My question was, which format should we chose in regards to faster data
loading performance ? and if any other aspects to it also should be
considered apart from just loading performance?

---

From: Josef Šimánek <[email-masked]>
Date: Fri, 5 Jul 2024 12:02:50 +0200

pá 5. 7. 2024 v 11:08 odesílatel sud <[email-masked]> napsal:
>
> 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 files in batches.
>
> My question was, which format should we chose in regards to faster data loading performance ? and if any other aspects to it also should be considered apart from just loading performance?

We are able to load ~300 million rows per one day using CSV and COPY
functions (https://www.postgresql.org/docs/current/libpq-copy.html#LIBPQ-COPY-SEND).

---

From: Ron Johnson <[email-masked]>
Date: Fri, 5 Jul 2024 09:03:15 -0400

On Fri, Jul 5, 2024 at 5:08 AM sud <[email-masked]> wrote:

> 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.