News for hackers. Stuff that matters.
Re:PostgreSQL Columnar Store for Analytic Workloads by rpedela on Thursday April 03, 2014 @01:26PM (#7524442)
I am curious, why are foreign tables necessary?
Re:PostgreSQL Columnar Store for Analytic Workloads by ozgune on Thursday April 03, 2014 @01:39PM (#7524551)
(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

Re:PostgreSQL Columnar Store for Analytic Workloads by saosebastiao on Thursday April 03, 2014 @02:01PM (#7524699)
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.
Re:PostgreSQL Columnar Store for Analytic Workloads by Jweb_Guru on Thursday April 03, 2014 @02:04PM (#7524736)
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.
Re:PostgreSQL Columnar Store for Analytic Workloads by jasonmp85 on Thursday April 03, 2014 @02:14PM (#7524828)
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.

Re:PostgreSQL Columnar Store for Analytic Workloads by Jweb_Guru on Thursday April 03, 2014 @02:37PM (#7525027)
That's pretty cool, I had no idea that was supported :)
Re:PostgreSQL Columnar Store for Analytic Workloads by rpedela on Thursday April 03, 2014 @01:43PM (#7524588)
Makes sense, thanks for the explanation. Mixing table types is a pretty nice feature.
Re:PostgreSQL Columnar Store for Analytic Workloads by mason55 on Thursday April 03, 2014 @01:34PM (#7524506)
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.

Re:PostgreSQL Columnar Store for Analytic Workloads by jasonmp85 on Thursday April 03, 2014 @01:46PM (#7524606)
(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.