Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will cover about the Postgresql Unlogged Table feature, which came into our lives with the PostgreSQL 9.1 version and significantly increased our typing speed.
PostgreSQL protects our data in most cases, and even if it shuts down in an emergency, our data is securely protected. It monitors every change in the table using PostgreSQL WAL. This causes I\O on the disk side, but doing this ensures that our data is kept securely.
Unlogged Tables are generally efficient in terms of writing speed, but since they are not written to WAL like normal tables, we are likely to lose data.
Unlogged Tables can be useful for data that can be easily recreated in bulk transactions, but your data may be lost in a sudden shutdown.
For example:
1 | CREATE UNLOGGED TABLE table_name(column_name datatype); |
1 | CREATE UNLOGGED TABLE unloggedtabled1(id int, name text); |
1 | ALTER TABLE table_name SET UNLOGGED; |
First, let’s create our table.
1 | create unlogged table unloggedtabled(column1 integer, column2 text, column3 boolean); |
After creating our table, let’s insert 10 000 000 random data.
1 2 3 4 | insert into unloggedtabled(column1,column2,column3) select tt.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,10000000) as tt(id) order by random(); |
10 000 000 random data inserts took 33 seconds and 894 ms as seen below.
Let’s insert the table that is not unlogged. Let’s create our flat table without being unlogged.
1 | create table tabled2(column1 integer, column2 text, column3 boolean); |
After creating our table, let’s insert the same data.
As seen above, our tabled2 table takes longer.
