![]() ![]() Another workaround is to use PG_BACKGROUND to run the ‘ detach. PostgreSQL extension PG_PARTMAN does a good job in automating adding and dropping partitions however the problem is that it detaches the partitions without the “concurrently” clause. DETACH CONCURRENTLY cannot be executed from a function When you try you will get:ĮRROR: ALTER TABLE. ![]() One problem is that you can’t use the “concurrently” clause inside a transaction block, including procedures and functions. To implement this, It’s highly recommended - especially in operational database environments - to detach the partition concurrently to minimize the transaction locking issue and impact on the transactions. In some cases, it also enhances the performance of the database. After the data reaches the end of its retention period, it usually makes sense to either move it to a cheaper storage or drop it completely, which helps reduce storage and management cost. The retention period is usually defined by an organization-level policy that mandates the length of time where the data must be stored. Many PostgreSQL database administrators prefer to dynamically maintain time series partitions by adding new partitions and / or dropping partitions with data beyond the retention period. Anyone who would like to use this automation method in their production environment must first customize the script and own it from a development and support perspective. ![]() Please note that this script is not by any means an official script released by Google Cloud, it can only be used for education and guidance purposes. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |