[Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)
От | Aleksander Alekseev |
---|---|
Тема | [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables) |
Дата | |
Msg-id | 20160729141552.4062e19b@fujitsu обсуждение исходный текст |
Ответы |
Re: [Patch] Temporary tables that do not bloat pg_catalog
(a.k.a fast temp tables)
|
Список | pgsql-hackers |
Hello Some time ago we discussed an idea of "fast temporary tables": https://www.postgresql.org/message-id/20160301182500.2c81c3dc%40fujitsu In two words the idea is following. <The Idea> PostgreSQL stores information about all relations in pg_catalog. Some applications create and delete a lot of temporary tables. It causes a bloating of pg_catalog and running auto vacuum on it. It's quite an expensive operation which affects entire database performance. We could introduce a new type of temporary tables. Information about these tables is stored not in a catalog but in backend's memory. This way user can solve a pg_catalog bloating problem and improve overall database performance. </The Idea> I took me a few months but eventually I made it work. Attached patch has some flaws. I decided not to invest a lot of time in documenting it or pgindent'ing all files yet. In my experience it will be rewritten entirely 3 or 4 times before merging anyway :) But it _works_ and passes all tests I could think of, including non-trivial cases like index-only or bitmap scans of catalog tables. Usage example: ``` CREATE FAST TEMP TABLE fasttab_test1(x int, s text); INSERT INTO fasttab_test1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); UPDATE fasttab_test1 SET s = 'ddd' WHERE x = 2; DELETE FROM fasttab_test1 WHERE x = 3; SELECT * FROM fasttab_test1 ORDER BY x; DROP TABLE fasttab_test1; ``` More sophisticated examples could be find in regression tests: ./src/test/regress/sql/fast_temp.sql Any feedback on this patch will be much appreciated! -- Best regards, Aleksander Alekseev
Вложения
В списке pgsql-hackers по дате отправления: