3 ms·

Poor data archiving practices. Jokes aside when you need to operate reporting on a multi-billion row scale which only needs to look at a very specific subset o

Poor data archiving practices. Jokes aside when you need to operate reporting on a multi-billion row scale which only needs to look at a very specific subset of your data I could see the use of partitioning. It's the transparent cousin to "copy it into it's own table".

barrkel 9y ago
There's a trade-off with size vs latency. It can make sense to use smaller partitions if you need to do interactive sorts and filters on a partitioned subset of the data, in the several million row range. Benefit is smaller if the data is naturally co-located due to insertion time. The biggest win I see though is fast deletes.
mulmen 9y ago
Do you have better advice for archiving practices? I don't get the joke. Partitioning is not like copying to another table because the partition is already its own table. It saves the entire expensive operation of copying that data and you get the additional benefit that it stays current.
- 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 ago
    Except 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 ago
    Ok 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.

barrkel 9y ago

There's a trade-off with size vs latency. It can make sense to use smaller partitions if you need to do interactive sorts and filters on a partitioned subset of the data, in the several million row range. Benefit is smaller if the data is naturally co-located due to insertion time. The biggest win I see though is fast deletes.

mulmen 9y ago

Do you have better advice for archiving practices? I don't get the joke. Partitioning is not like copying to another table because the partition is already its own table. It saves the entire expensive operation of copying that data and you get the additional benefit that it stays current.

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 ago
  Except 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 ago
  Ok 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.