From: | Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com> |
---|---|
To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Cc: | Charles Gomes <charlesrg(at)outlook(dot)com> |
Subject: | Partition insert trigger using C language |
Date: | 2013-01-10 18:45:43 |
Message-ID: | CAJghg4KJToKVdr=uecKyOYUjx5h3iNdxYPyhLst=8Lv78Oac+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi All,
Inspired by Charles' thread and the work of Emmanuel [1], I have made some
experiments trying to create a trigger to make partitioning using C
language.
The first attempt was not good, I tried to use SPI [2] to create a query to
insert into the correct child table, but it took almost no improvement
compared with the PL/pgSQL code.
Then, I used the Emmanuel's code and digged into the PG source code
(basically at copy.c) to create a trigger function that insert to the
partitioned table direct (using heap_insert instead of SQL) [3], and the
improvement was about 100% (not 4/5 times like got by Emmanuel ). The
function has no other performance trick, like caching the relations or
something like that.
The function does partition based on month/year, but it's easy to change to
day/month/year or something else. And, of course, it's not ready for
production, as I'm not sure if it can break things.
The tests were made using a PL/pgSQL code to insert 1 milion rows, and I
don't know if this is a real-life-like test (probably not). And there is a
table partitioned by month, with a total of 12 partitions (with the
insertions randomly distributed through all 2012).
I put the trigger and the experiments on a repository at GitHub:
https://github.com/matheusoliveira/pg_partitioning_tests
I don't know if this is the right list for the topic, and I thought the old
one has to many messages, so I created this one to show this tirgger sample
and see if someone has a comment about it.
PS: I'd be glad if someone could revise the code to make sure it don't
brake in some corner case. I'm made some tests [4], but not sure if they
covered everything.
PS2: It surely will not work on old versions of PostgreSQL, perhaps not
even 9.1 (not tested).
[1] http://archives.postgresql.org/pgsql-hackers/2008-12/msg01221.php and
http://archives.postgresql.org/pgsql-performance/2012-12/msg00189.php
[2]
https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/spi/partition_insert_trigger_spi.c
[3]
https://github.com/matheusoliveira/pg_partitioning_tests/blob/master/src/partition_insert_trigger.c
[4]
https://github.com/matheusoliveira/pg_partitioning_tests/tree/master/test/regress
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
From | Date | Subject | |
---|---|---|---|
Next Message | Matheus de Oliveira | 2013-01-10 18:48:30 | Re: Slow query after upgrade from 9.0 to 9.2 |
Previous Message | Jeff Janes | 2013-01-10 18:17:21 | Re: Slow query after upgrade from 9.0 to 9.2 |