cstore_fdw should support tablespaces

[jberkus]

After Enhancement #16 is implemented, we should also implement support for a TABLESPACE option for cstore tables, which would drop them in the base/oid directory on the specified tablespace.

[pykello]

It seems that "CREATE FOREIGN TABLE" doesn't have the TABLESPACE directive, so I was thinking of following options for supporting tablespaces:

• We use pg_class's reltablespace to store which tablespace to use. Since TABLESPACE directive is not supported by foreign tables, I was thinking the simplest way to do this is to use a UDF like set_cstore_tablespace(table, tablespace).
• We use foreign table options to store which tablespace to use.
• Get the tablespace name in foreign table options, but store it in pg_class using hooks.
• Get the tablespace name by current default tablespace, and store it in pg_class using hooks.

I don't have much arguments about which one is the best. Using pg_class for storing tablespace oid intuitively seems better than using foreign table options for doing this. Using a function for setting tablespace looks a bit strange and may complicate things later. So, my preference would be 4 > 1 > 3 > 2.

What do you think?

[jberkus]

First step it to discuss the viability of having a TABLESPACE directive on -hackers. If the answer there is "no", then I'd say use foriegn table options.

[jberkus]

So, based on the discussion on -hackers, I've revised my thinking on this one.

Clearly cstore_fdw now needs to control its own file locations completely. Given that, it would make sense for the file location to be a characteristic of the foreign server; that is, each foreign server would correspond to a single directory on the host. Make sense?

[pykello]

@jberkus Having the file location as a characteristic of the foreign server makes sense to me.

[samay-sharma]

To summarize the discussion in this thread and in issue #3, we would add location as a foreign server option. The path for cstore_fdw files would then be location/cstore_fdw/{database-oid}/{relfilenode}.

If the location is not specified, then we store the cstore_fdw files at the default path i.e. $PGDATA/cstore_fdw/{database-oid}/{relfilenode}.

@jberkus , @pykello - Does this make sense?

[besquared]

+1

[saosebastiao]

+1

[pykello]

Due to popular request for this feature, I am adding this to the list of features to be added in version 1.4.

[mtuncer]

When we investigate streaming replication support for cstore_fdw, providing an external file location turned out to be a blocker. We would probably end up removing that option at next major (2.0) release.

[nathansgreen]

Since #143 has been merged, there is no longer a way to control where table files are created. Systems that make use of tablespaces need to be able to control where cstore_fdw tables are stored, but that is no longer possible since Postgres does not support tablespace arguments on foreign tables. Are there any workarounds for this?

[nathansgreen]

I noticed that pg_total_relation_size will tell me the size of my table, but pg_relation_filepath will not tell me where the files are stored. Postgres appears to use the default tablespace that I assigned to my database when it was created. I tried using ALTER DATABASE SET default_tablespace but that does not appear affect where the table is stored.

[mtuncer]

@nathansgreen unfortunately we will loose the flexibility on controlling locations.

If it is not very troublesome, you could create a database in a specific tablespace and have cstore tables created in that database. Would that fix your problem ?

[nathansgreen]

It's possible, but it makes for a lot of extra complexity. I'd have to use dblink or postgres_fdw to load tables and run queries. Keeping everything organized would be a lot more difficult. Do you have any idea why the default_tablespace parameter has no effect? Since you have moved away from separately-managed files and are now using Postgres storage mechanisms, would it be feasible to stop using the foreign table feature entirely? I know work has been done recently to make pluggable storage more workable, so it seems like that could be a long-term goal.

[gunicsba]

We'd need this tablespace option too. We plan to move to AWS and have a lot of data already stored in cstore. What I'd like to try is: move everything to sc1 cold storage drives (they are cheap we have a lot of data that just sits there) And when reporting requirements demand faster storage move some of the tables involved to faster storage. We'd keep everything in cstore all the time.

How could we achieve that?