PostgreSQL Foreign Data Wrappers

5 points·pai1009··6 comments

What are the examples of usage of PostgreSQL Foreign Data Wrappers?

We have PG on a powerful box (256GB RAM, SSDs, 5000 core GPU), and use FDWs to import data from other databases for analysis. For example, our main data warehouse is on MS SQL Server, but not super fast, so we use a FDW to grab data from there and analyze it quickly in PG. We also have an Oracle DB, and there's an FDW for that too.

> our main data warehouse is on MS SQL Server, but not super fast

MSSQL on similar capacity hardware as PostgreSQL? Is the data restructured once in PG for specific queries?

Curious as to why MSSQL is slower given it has some advantages for data warehouse workloads such as query parallelism?

He may be using GPU acceleration, with something like PGStrom.

The MS SQL Server is on a different, older, cheaper machine. The PostgreSQL one is faster because it's newer and we had budget for it.

We also have two different database teams (actually more, it's a large company): IT controls the data warehouse (MS SQL) and BI controls the analysis server (PG)

As juliangoldsmith anticipated, we use PGStrom (also a FDW) so we can use our GPU for super fast scans.

Easy, direct access to different external data formats: https://wiki.postgresql.org/wiki/Foreign_data_wrappers

Alternate storage types: https://github.com/citusdata/cstore_fdw

file_fdw.

I have a file that keeps changing constantly. What was a file read/write at app level has been replaced. Less code, direct access to data and readily "ETL-able".

Reply on news.ycombinator.com