POSETTE 2024 is a wrap! 💯 Thanks for joining the fun! Missed it? Watch all 42 talks online 🍿
We received a number of interesting questions during this week's webinar entitled "Columnar Store for PostgreSQL Using cstore_fdw". We wanted to elaborate on a few of them.
"Is it possible/practical to move cstore-tables between servers such as on-line archive server?"
Yes, cstore_fdw is well-equipped for moving tables around, since the tables are fully contained in a data and a footer file. When you move cstore_fdw files, make sure you move the footer file before the data file, since new data is appended to the data file before the footer file is updated. To recover a cstore_fdw table from a file, specify the filename in the OPTIONS section of the CREATE TABLE statement.
"Is it possible to populate the data with sqoop?"
Yes, you can COPY data in and out of cstore_fdw tables in the same way as normal tables, which makes it fully compatible with sqoop.
"Does the append-only data need to be in a particular order for the automatic indexing to work?"
The skip indexes register the minimum and maximum value in a column for blocks of up to 1000 rows (configurable) in the order in which those rows are inserted. The data does not have to be strictly sorted for these indexes to be effective, but it helps if the ranges across larger sections of the data are distinct.
"Do these tables need to be vacuumed and/or analyzed like regular tables?"
cstore_fdw tables don't need to be vacuumed, since data cannot be overwritten, but it helps to run ANALYZE on cstore_fdw tables when using them in JOINs, since cstore_fdw provides some useful statistics to the PostgreSQL planner such as count estimates.
"What are the cstore_fdw features planned in the roadmap?"
Our immediate road map includes TRUNCATE support and 9.5 compatibility, with backwards compatibility to 9.3. cstore_fdw is an open source project, and our road map is largely based on input from users. If you would like to give feedback you can use the cstore_fdw mailing list or cstore_fdw github repository.
