Hacker News Reader

Comment by polskibus | Hacker News Reader

Now, if only I could somehow take that parquet processing optimizer out of DuckDB and into a Postgres fdw reading parquet files... that would make my ETLs work great. Does anyone know of another open source project that uses an optimizer to perform queries on parquet files?

View on HN

Here is a DuckDB FDW for Postgres! I have not used it, but it sounds like what you need! https://github.com/alitrack/duckdb_fdw

DuckDB is designed to be used as an embedded library, so it should be totally feasible to use it as part of a FDW.

Alternatively, you can run DuckDB as part of a plpython function:

  CREATE FUNCTION pyduckdb ()
    RETURNS integer
  AS $$
    import duckdb
    con = duckdb.connect()
    return con.execute('select 42').fetchall()[0][0]
  $$ LANGUAGE plpythonu;
  SELECT pyduckdb();

There is a parquet fdw for postgres:

https://github.com/adjust/parquet_fdw

Yes, I know, but is it as smart as duckdb?

I havent tried it yet, but the parquet_fdw docs say you have to run ANALYZE on the table for optimal performance.

Why won't you just copy your parquet data into Postgres?

Three reasons I can think of:

- Postgres is a row store optimized for transactional workloads, whereas Parquet is a column-oriented format optimized for analytical workloads.

- Database storage is typically very expensive SSD storage optimized for fast IO and high availability. Parquet files, on the other hand, can be stored in inexpensive object storage such as S3.

- Loading is an additional and possibly unnecessary step

Sure but what is the scenario where you wouldn't want to use DuckDB and you'd want to use PostgreSQL instead but wouldn't want to load into PostgreSQL? DuckDB is already a SQL engine. The only additional benefit I'd imagine from PostgreSQL would be stuff you get from it being on disk?

But that's why I'm asking, I don't know.

It depends on your situation: If you already have a Postgres instance then the FDW is probably the easier solution.

Oh yeah that makes sense. I assumed the commenter was just looking for any system to run SQL over Parquet files. If you don't want to use this system that makes it easy, fair enough.

> If you don't want to use this system that makes it easy, fair enough.

Be kind. Don't be snarky. Have curious conversation; don't cross-examine. Please don't fulminate. Please don't sneer, including at the rest of the community.

https://news.ycombinator.com/newsguidelines.html

It didn't read unkind or snarky at all to me - just an amicable way of ending a thread of investigation where the result was a simple "a ha, I understand" moment.

Reply on news.ycombinator.com