Hello! I have written python program to benchmark view efficiency, because in our platform they have a role to play and we noticed the performance is less than expected.
Basically, benchmark creates table:
CREATE TABLE IF NOT EXISTS foobar ( id int, text varchar(40) );
for i in range(1200300):
INSERT INTO foobar (id, text) VALUES ({i}, 'some string');
CREATE VIEW foobar_{i} as select * from foobar where id={i};
Couldn't be any simpler. Postgres 13.1 running in docker, on Ubuntu 20. However, noticed that performance of certain commands is strangely slow:
- dumping through pg_dump to tar took 13 minutes. Same table but without views: less than 1 second.
- restoring through pg_restore took 147 minutes. Same table but without views: half a second.
In other situation (not observed by me) the dumping process of real world db with not only 1.2M empty views but in addition gigabytes of data in rows, lasted for many many hours, and ultimately had to be stopped.
What's even stranger is dropping performance: DROP TABLE foobar CASCADE;. First of all, had to increase locks to allow it to finish, otherwise it was quickly bailing because of "too little shared memory".
alter system set max_locks_per_transaction=40000;
But even after that, it took almost 7 hours and crashed:
2022-09-13 23:16:31.113 UTC [1] LOG: server process (PID 404) was terminated by signal 9: Killed
2022-09-13 23:16:31.113 UTC [1] DETAIL: Failed process was running: drop table foobar cascade;
2022-09-13 23:16:31.115 UTC [1] LOG: terminating any other active server processes
2022-09-13 23:16:31.115 UTC [1247] WARNING: terminating connection because of crash of another server process
2022-09-13 23:16:31.115 UTC [1247] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-09-13 23:16:31.117 UTC [97] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2022-09-13 23:16:31.136 UTC [1248] FATAL: the database system is in recovery mode
2022-09-13 23:16:31.147 UTC [1249] FATAL: the database system is in recovery mode
2022-09-13 23:16:31.192 UTC [1] LOG: all server processes terminated; reinitializing
2022-09-13 23:16:31.819 UTC [1250] LOG: database system was interrupted; last known up at 2022-09-13 23:15:47 UTC
2022-09-13 23:16:34.959 UTC [1250] LOG: database system was not properly shut down; automatic recovery in progress
2022-09-13 23:16:34.965 UTC [1250] LOG: redo starts at 2/3A3FEEC8
2022-09-13 23:16:36.421 UTC [1250] LOG: invalid record length at 2/5F355008: wanted 24, got 0
2022-09-13 23:16:36.421 UTC [1250] LOG: redo done at 2/5F354FD0
2022-09-13 23:16:37.166 UTC [1] LOG: database system is ready to accept connections
After updating Postgres to 14.5, it crashed in a bit different way:
2022-09-15 19:20:26.000 UTC [67] LOG: checkpoints are occurring too frequently (23 seconds apart)
2022-09-15 19:20:26.000 UTC [67] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-09-15 19:20:39.058 UTC [1] LOG: server process (PID 223) was terminated by signal 9: Killed
2022-09-15 19:20:39.058 UTC [1] DETAIL: Failed process was running: drop table foobar cascade;
Wihout the views, table can be dropped in 20ms.
There must be something inherently slow in the way that Postgres manages views. I know that under the hood, views are like table+relation to parent table, so it could be
compared to having about million of tables. They are not that light, aren't they?
Probably the issue is made worse because of atomicity: dropping the foobar table with cascade needs to have all views dropped first, in transaction. But why handling them would be so slow?
Assuming the above is true, I'm wondering if there's a way to improve the performance of Postgres commands like (the most important) backup and restore, in situation
of so many views. Dropping table is not that important, but would be good to have it working too, ie. by first deleting the views in batches (my idea, will test).
But backups and restores must be faster and reliable in order to implement one feature in our platform.
Perhaps adding index on views, so that it can quickly assess how many there are and to lock them, disabling something, tweaking some perf option... throwing ideas.
Please advice. Or maybe there's no hope to make this behave better :)
Regards,
Hubert
