◎ Photo: by flickrfavorites on flickr
Today I’ve learned about one of the useful PostgreSQL features - ‘unlogged’ tables.
What does this feature exist for?
Say you need to cache some transient data and you don’t really care about it being persisted after a server crash, enter ‘unlogged’ tables.
If you need to “speed-up” your existing table this unsafe way, you can do it like this
alter table "your_table_name" set unlogged;
But to what extent is it “unsafe”?
The types and all of the data are the same; the indexes and like that including. They are not the same as temporary tables.
So you don’t need to change any of your code that uses such tables.
It just works (R) 🎉
Then what is the “catch” for this “speed it up for free”?
Of course, there is nothing for free 😃. By switching a table to ‘unlogged’ you’re excluding this table from the “logging” daemon’s list.
The so-called WAL - Write-Ahead Logging is the nice “workhorse” that makes sure your data is correct and persistent in case of any server failure or abnormal exit.
Also, by switching your table to ‘unlogged’ you are effectively “disabling” the copying of writes to this table to other servers.
And that’s basically it, folks (R) 😃
In case you’ve changed your mind and need to switch back to “safe”, it’s easy as well, just
alter table "your_table_name" set logged;
Here is a handy query for you: “show me all of the ‘unlogged’ tables, foreign keys, and indexes”:
select relname from pg_class where relpersistence = 'u';
Of course, there are much more use cases for this ‘unlogged’ feature, so I encourage you to go “google” them in case “you’ve been sold” on it 😃👍