Foreign tables provide a nice abstraction to separate the storage layer from the rest of the database. They are similar to MySQL storage engines in a sense. With them, users don't need to make any changes to their existing PostgreSQL databases, and instead just Create Extension and get rolling.
My question is what do you lose by doing so? FDW has always felt like a little bit of a hack, but I guess if the optimizer and execution engines can use it as if they were native tables, then there isn't much lost. But the fact that ProtoBuf is used makes me think that there is some overhead that doesn't occur in native tables.
I could be a bit off here, but from what I remember about working with FDWs I think the biggest drawback is that you can't assume native access to system tables on a remote server, which means you can't do things like CREATE INDEX on a foreign table and aren't going to have, e.g., statistics about those tables. However, it does have pretty much all the information the optimizer does about the actual query, and if you're implementing your own storage engine like Citus rather than trying to hook into someone else's you can probably get around those problems.
cstore_fdw supports ANALYZE so these statistics can indeed be used by the planner; however, the autovacuum daemon doesn't do this automatically for foreign tables, so it's up to the user to decide how often to run ANALYZE on them.
Likely because you have to run this as a separate db server. The speed improvements come from adjusting data locality on disk so they're probably not able to have columnar & regular data stores in the same PG instance/data files.
Similar to how Infobright is just MySQL but you still can't mix columnar and regular DBs in the same instance, you have to run IB separately.
As Ozgun notes above, the Foreign Data Wrapper APIs just provide a powerful abstraction for what this extension is doing.
Though the DDL to set up a foreign table requires first creating a "server" with CREATE SERVER, this is merely a formality: as in file_fdw the server doesn't actually represent an external process. All I/O occurs within the postgres backend process servicing the query.
Comments
I am curious, why are foreign tables necessary?
(Ozgun from Citus Data here.)
Foreign tables provide a nice abstraction to separate the storage layer from the rest of the database. They are similar to MySQL storage engines in a sense. With them, users don't need to make any changes to their existing PostgreSQL databases, and instead just Create Extension and get rolling.
You can also use both foreign and regular tables in the same query: http://citusdata.github.io/cstore_fdw/#toc_4
My question is what do you lose by doing so? FDW has always felt like a little bit of a hack, but I guess if the optimizer and execution engines can use it as if they were native tables, then there isn't much lost. But the fact that ProtoBuf is used makes me think that there is some overhead that doesn't occur in native tables.
I could be a bit off here, but from what I remember about working with FDWs I think the biggest drawback is that you can't assume native access to system tables on a remote server, which means you can't do things like CREATE INDEX on a foreign table and aren't going to have, e.g., statistics about those tables. However, it does have pretty much all the information the optimizer does about the actual query, and if you're implementing your own storage engine like Citus rather than trying to hook into someone else's you can probably get around those problems.
You're correct that foreign tables do not allow explicit index creation (though cstore_fdw does use skip indexes internally), but ANALYZE can ask them for statistics: http://www.postgresql.org/docs/current/static/sql-analyze.ht...
cstore_fdw supports ANALYZE so these statistics can indeed be used by the planner; however, the autovacuum daemon doesn't do this automatically for foreign tables, so it's up to the user to decide how often to run ANALYZE on them.
That's pretty cool, I had no idea that was supported :)
Makes sense, thanks for the explanation. Mixing table types is a pretty nice feature.
Likely because you have to run this as a separate db server. The speed improvements come from adjusting data locality on disk so they're probably not able to have columnar & regular data stores in the same PG instance/data files.
Similar to how Infobright is just MySQL but you still can't mix columnar and regular DBs in the same instance, you have to run IB separately.
(I'm Jason from Citus Data)
As Ozgun notes above, the Foreign Data Wrapper APIs just provide a powerful abstraction for what this extension is doing.
Though the DDL to set up a foreign table requires first creating a "server" with CREATE SERVER, this is merely a formality: as in file_fdw the server doesn't actually represent an external process. All I/O occurs within the postgres backend process servicing the query.