Postgres partitioning performance – rules vs triggers
Rafal Pietrak asked a question about postgres performance in partitioning scenarios.
The problem is, in classical partitioning approach you decide into which partition put the data basing only on the inserted data itself.
But we consider also situation when you want to make this decision basing on current database content.
For example we have some "driving" or "routing" table which tells us which partition is currently active.
Please read the above post for more background.
I prepared 4 test cases, for all combinations of rule versus trigger and static versus dynamic aka table-driven partitioning.
Test was performed on PostgreSQL 8.3.3 on Linux, commodity desktop box.
To make things short, here are the results of two test runs (links point to test scripts):
/what is measured: INSERT of 10000 rows/
Partitioning with RULEs, no dynamic routing:
2444.293 ms 2516.314 ms
Partitioning with RULEs, with dynamic routing:
42380.037 ms 39248.666 ms
Partitioning with TRIGGER, no dynamic routing:
14512.787 ms 14669.310 ms (but see the update below)
Partitioning with TRIGGER, with dynamic routing:
13486.808 ms 13904.370 ms
Conclusion:
If you have to do some database lookup to decide which partition data belongs to, use a trigger on master table.
If you have a well defined static set of rules, use PostgreSQL rule system.
Update (2010/06/15)
depesz wrote an article in which he noticed some problems with above test case.
First, he points out that the trigger in "Partitioning with TRIGGER, no dynamic routing" example is horribly written. And he is right. In this particular case, partition assignment logic is so simple it can be expressed in 5 lines instead of 1005 lines of code. This makes trigger-based solution an order of magnitude faster.
Second, he says that bulk inserts are not so common - so measuring them is not-so-relevant. This heavily depends on the flavour of database you are working on. For OLTP systems, depesz's statement is obviously true, but for analytic (OLAP and BI) systems, bulk inserts are more common.
Third, in general I agree that rules should be avoided. Triggers are more readable and understood by most database-literate programmers. Rules ... well the first rule of using PostgreSQL rules is "DO NOT USE RULES".
(end of update)
hstore key rename
hstore is a "Lazy DBA" extension for postgres, which enables storing hash tables in a table column.
When using hstore, sometimes you want to rename hash keys.
#
# create function renamekey( _hstore hstore, _oldname text, _newname text ) returns hstore as
$$ select delete($1, $2) || ($3 => ($1 -> $2)) $$ language sql immutable strict;
#
# \set old_codename '''dizzyflag'''
# \set new_codename '''is_trade'''
#
# UPDATE table_with_hstore
SET flags = renamekey(flags, :old_codename, :new_codename)
WHERE (flags -> :old_codename) is not null;
#
Sortowanie w PostgreSQL
Jarek napisał ciekawy artykuł o sortowaniu napisów w PostgreSQL (i nie tylko, w zasadzie rzecz dotyczy localesów w glibc).
explain analyze totals
Same as depesz and Greg, but using user-defined function:
CREATE FUNCTION ea_totals(text) RETURNS text AS $body$
my $result;
my $com = shift;
$com =~ /^\s*explain\s+/i or die qq{Not an explain query\n};
my $rv = spi_exec_query($com);
$rv->{status} eq 'SPI_OK_UTILITY' or die qq{Not a proper explain?\n};
for (map { $_->{'QUERY PLAN'} } @{$rv->{rows}}) {
my $string = ' ' x 10;
if ( /actual time=\d+\.\d*\.\.(\d+\.\d*) rows=\d+ loops=(\d+)/ ) {
$string = sprintf("%10.1f", $1 * $2);
}
$result .= "$string $_ \n";
}
return $result;
$body$ LANGUAGE plperl;
Usage:
SELECT * FROM ea_totals( $$ explain analyze query here $$);
zmiany w mysql
Porównywałem ostatnio funkcjonalność postgresa i mysql w zakresie replikacji, i mam kilka obserwacji.
Jak wiadomo, mysql posiada wbudowaną obsługę replikacji.
Posiada ona taką główną zaletę, że jest wbudowana >;)
Z drugiej strony - ma poważne ograniczenia, ponieważ jest oparta na logowaniu zapytań, a nie zmian w danych tak jak np. Slony.
Tutaj można znaleźć więcej informacji.
Jednak mysql przechodzi poważne zmiany...
W wersji 5.0, która jest obecnie stabilna pojawiły się procedury składowane oraz triggery. oba te "ficzery" działają w dosyć ograniczonym zakresie.
Problemy jakie autorzy mysql mieli z nimi dobrze widać, gdy czyta się dokumentację - cały rozdział o procedurach składowanych i triggerach najeżony jest ostrzeżeniami dotyczącymi logu binarnego (binlog), który jest czymś w rodzaju logu transakcji - a zarazem podstawą systemu replikacji w mysql.
Nic dziwnego - idea replikacji "statement-based" jest z natury słaba. Jest to prymitywne podejście zastosowane bez specjalnych sukcesów m.in. w pgpool.
Odpowiedzią na te problemy ze strony mysql jest wprowadzony w wersji 5.1.x nowy format binlog. Loguje on zmiany już "po bożemu" czyli per rekord.
Umożliwia to prawidłową replikację nawet gdy korzysta się z triggerów i procedur modyfikujących dane.
Podsumowując:
mysql próbuje przesunąć się w stronę dużych RDBMS.
niewykluczone że za parę lat zmieni się w system bazodanowy z prawdziwego zdarzenia.