PostgreSQL extension to move old pg_partman tables to columnar storage CSTORE
PLpgSQL Python Makefile Shell
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
bin
cron
doc
sql
updates
.gitignore
CHANGELOG.md
LICENSE.md
Makefile
README.md
partman_to_cstore.control

README.md

Partman to cstore

Partman to cstore is postgresql extension for moving old partman partitions to cstore columnar storage using cron job. When data are moved, inheritance is set on child table and parent table contains data from all cstore children and other regular children. Table check constraints are cloned too. All settings is in table move_config, there are attributes move_int - move old partman tables than $interval and drop_int - drop old cstore tables than $interval. Basic idea of this extension is save space and maybe can speedup some queries on slow disks.

-- configuration table for move partman partitions and drop cstore partitions
CREATE TABLE move_config(
    parent_table text PRIMARY KEY,
    move_int text NOT NULL DEFAULT '1d', 
    drop_int text DEFAULT '30d',
    compression text DEFAULT 'pglz', --if NULL use no compression
    stripe_row_count int, -- if NULL use default 150000
    block_row_count int, --if NULL use default 10000
    last_check timestamptz
);

Limitations

Its experimental extension, working only for partman time-based(epoch too) partitions!!! Don't forget that cstore tables isn't backuped by pg_dump(only schema dumped, no data included).

Support for move common partitions

There is function called move_singlepart_to_cstore with arguments schema and table name that can move single part of parted table to cstore. Doesn't matter if part is from pg_partman or standart postgresql partition.

Links

pg_partman extension cstore_fdw extension