Hello! Logged tables log the changes to WAL before writing to table file, this IO requires additional time to complete operation. In this article we will discuss about postgresql unlogged table performance and make a comparison between those two options.

Any insert or update will be logged to write ahead log before running in the table / database level in a logged table. Using a logged table ensures:

• Data consistency
• Data recovery in case of a system crash

Unlogged Tables: When to use?

Unlogged table does not generate WAL, that means if database crashes during a transaction, data will be lost. You can learn more about Write Ahead Logging from it’s documentation. Let’s think about a basic example here, think as we are doing an insert.

1. Write to write ahead log
2. Flush changes to table

Unlogged tables skip #1. That means, if server crashes while writing data to database you will lost unwritten data. But it has some advantages as well. Due to it skips first step, changes done faster. I suggest to use unlogged tables when you need:

• Temporary tables to collect information from different tables
• Faster insert time to make calculation
• Reporting
• Bulk inserts
• Smaller backups

As you can see one of key contributions is postgresql unlogged table performance is the performance improvement itself.

Let’s Try

In this chapter, we will try to understand difference of postgresql unlogged table performance.

I will create two tables, with one single columns. First on will be a logged table, and second one will be an unlogged table, let’s start!

create table LOGGED_TABLE (mycolumn integer);
create unlogged table UNLOGGED_TABLE (mycolumn integer);

We will insert 100 millions of records to our tables and observe how long will it take to complete the operation.

Postgesql Logged Table Performance

Run this simple query to complete inserts:

insert into logged_table values(generate_series(1,100000000));

I use dbeaver to run the tests, if you use psql you can run select now(); before and after the query to understand how long does it take.

It took 52 seconds to complete inserting to a unlogged table.

Postgresql Unlogged Table Performance

It took 30 seconds to complete inserting to an unlogged table.

Unplugged tables can complete inserts faster, here we insert 100 millions of rows to our tables, for this significant amount of data there is 50% performance difference.

To Sum Up

Modern database engines have different (and less used) features, i believe unlogged tables are one of them.

In cases you need more performance and not caring about data integrity (Like ETL process) you can use unlogged tables. As we tested postgresql unlogged table performance in this article, discovered it’s positive performance difference.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch