Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
1–10 of 28 posts
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#2This 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?
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#3This 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?
There are generally two classes for FDWs: PostgresPostgres and Postgres->Everything else.
The first one is generally suitable for production and is very useful for sharded Postgres when you want to communicate across shards without having to go back out through the application.
The second one's mileage really varies. Some implementations might or might not be prod ready or mig target only specific version combinations. Can be very useful for data engineering or analytics use cases for quick ETL into a staging database. Or for data migrations between database vendors.
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#4This 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?
This could be of interest to you:
Ville Tuulos - How to Build a SQL-based Data Warehouse for 100+ Billion Rows in Python
PyData SV 2014 - In this talk, we show how and why AdRoll built a custom, high-performance data warehouse in Python which can handle hundreds of billions of data points with sub-minute latency on a small cluster of servers. This feat is made possible by a non-trivial combination of compressed data structures, meta-programming, and just-in-time compilation using Numba, a compiler for numerical Python. To enable smooth interoperability with existing tools, the system provides a standard SQL-interface using Multicorn and Foreign Data Wrappers in PostgreSQL.
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#5This 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 definitely think they are used in production though I haven't tried it myself.
But I can find ancedots of people using it production on the web[1].
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#6This 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?
There are generally two classes for FDWs: Postgres Postgres and Postgres->Everything else. The first one is generally suitable for production and is very useful for sharded Postgres when you want to communicate across shards without having to go back out through the application. The second one's mileage really varies. Some implementations might or might not be prod ready or mig target only specific version combinatio…
[deleted]
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#7This 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?
Here's a great list of Postgres FDWs: https://wiki.postgresql.org/wiki/Foreign_data_wrappers
I had no idea PG has native FDWs for Twitter and S3. That's pretty awesome.
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#8This 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?
We use them in production at Splitgraph [0] to power our DDN (like a CDN, but for data). We make a PostgreSQL-compatible endpoint available to the public to query any of the tens of thousands of open datasets by referencing them as virtual tables: they're not hosted by us but we proxy to them using Postgres FDWs. When a query comes in, we intercept it and redirect it to a FDW instance that handles query translation and planning from the PG dialect to that of the backend data source.
We wrote an FDW for Socrata-powered [1] government open data portals to query the public datasets that we index in the Splitgraph catalog as a proof-of-concept. However, there are plenty of other FDWs that we're working on integrating to let people add their own backend data sources (RDS, Snowflake etc).
FDW plugin quality varies (some of them can't push down all predicates or JOINs) but it's definitely an interesting way to think about accessing data. We also added a lot of scaffolding around foreign data wrappers in our open-source tool [2] that makes it easy to add a FDW-managed data source to a PostgreSQL instance.
[0] https://www.splitgraph.com/blog/data-delivery-network-launch
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#9I 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.
Re: Writing a Postgres Foreign Data Wrapper for Clickhouse in Go
#10This 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 definitely think they are used in production though I haven't tried it myself. But I can find ancedots of people using it production on the web[1]. 1: https://carto.com/blog/postgres-fdw/
Currently using this one https://github.com/pramsey/pgsql-ogr-fdw for an image migration project. Using it to pull image metadata thru ODBC. While it's primary design is for GIS data, it works well for any ODBC database.