Postgres Truncate Query Takes Longer Than Psycopg

[arunksoman]

What happens?

I tried to truncate a large table with both psycopg and duckdb_postgres_scanner. Psycopg is truncating table instantly. Duckdb postgres scanner takes long time than psycopg (about 20-30 minute). I believe duckdb is handling transaction and hence it is taking longer. Is there any parameter I can pass to avoid handling transaction by postgres extension while executing query? I didn't found anything on the documentation.

To Reproduce

import duckdb
import config

conn = duckdb.connect(":memory:")
conn.execute(
            f"ATTACH 'dbname={config.DB_NAME} user={config.DB_USER} password={config.DB_PASSWORD} hostaddr={config.DB_HOST}' AS pg_db (TYPE postgres);"
        )
conn.execute("TRUNCATE table pg_db.large_table")

OS:

Windows

PostgreSQL Version:

14.5

DuckDB Version:

0.10.1

DuckDB Client:

Python

Full Name:

Arun K Soman

Affiliation:

Nothing

Have you tried this on the latest main branch?

• [X] I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] I agree

[Hajna]

same here, when checking pg_stat_activity, you can see delete statements and not truncate