How to get shortest connection between two cities

Yesterday, on #postgresql on irc some guy asked:

22:28 < rafasc> i am trying to use plpgsql to find the shortest path between two cities, each pair of cities has one or more edges, each edge has a different wheight.
22:28 < rafasc> Is there a easy way to compute the shortest path between two cities?

Well, I was not really in a mood to solve it, so I just told him to try with recursive queries, and went on my way.

But I thought about it. And decided to see if I can write the query.

Continue reading How to get shortest connection between two cities

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?

How much RAM is PostgreSQL using?

(disclaimer: all the data and examples in here are on Linux – the same data can be probably obtained on other systems too, it's just that I work on Linux and don't know other systems well).

This question pops occasionally in various places – PostgreSQL is using too much memory, why is that, and how can it be mitigated?

Before we can go to “optimizing", we should understand the problem. But do we? Both standard tools – ps and top – lie. How/why? Let's see.

Continue reading How much RAM is PostgreSQL using?

“= 123” vs. “= ‘depesz'”. What is faster?

There is this idea that normal form in databases require you to use integer, auto incrementing, primary keys.

The idea was discussed by many people, I will just point you to series of three blog posts on the subject by Josh Berkus ( part 1, 2 and 3, and reprise).

One of the points that proponents of surrogate keys (i.e. those based on integer and sequences) raise is that comparing integers is faster than comparing texts. So,

SELECT * FROM users WHERE id = 123

is faster than

SELECT * FROM users WHERE username = 'depesz'

Is it?

Continue reading “= 123" vs. “= ‘depesz'". What is faster?

OmniPITR 0.7.0

Just released new version of OmniPITR.

This version has one important new feature: when you're calling omnipitr-backup-slave, it will make backups only of required xlog files, and not, as previously, of all in walarchive directory.

This is important, especially in case you have multiple slaves, or you keep shared long-term walarchive. Previously – backups would get all files from walarchive (-s option to omnipitr-backup-slave), but now, it picks just the ones that are needed.

On somehow related note – I will be working now, finally, to get omnipitr-monitor functionality working.

OmniPITR 0.6.0

Just released new version, 0.6.0 (it should be visible on pgxn soon) of OmniPITR set of tools.

New version has one new feature – parallelism.

This works in omnipitr-archive and omnipitr-backup-* programs, and allows for parallel delivery to remote destinations (multiple -dr switches).

Also – if you're using compresses wal archive and omnipitr-backup-slave reading from it – all the wal files have to be decompressed before making backup – and this decompression can be parallelized too.

All parallelization is controled using -PJ option (–parallel-jobs), so you can add “-PJ 10" to get up to 10 decompressions at the same time or up to 10 deliveries at the same time.

Let’s talk dirty

Important disclaimer: the module that I'm writing about was written by my colleague Phil Sorber.

We all have been in, or heard about, situation like this:

$ UPDATE users SET password = '...'; WHERE id = 123;

(hint: first ; is before where).

Of course you should have backups, and you can protect yourself from it. But what if backup is too old, and you didn't protect yourself?

Continue reading Let's talk dirty