From: | Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables) |
Date: | 2016-07-29 11:15:52 |
Message-ID: | 20160729141552.4062e19b@fujitsu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Attachment | Content-Type | Size |
---|---|---|
fast-temporary-tables-v1.patch | text/x-patch | 115.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Borodin | 2016-07-29 12:44:14 | Re: Optimizing numeric SUM() aggregate |
Previous Message | Amit Kapila | 2016-07-29 10:48:09 | Re: System load consideration before spawning parallel workers |