Querying Postgres Tables Directly from DuckDB • Hacker News Board

Re: Querying Postgres Tables Directly from DuckDB

#31

Post by mike_hock » Sat, Oct 01, 2022, 12:30 AM UTC

rgblambda wrote: post #26

somenameforme wrote: post #25

Earlier quoted context omitted.

It makes one wonder what the software world would like if the whole notion of 'premature optimization is the root of all evil' never existed. Because that advice, well intentioned and perfectly reasonable when applied thoughtfully, gradually morphed into 'optimization is the root of all evil' contributing to this ongoing race between computers speeding up and software slowing down. And that's a scenario which a cynic…

I don't think the idea morphed into "any optimization" is evil. But it is the unfortunate consequence of leaving optimization until after functional requirements are met. Same with any kind of tech debt. A mentality of "Let's just get this out the door now any fix it later" results in later meaning never.

It has morphed into "premature pessimization is the gold standard."

Re: Querying Postgres Tables Directly from DuckDB

#32

Post by xwowsersx » Sat, Oct 01, 2022, 12:51 AM UTC

Crazy idea, but...use Python client in Django app and for some of the larger/worst-performing queries, use DuckDB for those. Can even use ORM to construct the queries then get raw sql and execute with Duck. Crazy?

Re: Querying Postgres Tables Directly from DuckDB

#33

Post by m_ke » Sat, Oct 01, 2022, 1:35 AM UTC

xwowsersx wrote: post #30

m_ke wrote: post #2

This might end up being the best way to etl postgres tables to parquet. From everything else that I tried, doing a copy to CSV and then converting to parquet was the fastest but can be a pain when dealing with type conversions.

This won't let you ETL from PG to parquet, but I used this in anger the other day https://github.com/manojkarthick/pqrs Worked quite well for my purposes!

I was trying https://github.com/sfu-db/connector-x and hacking around with this https://github.com/spitz-dan-l/postgres-binary-parser but it turned out that a COPY to csv using asyncpg and then converting to parquet was the fastest.

Re: Querying Postgres Tables Directly from DuckDB

#34

Post by xwowsersx » Sat, Oct 01, 2022, 1:53 AM UTC

m_ke wrote: post #33

xwowsersx wrote: post #30

Earlier quoted context omitted.

This won't let you ETL from PG to parquet, but I used this in anger the other day https://github.com/manojkarthick/pqrs Worked quite well for my purposes!

I was trying https://github.com/sfu-db/connector-x and hacking around with this https://github.com/spitz-dan-l/postgres-binary-parser but it turned out that a COPY to csv using asyncpg and then converting to parquet was the fastest.

Ah. connector-x claims to be able to do a lot. Seems useful if it can actually deliver on that. Good to know that COPY with asyncpg is a quick approach.

In my case, I had parquet to begin with because I accidentally deleted some production data (oopsies) and when you export a snapshot from RDS to S3, it is in Parquet. Thankfully, I now have a few tricks up my sleeve to quickly restore data, but that was stressful for a bit haha

Re: Querying Postgres Tables Directly from DuckDB

#35

Post by xnx » Sat, Oct 01, 2022, 3:52 AM UTC

somenameforme wrote: post #25

xnx wrote: post #15

SQLite and DuckDB are excellent demonstrations that the computers we have on our desks (and even in our pockets) are ridiculously fast and capable for almost everything but the largest workloads. They're a stark contrast to framework bloat that has stolen our CPU cycles and given us perceptible lag when typing and scrolling.

It makes one wonder what the software world would like if the whole notion of 'premature optimization is the root of all evil' never existed. Because that advice, well intentioned and perfectly reasonable when applied thoughtfully, gradually morphed into 'optimization is the root of all evil' contributing to this ongoing race between computers speeding up and software slowing down. And that's a scenario which a cynic…

A lot of developers dont even realize how fast a web page or text editor could be. When you starting point is a massive farmework, you dont even realize that a computer should be ablr to fetch, parse, render and display in milliseconds. For example: the modern reinvention of serving html from a server as "server side rendering".

Re: Querying Postgres Tables Directly from DuckDB

#36

Post by exyi » Sat, Oct 01, 2022, 9:47 AM UTC

1egg0myegg0 wrote: post #17

exyi wrote: post #14

Earlier quoted context omitted.

Exactly, I almost started writing a tool for that, because nothing else I found supports arrays and composite types. Now let's hope DuckDB does support that :) If you don't use arrays and composites, Spark should be able to do it, right?

Yes, DuckDB supports writing lists and structs to Parquet! https://github.com/duckdb/duckdb/pull/2832 Does that help or do you have any other questions?

Awesome. However, the postgres_scanner has problems with jsonb, it won't even connect the database which contains those :/

Re: Querying Postgres Tables Directly from DuckDB

#37

Post by menaerus » Sat, Oct 01, 2022, 10:19 AM UTC

This is exciting but it left me wondering if the approach will remain to scale with larger TPC-H scale factors? Scale factor of 1 is honestly very small.

Also, I didn't quite understand if DuckDB in order to achieve this must:

1. Read the Postgres row formatted data

2. Transform the row formatted data into its internal columnar representation

3. Keep the representation in memory

Re: Querying Postgres Tables Directly from DuckDB

#38

Post by 1egg0myegg0 » Sat, Oct 01, 2022, 1:33 PM UTC

menaerus wrote: post #37

This is exciting but it left me wondering if the approach will remain to scale with larger TPC-H scale factors? Scale factor of 1 is honestly very small. Also, I didn't quite understand if DuckDB in order to achieve this must: 1. Read the Postgres row formatted data 2. Transform the row formatted data into its internal columnar representation 3. Keep the representation in memory

You can think of the attach operation as creating views in DuckDB with Postgres tables underneath! DuckDB will then query those Postgres rows (using the typical Postgres wire protocol, except in binary mode).

No data is persisted in DuckDB unless you do an insert statement with the result of the Postgres scan. DuckDB does process that data in a columnar fashion once it has been pulled into DuckDB memory though!

Does that help?

Re: Querying Postgres Tables Directly from DuckDB

#39

Post by 1egg0myegg0 » Sat, Oct 01, 2022, 1:34 PM UTC

exyi wrote: post #36

1egg0myegg0 wrote: post #17

Earlier quoted context omitted.

Yes, DuckDB supports writing lists and structs to Parquet! https://github.com/duckdb/duckdb/pull/2832 Does that help or do you have any other questions?

Awesome. However, the postgres_scanner has problems with jsonb, it won't even connect the database which contains those :/

Thank you for filing a bug, we will have a look at that I'm sure!

Re: Querying Postgres Tables Directly from DuckDB

#40

Post by exyi » Sat, Oct 01, 2022, 1:55 PM UTC

1egg0myegg0 wrote: post #39

exyi wrote: post #36

Earlier quoted context omitted.

Awesome. However, the postgres_scanner has problems with jsonb, it won't even connect the database which contains those :/

Thank you for filing a bug, we will have a look at that I'm sure!

It's more of a feature request. Thank you :)