Tropeçando 72

A simple JSON difference function

A function that would take two JSONB objects in PostgreSQL, and return how the left-hand side differs from the right-hand side.

Deadlocks in PostgreSQL

Compreenda os tipos de lock existentes no PostgreSQL, como ocorre um deadlock e pesquisar o causador do lock.

PG Phriday: COPY and Alternative Import Methods

Alternativa para cópia de conteúdo de arquivos para o banco de dados.

Always Do This #4: Put stats_temp_directory on a memory file system

The PostgreSQL statistics collector generates a lot of very important statistics about the state of the database. If it’s not working, autovacuum doesn’t work, among other problems. But it does generate a lot of write activity, and by default, that goes back onto the database volume.

Instead, always set statstempdirectory to point to a RAM disk (which has to be owned by the postgres user, with 0600 permissions). The statistics are written back to the database on shutdown, so in normal operations, you won’t lose anything on a reboot. (You’ll lose the most recent statistics on a crash, but you will anyway; the statistics are reset on recovery operations, including restart from a crash.)

This can substantially cut down the amount of write I/O the main database storage volume has to receive, and it’s free!

Putting stats_temp_directory on a ramdisk

When statistics are not generated, we can have, among other problems, the halt of the autovacuum execution. As a consequence of the problems caused by the interruption of statistics collection, large spikes in writing activity end up occurring, which overloads server utilization. Changing the stat_temp_directory setting can prevent this.