UNLOGGED TEMPORARY tables?

I'm pretty sure that unlogged tables and temp tables are two separate
& distinct features, with no overlap in functionality. It would be
nice if it was possible to create an unlogged temp table.

On Sun, Mar 24, 2013 at 1:32 PM, aasat <satriani@veranet.pl> wrote:

I was tested write speed to temporary and unlogged tables and noticed that
unlogged tables was a much faster

Postgres 9.2.2

Write speed

Temporary 14.5k/s
UNLOGGED 50k/s

Before test I was convinced that temporary tables in postgres >= 9.1 are
unlogged

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

aasat <satriani@veranet.pl> writes:

I was tested write speed to temporary and unlogged tables and noticed that
unlogged tables was a much faster

Postgres 9.2.2

Write speed

Temporary 14.5k/s
UNLOGGED 50k/s

I think there's something skewed about your test.

Temp tables *are* unlogged. They also live in session-private buffers,
which eliminates a great deal of synchronization overhead; at the cost
that any writing that does happen has to be done by the backend process
itself, without help from the background writer. It's possible that
there's something about your specific test case that makes that scenario
look bad. Another likely source of bogus results is if you were testing
a tiny temp_buffers setting versus a more appropriately sized
shared_buffers setting.

However, this is all speculation, since you provided not a whit of
detail about your test case. Nobody's going to take these numbers
seriously if you haven't explained how to reproduce them.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Mar 26, 2013 at 8:26 AM, Lonni J Friedman <netllama@gmail.com>wrote:

I'm pretty sure that unlogged tables and temp tables are two separate
& distinct features, with no overlap in functionality. It would be
nice if it was possible to create an unlogged temp table.

Temporary tables are a subtype of unlogged tables, as temporary tables are
not WAL-logged.
This article from Robert Haas will give a good summary of such differences:
http://rhaas.blogspot.jp/2010/05/global-temporary-and-unlogged-tables.html
--
Michael

On Mon, Mar 25, 2013 at 4:49 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Tue, Mar 26, 2013 at 8:26 AM, Lonni J Friedman <netllama@gmail.com>
wrote:

I'm pretty sure that unlogged tables and temp tables are two separate
& distinct features, with no overlap in functionality. It would be
nice if it was possible to create an unlogged temp table.

Temporary tables are a subtype of unlogged tables, as temporary tables are
not WAL-logged.
This article from Robert Haas will give a good summary of such differences:
http://rhaas.blogspot.jp/2010/05/global-temporary-and-unlogged-tables.html

Thanks, that's good to know. the official dox don't really make it
clear that temp tables are unlogged.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

temp_buffers = 16MB

--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNLOGGED-TEMPORARY-tables-tp5749477p5749656.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

New test with temporary table and unlogged

Total rows 600k
Table size after copy 121MB

temp_buffers / copy speed

16MB - 12999 rows/s
128MB - 13005 rows/s
256MB - 13258 rows/s
512MB - 13399 rows/s
1GB - 13145 rows/s

Unlogged table - 13333 rows/s

I don't undestand why previous test showed the difference

--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNLOGGED-TEMPORARY-tables-tp5749477p5749662.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general