1

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!

arjunagarwal
  • 361
  • 1
  • 4
  • 17
  • 3
    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. –  Aug 01 '19 at 10:21
  • Use [CTEs](https://www.postgresql.org/docs/current/queries-with.html) instead: '*These statements ... can be thought of as defining **temporary tables** that exist just for one query.*' –  Aug 01 '19 at 10:23
  • @user11044402 CTEs are limited. Eg you can't use IF statements in them, and recursive CTEs end up with [these kinds](https://www.google.com/search?q=recursive+reference+to+query+must+not+appear+within+a+subquery&oq=recursive+reference+to+query+must+not+appear+within+a+subquery) [of issues](https://stackoverflow.com/questions/57299663/aggregate-functions-are-not-allowed-in-a-recursive-query-is-there-an-alternativ). Postgres REALLY needs convenient in-mem temp tables that you can easily declare like in MSSQL `select ... into #myTempTable` – poshest Mar 11 '21 at 11:06

1 Answers1

2

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.

  • `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. – Splitframe May 09 '22 at 08:59
  • 1
    @Splitframe: [set session ...](https://www.postgresql.org/docs/current/sql-set.html) –  May 09 '22 at 09:25
  • Oh wow, thank you very much. I see now why it wouldn't show up in my searches. Much obliged! – Splitframe May 09 '22 at 09:31