Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

arunsori.me

Post reply on HN
Print view View original

11–20 of 28 posts

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#11

Post by _ph_ »

I have done something similar. For a software, which take shared libraries - usually written in C - as plugins, I wrote such a plugin in Go. This was a very good experience. It took only little work to set up the C compatible Go functions realizing the API, but the rest I could implement in Go which made the life so nice. I also ended up calling back into some of the applications APIs from Go, that worked seamlessly.

Go has good facilities interfacing with C, the only attention you need to pay is properly handling C pointers (manual memory management) vs. Go pointers (automatically managed via the GC). But with very little care this is not a big issue. The Go part of the code is however much nicer than if you had to implement the functionality in C. (Yes, I do think that Go is a great C replacement)

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#12

Post by gscho »

_ph_ wrote: post #11

I have done something similar. For a software, which take shared libraries - usually written in C - as plugins, I wrote such a plugin in Go. This was a very good experience. It took only little work to set up the C compatible Go functions realizing the API, but the rest I could implement in Go which made the life so nice. I also ended up calling back into some of the applications APIs from Go, that worked seamlessly.…

I only just discovered foreign data wrappers today but it seems like you can write these in python though right? Might be easier than using Go to interface with C.

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#13

Post by sixdimensional »

gscho wrote: post #2

This is really cool. I had not heard of Foreign Data Wrappers for Postgres before! Are these used in production commonly or more of a toy thing?

Then you might be even more surprised to find out that Foreign Data Wrappers are Postgres' implementation of the ANSI SQL standard extension known as "SQL/MED" [1], where MED stands for "management of external data".

I spent a number of years working on a data federation/virtualization engine - and SQL/MED is very much related to that.

It's actually a relatively unknown topic by many software/data engineers I have worked with, but things like GraphQL federation (example, Apollo GraphQL) or some of the more popular tools such as Presto, Dremio, Denodo, etc.) are where more advanced versions of this are today.

SQL/MED and what Postgres can do is quite cool, but just know that any time you have system boundaries you cross (e.g. between heterogenous systems), things like joins, data types, and many other things becoming a bit more difficult - or you just have to think about them more. But very cool tech.

I've used SQL/MED in Postgres, FDW, linked servers in SQL Server, database links in Oracle, and more advanced virtualization/federation engines also.

If you haven't been exposed to this area before, highly recommended as another tool to know about for the toolkit.

[1] https://en.wikipedia.org/wiki/SQL/MED

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#15

Post by tankenmate »

gscho wrote: post #12
_ph_ wrote: post #11

I have done something similar. For a software, which take shared libraries - usually written in C - as plugins, I wrote such a plugin in Go. This was a very good experience. It took only little work to set up the C compatible Go functions realizing the API, but the rest I could implement in Go which made the life so nice. I also ended up calling back into some of the applications APIs from Go, that worked seamlessly.…

I only just discovered foreign data wrappers today but it seems like you can write these in python though right? Might be easier than using Go to interface with C.

Messagebird uses Go on the backend; I suspect that's why they wrote the FDW in Go.

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#16

Post by fork1 »

gscho wrote: post #2

This is really cool. I had not heard of Foreign Data Wrappers for Postgres before! Are these used in production commonly or more of a toy thing?

I've written a DB2 FDW that is used in production. Not as good as direct access of course, but very practical still.

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#17

Post by daniellarusso »

stareatgoats wrote: post #9

I sometimes fondly remember my time as an office hero, using MSAccess to attach to just about any data source imaginable, copy to a temptable and run whatever cleaning was required before loading into the server database. Good times; almost 25 years ago now. Sometimes I wonder if we're stuck.

I had actually considered doing this recently with a new hire.

I purchased them a license with Access included, but ai have not had time to play with it to talk to MySQL 5.6.

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#19

Post by AhtiK »

tristor wrote: post #14

I wonder if you've benchmarked your Go implementation against the Percona Lab's FDW for Clickhouse? https://github.com/Percona-Lab/clickhousedb_fdw

There's now also a more recent CH FDW written in C by the team at adjust, https://github.com/adjust/clickhouse_fdw

Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go

#20

Post by arunk-s »

tristor wrote: post #14

I wonder if you've benchmarked your Go implementation against the Percona Lab's FDW for Clickhouse? https://github.com/Percona-Lab/clickhousedb_fdw

Hi, Author here.

I have to admit, I haven't done any benchmarking against the existing FDWs for Clickhouse. But I actually wrote the Go FDW 2 years ago(around Dec 2018). ;)

There weren't any Clickhouse FDW available at that time and I probably would've tried them as well.

Now I just got around to write the blog post and convincing the team to release the code.

Though I have a suspicion that the percona FDW might win in the benchmarks as they won't have to pay the penalties when crossing the Go land to C land.[1]

1: https://www.cockroachlabs.com/blog/the-cost-and-complexity-o...

Post reply on HN