Which tables should be auto vacuumed or auto analyzed – UPDATE

Some time ago I wrote blogpost which showed how to list tables that should be autovacuumed or autoanalyzed.

Query in there had one important problem – it didn't take into account per-table settings.

Continue reading Which tables should be auto vacuumed or auto analyzed – UPDATE

Which tables should be auto vacuumed or auto analyzed?

Recently I was in a situation where autovacuum couldn't keep up with changes. To solve the problem I finally decided to manually vacuum analyze all tables (manual vacuum/analyze is faster than one ran by autovacuum daemon).

But it irritated me that I didn't have ready way to check which tables are waiting for autovacuum to work on them.

So, I wrote it.

Continue reading Which tables should be auto vacuumed or auto analyzed?

Picking task from queue – revisit

Some time ago, I wrote blogpost about how to pick a task from queue, without locking.

It was written in 2013, and as such it couldn't reflect everything we have now in PostgreSQL – namely SKIP LOCKED – which was added to PostgreSQL over year later.

Two people mentioned SKIP LOCKED in comments, but it was before it was committed even to git repo. But now, we have, officially released, PostgreSQL version with this mechanism, so let's see what it can do.

Continue reading Picking task from queue – revisit

Waiting for 9.6 – Add new function pg_notification_queue_usage.

On 17th of July, Robert Haas committed patch:

Add new function pg_notification_queue_usage.
 
This tells you what fraction of NOTIFY's queue is currently filled.
 
Brendan Jurd, reviewed by Merlin Moncure and Gurjeet Singh.  A few
further tweaks by me.

Continue reading Waiting for 9.6 – Add new function pg_notification_queue_usage.

Waiting for 9.5 – Implement SKIP LOCKED for row-level locks

On 7th of October, Alvaro Herrera committed patch:

Implement SKIP LOCKED for row-level locks
 
This clause changes the behavior of SELECT locking clauses in the
presence of locked rows: instead of causing a process to block waiting
for the locks held by other processes (or raise an error, with NOWAIT),
SKIP LOCKED makes the new reader skip over such rows.  While this is not
appropriate behavior for general purposes, there are some cases in which
it is useful, such as queue-like tables.
 
Catalog version bumped because this patch changes the representation of
stored rules.
 
Reviewed by Craig Ringer (based on a previous attempt at an
implementation by Simon Riggs, who also provided input on the syntax
used in the current patch), David Rowley, and Álvaro Herrera.
 
Author: Thomas Munro

Continue reading Waiting for 9.5 – Implement SKIP LOCKED for row-level locks

Pick a task to work on

There is new blogpost on this subject!

There are cases where system stores list of things to do, and then there are some worker processes that check the list, pick something to work on, do it, and remove from the list.

Proper solution is to use some kind of queuing system. There is even PgQ which works withing PostgreSQL, but some people are not happy with it, as it requires compilation and installation. So they just use plain selects.

Will that work OK?

Continue reading Pick a task to work on

How to send mail from database?

Similar question has been asked many times on mailing lists and on IRC. Sometimes it's not mail sending, but file/directory creation, or something else that generally requires some interaction with “world outside of database".

Can it be done? Sure. How, then?

Continue reading How to send mail from database?