gistfile1.txt

bloating system catalog to simulate slow systems


postgres@pg:~/udemy/15$ more db1/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_locks_per_transaction=512
allow_system_table_mods = on


postgres=# alter table pg_attribute set (autovacuum_enabled = false);
ALTER TABLE
postgres=# alter table pg_class set (autovacuum_enabled = false);
ALTER TABLE



postgres=# \dt+ pg_attri*
                                         List of relations
   Schema   |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description
------------+--------------+-------+----------+-------------+---------------+--------+-------------
 pg_catalog | pg_attribute | table | postgres | permanent   | heap          | 285 MB |
(1 row)

postgres=# do $$
declare i int;
begin
for i in 1..10000 loop
execute format('create table if not exists t%s as (select * from pg_class where false)', i);
execute format('drop table t%s', i);
end loop;
end;
$$;
DO
Time: 53441.221 ms (00:53.441)
postgres=# \dt+ pg_attri*
                                         List of relations
   Schema   |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description
------------+--------------+-------+----------+-------------+---------------+--------+-------------
 pg_catalog | pg_attribute | table | postgres | permanent   | heap          | 356 MB |
(1 row)

postgres=# do $$
declare i int;
begin
for i in 1..10000 loop
execute format('create table if not exists t%s as (select * from pg_class where false)', i);
execute format('drop table t%s', i);
end loop;
end;
$$;
DO
Time: 86905.042 ms (01:26.905)
postgres=# \dt+ pg_attri*
                                         List of relations
   Schema   |     Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description
------------+--------------+-------+----------+-------------+---------------+--------+-------------
 pg_catalog | pg_attribute | table | postgres | permanent   | heap          | 427 MB |
(1 row)


the more the size of the system catalog, it will spend more time scanning the catalog, slower systems