Does postgres support in memory temp table

Question

I know that postgres does not allow for in memory structures. But for temp table to work efficiently it is important to have in memory structure, otherwise it would have to flow to disk and would not be that efficient. So my question is does postgres allow in memory storage for temp table? My hunch is that it does not. Just wanted to confirm it with someone.

Thanks in advance!

In my experience people trying to "optimize" the use of temp tables in Postgres, are mis-using temp tables to begin with. Very often they aren't needed at all. — user330315
– user330315, Commented Aug 1, 2019 at 10:21
Use CTEs instead: 'These statements ... can be thought of as defining temporary tables that exist just for one query.' — user11044402
– user11044402, Commented Aug 1, 2019 at 10:23
@user11044402 CTEs are limited. Eg you can't use IF statements in them, and recursive CTEs end up with these kinds of issues. Postgres REALLY needs convenient in-mem temp tables that you can easily declare like in MSSQL select ... into #myTempTable — poshest
– poshest, Commented Mar 11, 2021 at 11:06

user330315 · Accepted Answer · 2019-08-01 10:24:22Z

3

Yes, Postgres can keep temp tables in memory. The amount of memory available for that is configured through the property temp_buffers

Quote from the manual:

Sets the maximum number of temporary buffers used by each database session. These are session-local buffers used only for access to temporary tables. The default is eight megabytes (8MB). The setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session.

A session will allocate temporary buffers as needed up to the limit given by temp_buffers. The cost of setting a large value in sessions that do not actually need many temporary buffers is only a buffer descriptor, or about 64 bytes, per increment in temp_buffers. However if a buffer is actually used an additional 8192 bytes will be consumed for it (or in general, BLCKSZ bytes).

So if you really need that, you can increase temp_buffers.

answered Aug 1, 2019 at 10:24

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Splitframe Over a year ago

The setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session.

I searched far and wide and couldn't find how to increase the buffer for the current session only.

user330315 Over a year ago

@Splitframe: set session ...

Splitframe Over a year ago

Oh wow, thank you very much. I see now why it wouldn't show up in my searches. Much obliged!

Collectives™ on Stack Overflow

Does postgres support in memory temp table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related