Last week, we were assisting a client who needed to resolve an issue related to partitioned tables.
Often, a table can include dozens or even hundreds of partitions. Unfortunately, some object-level parameters for tuning (like storage_parameter) cannot be modified from the parent table and must be manipulated directly through the child table. As a result, we came up with the idea of leveraging PostgreSQLl’s metacommand \gexec to speed up things. The \gexec approach can be used to automate and speed up several tasks, and it does not restrict itself to the previously mentioned scenario.
PostgreSQL includes some fantastic functions, such as \watch, \copy, and \crosstabview, among many others. You can learn about them from the official PostgreSQL documentation at https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-GEXEC
This time, we would like to share a helpful experience using \gexec, which can significantly accelerate some repetitive tasks.
\gexec in action
Consider the following scenario (and unleash your mind to the countless possibilities it applies).
1. You have a partitioned table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | postgres=# d+ partitioned_table Partitioned table "public.partitioned_table" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ------------+---------+-----------+----------+---------+---------+-------------+--------------+------------- id | integer | | | | plain | | | f1 | integer | | | | plain | | | start_date | date | | | | plain | | | Partition key: RANGE (start_date) Partitions: partitioned_table_y2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'), partitioned_table_y2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), partitioned_table_y2022 FOR VALUES FROM ('2022-01-01') TO ('2023-01-01'), partitioned_table_y2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'), partitioned_table_y2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01') postgres=# |
2. Now, you need to perform changes over each table’s partition (or even database table, or database index, whatever), and the candidate’s list is too long. E.g., You want to perform a given task over the candidate tables, and you also want to avoid autovacuum running over the candidate tables (but you want to disable autovacuum at the database level).
All your tables have autovacuum enabled at table level (which is great)
1 2 3 4 5 6 7 8 9 10 | postgres=# SELECT relnamespace::regnamespace AS schema_name, postgres-# relname as table_name, postgres-# reloptions as options postgres-# FROM pg_class postgres-# WHERE 'autovacuum_enabled=false' = any(reloptions); schema_name | table_name | options -------------+------------+--------- (0 rows) postgres=# |
3. Using custom queries and \gexec, you can modify all tables in a single shot as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | postgres=# SELECT 'SET statement_timeout to ''5s'';' postgres-# UNION ALL postgres-# SELECT 'ALTER TABLE ' || c.oid::pg_catalog.regclass || ' SET (autovacuum_enabled = false);' postgres-# FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i postgres-# WHERE c.oid = i.inhrelid AND i.inhparent = (SELECT oid FROM pg_class WHERE relname = 'partitioned_table');\gexec ?column? ----------------------------------------------------------------------- SET statement_timeout to '5s'; ALTER TABLE partitioned_table_y2020 SET (autovacuum_enabled = false); ALTER TABLE partitioned_table_y2021 SET (autovacuum_enabled = false); ALTER TABLE partitioned_table_y2022 SET (autovacuum_enabled = false); ALTER TABLE partitioned_table_y2023 SET (autovacuum_enabled = false); ALTER TABLE partitioned_table_y2024 SET (autovacuum_enabled = false); (6 rows) SET ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE postgres=# |
This time, we disabled autovacuum at the table level for every partition that matches the conditions in our custom query. Moreover, we added a statement_timeout value to add an extra security layer, avoiding long-term locks.
4. Then we verify the success of our task:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | postgres=# SELECT relnamespace::regnamespace AS schema_name, postgres-# relname as table_name, postgres-# reloptions as options postgres-# FROM pg_class postgres-# WHERE 'autovacuum_enabled=false' = any(reloptions); schema_name | table_name | options -------------+-------------------------+---------------------------- public | partitioned_table_y2022 | {autovacuum_enabled=false} public | partitioned_table_y2021 | {autovacuum_enabled=false} public | partitioned_table_y2023 | {autovacuum_enabled=false} public | partitioned_table_y2024 | {autovacuum_enabled=false} public | partitioned_table_y2020 | {autovacuum_enabled=false} (5 rows) postgres=# |
You can revert the operation by just modifying the custom query and inserting “SET (autovacuum_enabled = true);” instead of “SET (autovacuum_enabled = false);” This way, you can grant or revoke a long list of privileges, perform several DDLs in a shot, add a long list of tables on a given publication, and do many other things. The actual scope of this is only limited to your SQL skills and imagination in taking advantage of the pg_catalog and building your custom queries that match the required conditions.
