Tag: postgresql

Tips N’ Tricks – getting sizes of relations without locks

If you have production DB servers, chances are you're running variant of these queries:

SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'i'
SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'r'

To get summarized size of tables and/or indexes in your database (for example for graphing purposes).

This (getting pg_relation_size for rows in pg_class) has one problem – it can lock, or it can fail.

Continue reading Tips N' Tricks – getting sizes of relations without locks

r/trees ( recursive trees, what did you think about? )

I got asked on irc to show some examples how to use recursive CTE. Apparently my previous post wasn't good enough 🙂

I think that most of the users will use recursive cte to deal with trees I decided to show how to use it, even though it's not my favorite approach to dealing with trees in SQL.

Continue reading r/trees ( recursive trees, what did you think about? )

Waiting for 9.2 – excluding data of table from dump

On 14h of December, Andrew Dunstan committed patch:

Add --exclude-table-data option to pg_dump.
 
Andrew Dunstan, reviewed by Josh Berkus, Robert Haas and Peter Geoghegan.
 
This allows dumping of a table definition but not its data, on a per table basis.
Table name patterns are supported just as for --exclude-table.

Continue reading Waiting for 9.2 – excluding data of table from dump

OmniPITR 0.2.0

Just released version 0.2.0 of OmniPITR. There is one important change – that is OmniPITR backup programs can now create checksum files for backups.

Details in docs, but the basic idea is that to all the other options to omnipitr-backup-{master,slave} we added (Phil Sorber to be exact) –digest option, which lets you specify that you want some checksums (md5, sha1, sha2).

Waiting for 9.2 – Index only scans

On 8th of October, Tom Lane committed patch:

Support index-only scans using the visibility map to avoid heap fetches.
 
When a btree index contains all columns required by the query, and the
visibility map shows that all tuples on a target heap page are
visible-to-all, we don't need to fetch that heap page.  This patch depends
on the previous patches that made the visibility map reliable.
 
There's a fair amount left to do here, notably trying to figure out a less
chintzy way of estimating the cost of an index-only scan, but the core
functionality seems ready to commit.
 
Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas.

Continue reading Waiting for 9.2 – Index only scans

What index to create?

Some time ago I wrote a blogpost about why index might not be used.

While this post seemed to be well received (top link from depesz.com on reddit), it doesn't answer another question – what index to create for given situation.

I'll try to cover this question now.

IMPORTANT UPDATE: As of PostgreSQL 10 hash indexes are WAL logged. As such, main point against them is gone.

Continue reading What index to create?

Waiting for 9.2 – filtered info in explain analyze

On 22nd of September, Tom Lane committed patch:

Make EXPLAIN ANALYZE report the numbers of rows rejected by filter steps.
 
This provides information about the numbers of tuples that were visited
but not returned by table scans, as well as the numbers of join tuples
that were considered and discarded within a join plan node.
 
There is still some discussion going on about the best way to report counts
for outer-join situations, but I think most of what's in the patch would
not change if we revise that, so I'm going to go ahead and commit it as-is.
 
Documentation changes to follow (they weren't in the submitted patch
either).
 
Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom

Continue reading Waiting for 9.2 – filtered info in explain analyze