Cloud SQL - Postgres - Very slow insert

I may be used to Big Data technology, but when I try to insert ~300k rows for a total (as csv) of 30Mb I don't think 15min is an acceptable time to spend on INSERT with Postgres.

I first understand that increasing the total disk size on GCP also increase the IOPS, so I up the disk from 20Go to 400Go.

Then looking around internet I found out this article : https://naysan.ca/2020/05/09/pandas-to-postgresql-using-psycopg2-bulk-insert-performance-benchmark/

I was using old df.to_sql() to insert my data (crazy me, right !). I remembered that there is an argument like fast_executemany for a MSSQL Driver but nothing suitable for psycopg2.

So I try the faster method in the article by copying the df in memory and write to the database. With all those upgrade (disk size + code optimization) I whent from 30/35min to ~15.

Improvement ! But still around ~1000s

What can be the issue here ? I have an excellent internet connection so the bottleneck is not here.

Here a sample of what I'm doing :

import pandas as pd
import psycopg2
import time
from io import StringIO


if __name__ == '__main__':
    startTime = time.time()
    df = pd.read_gbq(
        "SELECT * FROM dataset.defaut",
        project_id="my_id_12345",
        location="europe-west1"
    )

    executionTime = (time.time() - startTime)
    print('Execution GBQ Read time in seconds: ' + str(executionTime))

    df_qgis = df[
        ["voie", "direction", "date_mesure",
         "dfo_id", "pk_ref", "type_defaut",
         "niveau_ref", "val_ref", "longueur",
         "pk_debut", "pk_fin", "pk", "pk_original"]]

    param_dic = {
        "host": "1.2.3.4",
        "database": "qgis",
        "user": "user",
        "password": "y0lo123"
    }


    def connect(params_dic):
        """ Connect to the PostgreSQL database server """
        conn = None
        try:
            # connect to the PostgreSQL server
            print('Connecting to the PostgreSQL database...')
            conn = psycopg2.connect(**params_dic)
        except (Exception, psycopg2.DatabaseError) as error:
            print(error)
            exit(1)
        print("Connection successful")
        return conn


    conn = connect(param_dic)


    def copy_from_stringio(conn, df, table):
        """
        Here we are going save the dataframe on disk as
        a csv file, load the csv file
        and use copy_from() to copy it to the table
        """
        # save dataframe to an in memory buffer
        buffer = StringIO()
        df.to_csv(buffer, index_label='id', header=False)
        buffer.seek(0)
        cursor = conn.cursor()
        try:
            cursor.copy_from(buffer, table, sep=",")
            conn.commit()
        except (Exception, psycopg2.DatabaseError) as error:
            print("Error: %s" % error)
            conn.rollback()
            cursor.close()
            return 1
        print("copy_from_file() done")
        cursor.close()


    copy_from_stringio(conn, df_qgis, "develop.defaut")

    executionTime = (time.time() - startTime)
    print('Execution final time in seconds: ' + str(executionTime))

The table itself I'm targeting :

CREATE TABLE IF NOT EXISTS public.defaut
(
    id integer GENERATED ALWAYS AS IDENTITY,
    voie varchar NOT NULL,
    direction varchar NULL,
    date_mesure varchar NOT NULL,
    dfo_id int4 NOT NULL,
    pk_ref varchar NOT NULL,
    type_defaut varchar NOT NULL,
    niveau_ref varchar NOT NULL,
    val_ref float8 NOT NULL,
    longueur float8 NULL,
    pk_debut float8 NOT NULL,
    pk_fin float8 NOT NULL,
    pk float8 NOT NULL,
    pk_original float8 NULL,
    secteur varchar NULL,
    date_sect2 varchar NULL
);