On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer <shammat@gmx.net> wrote:
Hello,
I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres?
Especially during bulk loads it's more efficient to TRUNCATE a partition if I know I want to replace all rows, rather than doing a DELETE.
Currently this requires dynamic SQL which isn't always feasible (and might get complicated quickly).
So I was thinking that a new command to allow truncating partitions by identifying the partitions by "value" rather by name might be helpful in that case.
Something along the lines of:
truncate partitions of base_table for values in (...);
If the IN part allowed for sub-queries then this could be used to gather the partition keys from e.g. a staging table.
For me, it seems too easily error prone such that a single typo in the IN clause may result in an entire partition being removed that wasn't supposed to be targeted. Given the user still needs to manually generate that list somehow, I don't see it as a huge effort to query the partitions and run individual commands to truncate or detach several partitions manually.
Unless it is in the SQL standard, or allows users to do something that cannot be easily done otherwise, I see it as a bell / whistle that would unnecessarily complicate the code. Writing a function that finds the partition table names and gives back a string with the text of the DDL commands that needs to be run is simple, and would encourage the user to review which tables are targeted for truncate command.