I have a PostgreSQL database with tables that have more than 500 billions rows. I'm trying to delete around 6 millions row but it's takes more than 4 minutes. I need to optimize those delete operation to make an useful system.
I think a good idea could be to disable constraints and triggers and then enable it back, so my questions are:
How can I disable all constraints? And, is it possible to disable relations between tables?
Right now, I just disable the triggers using this:
Alter table "table_name" disable all
And enable it back using this:
Alter table "table_name" enable all
This is the query result with triggers enabled.
Delete on "ParameterValues" (cost=0.00..1015910.71 rows=47196359 width=6) (actual time=296995.938..296995.938 rows=0 loops=1) Buffers: shared hit=6000000 read=420811 dirtied=93346 written=51646 -> Seq Scan on "ParameterValues" (cost=0.00..1015910.71 rows=47196359 width=6) (actual time=7977.126..19798.361 rows=6000000 loops=1) Filter: ("Id" < 23000000) Rows Removed by Filter: 34218414 Buffers: shared read=420811 dirtied=49228 written=51646 Planning Time: 304.085 ms Execution Time: 296995.963 ms
Disabling all triggers I improve the performance reducing the execution time from 5 minutes to 3 minutes.
Alter table "table_name" disable trigger all
However, I need to improve more delete operations. Any idea?
The correct statement to disable all triggers on a table is
ALTER TABLE atable DISABLE TRIGGER ALL;
This will disable all triggers and foreign key constraints defined on the table, since foreign key constraints are implemented by system triggers in PostgreSQL.
It will also disable deferrable primary key, unique and exclusion constraints, which are also implemented with triggers. Non-deferrable primary key, unique and exclusion constraints have no associated triggers and are not affected.
You can later re-enable these constraints by enabling the triggers, but there won't be any check for consistency. Because that is dangerous, disabling such triggers requires superuser privileges.
An alternative way to disable trigger-based constraints is to set the
session_replication_role parameter to
replica, which also requires superuser privileges.
The only way to disable check constraints and non-deferrable primary key, unique and exclusion constraints is to drop them and later re-create them. The latter will take some time as it checks the table for consistency, but you may still win out, since it will make data modifications faster.
For reference see the documentation of ALTER TABLE.
If you want a pure pl/pgsql solution. you may use an anonymous block like this( or even a function with table name and schema as arguments).
DO $$ DECLARE l_tab_name TEXT := 'yourtablename'; l_schema_name TEXT := 'public'; rec record; BEGIN for rec IN ( SELECT rel.relname as table_name ,con.conname as constraint_name FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = l_schema_name AND rel.relname = l_tab_name ) LOOP EXECUTE format ('ALTER TABLE %I DROP CONSTRAINT %I',rec.table_name,rec.constraint_name ); END LOOP; END $$;