egorius

https://postgr.es/p/7hr

https://postgr.es/p/7hr

https://postgr.es/p/7hn

PostgreSQL 18 is such an exceptional piece of software - it is hard to imagine anything better, right? I remember a similar thought when PostgreSQL 7 was released (“wow, how cool is that - this is it”). Well, let me put it mildly: I was wrong, and things are a lot better than they used to be. So, obviously the idea that PostgreSQL 18 is as far as humanity can go is also wrong. But what might be next?

Synchronous IO, Asynchronous I/O, Direct I/O?

• We have already spoken about synchronous and asynchronous I/O in PostgreSQL 18 in our previous post. Some other excellent PostgreSQL content about running asynchronous I/O has also been written by Tomas Vondra to explain some of the key aspects of performance and AIO. 

The question is: While a major step forward has been made in PostgreSQL 18, and an important milestone has been achieved … is there anything out there that is even cooler? Something we might see in a couple of years? Well, maybe: I am of course talking about “Direct I/O”.

What is Direct I/O?

Under normal circumstances, a read works like this:

• Application requests some data
  • Kernel accepts the read 
    
    
    • does sanity checks
    
    
    • cache lookup happens
      
      
      • if needed data is read from disk
      
      
      • otherwise the filesystem cache serves the data

• • Application receives an OK

Sounds good? Well it is, and in most cases this is exactly what we want. However, there is an additional method: Direct IO. What it does can be summed up in one sentence:  DirectIO bypasses the OS page cache layer. 

But let us take a look at more detail: The good part is that it skips the overhead and scalability limitations. The bad part is that it skips the services that layer provi

https://postgr.es/p/7hn

https://postgr.es/p/7hk

PostgreSQL 18 was released on September 25, 2024, introducing a host of exciting new features across all major areas. In this blog series, we’ll delve into these features and explore how they can benefit database developers and migration engineers transitioning to PostgreSQL.

Part 1 of the series focuses on enhancements to the COPY command, particularly for use cases involving loading external files into the database.

PostgreSQL 18 – Enhancements with the COPY Command

The COPY command is the default native option for loading flat files, primarily in text or CSV format, into PostgreSQL. If you are a data engineer or database developer looking for ways to load flat files into PostgreSQL, you must take care of quality checks and encapsulate a mechanism to report or discard failed records.

Until PostgreSQL 16, if quality checks were planned to be done on the database side, one option was to load flat files into staging tables with all columns as text or a generic data type and later move legitimate and discarded data to the concerned tables.With PostgreSQL 17 onward, the COPY command provides options for handling data type incompatibilities and logging failed rows or records using on_error options.

In PostgreSQL 18 these features is further enhances with REJECT_LIMIT options. If overall discarded or error row data is beyond the reject limit the copy command is discarded as whole.

PostgreSQL 18 – New REJECT_LIMIT option.

Let’s start by creating sample target tables and a CSV file with intentional data type mismatch issues. The sample table also has check and NOT NULL constraints defined on it.

CREATE TABLE copy18(
    col1 integer,
    col2 text DEFAULT 'x'::text,
    col3 text NOT NULL,
    col4 text,
    col5 boolean,
    col6 date,
    col7 timestamp without time zone
    CONSTRAINT copy18_col4_check CHECK (length(col4) > 2)
);

The CSV file contains data type incompatibilities in the following lines:

• Timestamp (4th line)
• Integer (5th line)
• Boolean type (7t

https://postgr.es/p/7hk

https://postgr.es/p/7hj

It has been a while since last time I blogged about any real-database-life problems and investigation. Here is one which I am really happy about, because it took a while and it was not obvious, but I found the root cause of a problem!

The problem has been going on for months: one of the Postgres databases WAL file system was growing with an alarming speed, many times faster than the database itself. Not like we can’t promptly remove the WALs, but this situation made any database restore a reali nightmare. At first, I suspected long transactions with suboptimal backfill algorithms. There were, indeed, a lot of jobs running on a regular basis which could be improved, however, I noticed a couple of things.

First, when the users fixed one of the primary suspects jobs, the situation with WAL growth didn’t change. Second, the rate of the growth couldn’t be explained by these suboptimal jobs: the data volumes they were removing and reloading were still magnitudes smaller than the WAL size we were dealing with. Finally, I decided to do what I should have done from the start – to take a look at what exactly was in these super-fast growing WALs.

Here is what I did:

create extension  pg_walinspect;

select pg_current_wal_insert_lsn();  /* save the result: '17797/A167C8E8' */

select pg_current_wal_insert_lsn() /* save the result : '17797/FEDE0CC8'*/

select * from  pg_get_wal_stats('17797/A167C8E8', '17797/FEDE0CC8', true)
where count>0;

To my amazement, I realized that 97% of the WALs stored the information about locks! To be honest, I didn’t even know that locks are recorded in the WAL files, so I am thankful for gracious Postgres community enlightening me!

Now that I knew where to look, I ran the following:

select * from pg_get_wal_records_info('17797/A167C8E8', '17797/FEDE0CC8')
where resource_manager='Heap'
and record_type='LOCK'

Zooming in:

Next, I found the table which had all these locks:

select * from pg_class where oid =10229951

When I fo

https://postgr.es/p/7hj