When I used pg_repack on a timescaledb hypertable, the following error occurred.

[s0214lx]

I need to update the timescaledb table frequently, which is unavoidable. And all cleanup operations need to be performed automatically. Using Autovacuum alone cannot return the space to the disk. In actual applications, the storage space occupied by the database is still increasing. I need a tool like pg_repack to clean up dead tuples and return space to the disk, but there will be problems with the use of hypertables: "ERROR: hypertables do not support enabling or disabling triggers."

[za-arthur]

pg_repack makes sure that triggers are enabled on the table by executing command returned by repack.get_enable_trigger().
You can try to re-define it as no-op function.

[s0214lx]

Does this affect other features?Will disabling it result in data loss for insert and update operations that occur during table rebuilding?

[s0214lx]

I have changed repack.get_enable_trigger to a no-op function, and I have successfully executed pg_repack. I want to know if changing it to a no-op function has any side effects and whether it will affect the consistency of the data.

[za-arthur]

@s0214lx it doesn't affect features. But you would need to make sure that your option session_replication_role is origin or local. Otherwise you might lose new update on a repacked table.

[s0214lx]

@s0214lx it doesn't affect features. But you would need to make sure that your option session_replication_role is origin or local. Otherwise you might lose new update on a repacked table.

Thank you, I have checked session_replication_role and made sure it is origin. In this case, when I execute pg_repack, it will not affect my insert and update operations on data, right? Will pg_repack lock the table and block insert operations? It seems that it will not happen from what I understand.

[s0214lx]

@s0214lx it doesn't affect features. But you would need to make sure that your option session_replication_role is origin or local. Otherwise you might lose new update on a repacked table.

I also found a problem. I created partitions for the hypertable. When I executed pg_repack on the entire table, an error occurred. However, when I executed pg_repack on a specific partition, there was no problem. Why is this happening?

[za-arthur]

@s0214lx pg_repack nees to take an exclusive lock couple of times for a short time. In your case pg_repack might have had conflict with some of your queries or with a timescale worker.

In general we didn't test running pg_repack with timescale hyper tables and we recommend you to check timescale docs or contact timescale of proper ways of reduce table bloat.

[s0214lx]

@s0214lx pg_repack nees to take an exclusive lock couple of times for a short time. In your case pg_repack might have had conflict with some of your queries or with a timescale worker.

In general we didn't test running pg_repack with timescale hyper tables and we recommend you to check timescale docs or contact timescale of proper ways of reduce table bloat.

Thank you. From my current experience, pg_repack works very well and may already meet my needs.

[andreasscherbaum]

@s0214lx Which Timescale extension version are you using?

[s0214lx]

@s0214lx Which Timescale extension version are you using?

The version of the timescaledb extension is 2.13, the version of the pg_repack extension is 1.5.2, and the version of pg is 16.8