Re: Global temporary tables

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Craig Ringer <craig(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Global temporary tables
Date: 2019-08-20 14:51:40
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 19.08.2019 18:53, Pavel Stehule wrote:
> Certainly, default (small) temp buffer size plays roles.
> But it this IPC host this difference is not so important.
> Result with local temp tables and temp_buffers = 1GB: 859k TPS.
> It is little bit unexpected result.I understand so it partially it is
> generic problem access to smaller dedicated caches versus access to
> bigger shared cache.
> But it is hard to imagine so access to local cache is 10% slower than
> access to shared cache. Maybe there is some bottle neck - maybe our
> implementation of local buffers are suboptimal.

It may be caused by system memory allocator - in case of using shared
buffers we do not need to ask OS to allocate more memory.

> Using local buffers for global temporary tables can be interesting
> from another reason - it uses temporary files, and temporary files can
> be forwarded on ephemeral IO on Amazon cloud (with much better
> performance than persistent IO).

My assumption is that temporary tables almost always fit in memory. So
in most cases there is on need to write data to file at all.

As I wrote at the beginning of this thread, one of the problems with
temporary table sis that it is not possible to use them at replica.
Global temp tables allows to share metadata between master and replica.
I perform small investigation: how difficult it will be to support
inserts in temp tables at replica.
First my impression was that it can be done in tricky but simple way.

By making small changes changing just three places:
1.  Prohibit non-select statements in read-only transactions
2. Xid assignment (return FrozenTransactionId)
3. Transaction commit/abort

I managed to provide normal work with global temp tables at replica.
But there is one problem with this approach: it is not possible to undo
changes in temp tables so rollback doesn't work.

I tried another solution, but assigning some dummy Xids to standby
But this approach require much more changes:
- Initialize page for such transaction in CLOG
- Mark transaction as committed/aborted in XCLOG
- Change snapshot check in visibility function

And still I didn't find safe way to cleanup CLOG space.
Alternative solution is to implement "local CLOG" for such transactions.
The straightforward solution is to use hashtable. But it may cause
memory overflow if we have long living backend which performs huge
number of transactions.
Also in this case we need to change visibility check functions.

So I have implemented simplest solution with frozen xid and force
backend termination in case of transaction rollback (so user will no see
inconsistent behavior).
Attached please find global_private_temp_replica.patch which implements
this approach.
It will be nice if somebody can suggest better solution for temporary
tables at replica.

Konstantin Knizhnik
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
global_private_temp_replica.patch text/x-patch 55.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alex 2019-08-20 14:52:03 Re: understand the pg locks in in an simple case
Previous Message Robert Haas 2019-08-20 14:40:02 Re: POC: Cleaning up orphaned files using undo logs