Optimizing PostgreSQL Logging

best practices for improved performance

Logging in PostgreSQL plays a crucial role in maintaining a record of activities and troubleshooting issues. To ensure optimal performance, here are some best practices to consider when setting up your PostgreSQL log tables.

1. Independence and Simplicity

When creating a log table, prioritize independence. It’s recommended to keep it as standalone as possible, avoiding constraints and indexes. This helps streamline the logging process and ensures that the log table doesn’t become a bottleneck for performance.

I have created two identical temporary tables temp table1 and temp table2.

2. Choose Wisely

Unlogged vs. Persistent: Consider the criticality of log data when deciding between unlogged and persistent tables. If the loss of log data is not critical and speed is a priority, opt for an unlogged table. On the other hand, if data integrity is paramount, choose a persistent table despite the potential impact on speed.

create unlogged table ppromo_exec_log.general_exec_log_details_tmp
(
    id              integer generated by default as identity,
    log_id          integer,
    rec_date        timestamp,
    user_id         integer,
    st_date         timestamp,
    end_date        timestamp,
    working_time    numeric,
    error_status    integer default 0,
    additional_data jsonb,
    action_name     varchar(10),
    unique_id       uuid
);


create index idx_general_exec_log_details_tmp_id
    on ppromo_exec_log.general_exec_log_details_tmp (id);

I’ve designed an unlogged table for the purpose of efficient table logging. This table features an id column, which is generated as an identity column. To enhance query performance, I’ve implemented a B-tree (that is by default too). Column id is automatically indexed without consuming additional resources. The B-tree index efficiently organizes and separates the data within pages, contributing to a streamlined and resource-efficient indexing process.

3. Mitigate Table Size Impact

To manage the size of the log table effectively, implement a strategy involving two identical tables.

By switching between these tables in the logging procedure (in my case proc: proc_exec_log), you can avoid overwhelming a single table with a massive amount of data. This approach helps maintain performance and ensures that your log table remains manageable.

4. Data Transfer to main table

Create another table dedicated to comprehensive indexing and partitions. This table, designed for reporting and analysis, should be regularly updated with data from the temporary log tables.

create table ppromo_exec_log.general_exec_log_details
(
    project_id      integer not null,
    log_id          integer not null,
    rec_date        timestamp,
    user_id         integer,
    st_date         timestamp,
    end_date        timestamp,
    working_time    numeric,
    error_status    integer default 0,
    additional_data jsonb,
    tmp_id          integer,
    action_name     varchar(10),
    unique_id       uuid
)
    partition by LIST (project_id);

-- add indexes too

Implement a well-thought-out procedure that transfers data from the temporary log tables to the dedicated reporting table. Schedule this procedure to run at intervals using a job scheduler. This ensures that your reporting table remains up-to-date without compromising the real-time logging capabilities.

for i in (SELECT t.id,
                 t.log_id,
                 t.rec_date,
                 t.user_id,
                 t.st_date,
                 t.end_date,
                 t.working_time,
                 t.additional_data,
                 t.action_name,
                 t.unique_id,
                 t.error_status
          FROM ppromo_exec_log.general_exec_log_details_tmp t
          where t.id between v_log_id_start and v_log_id_end)
    loop

        --
        INSERT INTO ppromo_exec_log.general_exec_log_details
        (project_id, tmp_id, log_id, rec_date, user_id, st_date, end_date,
         working_time, additional_data, action_name, unique_id, error_status)
        values (1::integer, i.id, i.log_id, i.rec_date, i.user_id, i.st_date, i.end_date, i.working_time,
                   --
                i.additional_data, i.action_name, i.unique_id, i.error_status);