How can I disable all contraints in my postgresql?

c# entity-framework-core postgresql

Question

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

Edit

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?

1
2
5/29/2019 8:00:55 AM

Accepted Answer

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.

3
5/28/2019 6:35:44 AM

Popular Answer

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 $$;


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow