3 ms·
Personally I'm a fan of pruning production data and creating separate structures for archival purpose. You should already have recurring backups so pulling the
by gravypod 9y ago
Personally I'm a fan of pruning production data and creating separate structures for archival purpose. You should already have recurring backups so pulling the data out of your tables should not be scary.
Copy data out of tables, put them into formats conducive to analysis (document stores, relational, column oriented stores, etc). You can then tie everything together in your language or from something like Presto.
- pgaddict 9y agoExcept that the removal of data from tables is fairly expensive process - you have to delete the data, which means running queries (which have to scan the data, write a lot of WAL and modified blocks, etc) and then do cleanup (which means vacuum on the table and also on all the indexes). That's expensive. With well designed partitioning scheme you can simply drop a partition, and you're done. Much faster/cheaper. But this is just one one benefit of partitioning - I've mentioned the possible benefits for planning elsewhere in this thread.
- mulmen 9y agoOk but partitions make it easier to prune data without expensive cleanup operations and are useful for the same reason in the relational analytic store. Nothing about partitions requires you to run analytics on production databases.