September 1st, 2014 by depesz | Tags: , , , , , , , | No comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

On 22nd of August, Alvaro Herrera committed patch:

Implement ALTER TABLE .. SET LOGGED / UNLOGGED
 
This enables changing permanent (logged) tables to unlogged and
vice-versa.
 
(Docs for ALTER TABLE / SET TABLESPACE got shuffled in an order that
hopefully makes more sense than the original.)
 
Author: Fabrízio de Royes Mello
Reviewed by: Christoph Berg, Andres Freund, Thom Brown
Some tweaking by Álvaro Herrera

In 9.1 we got unlogged tables. To summarize – these are tables that are much faster for writes, but are designed to lose all data in case of server failure.

That means – if you'll stop pg and start it properly – data will be there, but if you'll do “immediate stop", or you'll lose power, or pg will get “kill -9″‘ed – all the data is gone.

One of side effects of these tables were that they were not replicated (via WAL) – well, no log, nothing to replicate.

Now, with 9.5, we'll get ability to switch state of table – logged/unlogged.

It's not free (by far), but it's there.

How does it work? That's simple:

$ create unlogged table test (a int4);
CREATE TABLE
 
$ \d test
 Unlogged table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer |

And now:

$ alter table test set logged;
ALTER TABLE
 
$ \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer |

and of course, I can do the same, the other way around:

$ alter table test set unlogged;
ALTER TABLE
 
$ \d test
 Unlogged table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer |

It's nice, but there is one significant issue, or more specifically – a thing you should know about.

Let's see some details:

$ alter table test add primary key (a);
ALTER TABLE
 
$ select relname, relkind, relpersistence, relfilenode from pg_class where relname in ('test', 'test_pkey');
  relname  | relkind | relpersistence | relfilenode 
-----------+---------+----------------+-------------
 test      | r       | u              |       18067
 test_pkey | i       | u              |       18070
(2 rows)

and now, let's make it logged:

$ alter table test set logged;
ALTER TABLE
 
$ select relname, relkind, relpersistence, relfilenode from pg_class where relname in ('test', 'test_pkey');
  relname  | relkind | relpersistence | relfilenode 
-----------+---------+----------------+-------------
 test      | r       | p              |       18075
 test_pkey | i       | p              |       18078
(2 rows)

relpersistence column has information whether it's logged ( relpersistence = ‘p' ) or not (relpersistence = ‘u').

But please note the relfilenode – it has changed.

Reason is very simple – changing logged state requires rewrite of the table. During this time the table is exclusively locked.

So, while it might be solution to certain cases, I think generally some caution will have to be applied to avoid locking application out of use.

In any way – it's a step in right direction (more power), so I'm very happy with it. Thanks guys.

Leave a comment