Is your feature request related to a problem? Please describe.
When running Temporal at high scale with PostgreSQL, our production database is under significant, constant load from the archival service's DELETE operations. This leads to performance degradation, table bloat, and high resource consumption (IOPS, CPU), making it a major operational bottleneck. It forces us to perform aggressive database maintenance and over-provision resources to cope with the inefficient, row-by-row deletion pattern on massive tables.
Describe the solution you'd like
I'd like a new, opt-in archival mode for PostgreSQL that is "partition-aware." Instead of deleting rows, the Temporal service would manage the lifecycle of entire table partitions.
The process would be:
-
Detach: The server automatically detaches partitions older than the retention period based on a user-configurable frequency (e.g., weekly).
-
Archive: The archival service then reads the entire contents of that standalone, detached table and uploads it to the configured blob store (S3, GCS, etc.).
-
Drop: After a successful archival, the server issues a DROP TABLE command on the detached partition, which instantly reclaims all storage space.
This replaces the expensive DELETE workload with highly efficient, metadata-only operations.
Describe alternatives you've considered
-
Current Archival Process: We use the standard archival feature. While it works, the DELETEs do not scale efficiently and cause the performance problems described above.
-
Manual Partitioning + Standard Archival: We considered manually partitioning the tables. This helps performance by isolating DELETEs to older partitions (via partition pruning) but still relies on the inefficient DELETE command itself.
-
Custom ETL Pipeline: We considered building a custom script to detach partitions, upload them to S3, and drop them. This completely bypasses Temporal's tooling, making the archived data invisible to the platform
Additional context
Open Question: An important design consideration is how partition creation is handled. Should this feature also manage creating future partitions, or should it require users to handle that externally (e.g., via pg_partman)?
Is your feature request related to a problem? Please describe.
When running Temporal at high scale with PostgreSQL, our production database is under significant, constant load from the archival service's DELETE operations. This leads to performance degradation, table bloat, and high resource consumption (IOPS, CPU), making it a major operational bottleneck. It forces us to perform aggressive database maintenance and over-provision resources to cope with the inefficient, row-by-row deletion pattern on massive tables.
Describe the solution you'd like
I'd like a new, opt-in archival mode for PostgreSQL that is "partition-aware." Instead of deleting rows, the Temporal service would manage the lifecycle of entire table partitions.
The process would be:
Detach: The server automatically detaches partitions older than the retention period based on a user-configurable frequency (e.g., weekly).
Archive: The archival service then reads the entire contents of that standalone, detached table and uploads it to the configured blob store (S3, GCS, etc.).
Drop: After a successful archival, the server issues a DROP TABLE command on the detached partition, which instantly reclaims all storage space.
This replaces the expensive DELETE workload with highly efficient, metadata-only operations.
Describe alternatives you've considered
Current Archival Process: We use the standard archival feature. While it works, the DELETEs do not scale efficiently and cause the performance problems described above.
Manual Partitioning + Standard Archival: We considered manually partitioning the tables. This helps performance by isolating DELETEs to older partitions (via partition pruning) but still relies on the inefficient DELETE command itself.
Custom ETL Pipeline: We considered building a custom script to detach partitions, upload them to S3, and drop them. This completely bypasses Temporal's tooling, making the archived data invisible to the platform
Additional context
Open Question: An important design consideration is how partition creation is handled. Should this feature also manage creating future partitions, or should it require users to handle that externally (e.g., via pg_partman)?