pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFwd: Table Partitioning
6+ messages / 5 participants
[nested] [flat]
* Fwd: Table Partitioning
@ 2007-08-10 20:58 Nurlan Mukhanov <nurike@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nurlan Mukhanov @ 2007-08-10 20:58 UTC (permalink / raw)
To: pgsql-performance
Hello All.
I have a table with ca. 100.000.000 records. The main idea is make
Partitioning for this table (1000 or 10000 tables).
Let's take for example.
CREATE TABLE test
(
id integer,
data date not null default now()
)
WITHOUT OIDS;
CREATE TABLE test00 ( CHECK ( id%100 = 0 ) ) INHERITS (test);
CREATE TABLE test01 ( CHECK ( id%100 = 1 ) ) INHERITS (test);
...
CREATE TABLE test09 ( CHECK ( id%100 = 9 ) ) INHERITS (test);
-- RULES
CREATE OR REPLACE RULE test00 AS
ON INSERT TO test WHERE (NEW.id%100) = 0
DO INSTEAD INSERT INTO test00 (id) VALUES (NEW.id);
CREATE OR REPLACE RULE test01 AS
ON INSERT TO test WHERE (NEW.id%100) = 1
DO INSTEAD INSERT INTO test01 (id) VALUES (NEW.id);
...
CREATE OR REPLACE RULE test09 AS
ON INSERT TO test WHERE (NEW.id%100) = 9
DO INSTEAD INSERT INTO test09 (id) VALUES (NEW.id);
So the main algorithm is to take last digits of ID and put to special
table. Yes, it is work correct. But when I make a selection query
database ask all table instead of one.
"Aggregate (cost=134.17..134.18 rows=1 width=0)"
" -> Append (cost=4.33..133.94 rows=90 width=0)"
" -> Bitmap Heap Scan on test01 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test01_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test02 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test02_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test03 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test03_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test04 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test04_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test05 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test05_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test06 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test06_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test07 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test07_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test08 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test08_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
" -> Bitmap Heap Scan on test09 (cost=4.33..14.88 rows=10 width=0)"
" Recheck Cond: (id = 1)"
" -> Bitmap Index Scan on test09_id (cost=0.00..4.33
rows=10 width=0)"
" Index Cond: (id = 1)"
If change CHECK to
CREATE TABLE test00 ( CHECK ( id = 0 ) ) INHERITS (test);
CREATE TABLE test01 ( CHECK ( id = 1 ) ) INHERITS (test);
... etc - everything work correct, only one table is asked for data.
But how to implement my idea if ID is always increment and have range
from 1 to BIGINT?
How it is possible or is there any variants to store different IDs in
separated tables when CHECK condition will be used during SELECT or
DELETE queries?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Table Partitioning
@ 2007-08-18 11:11 Henrik <henke@mac.se>
parent: Nurlan Mukhanov <nurike@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Henrik @ 2007-08-18 11:11 UTC (permalink / raw)
To: Nurlan Mukhanov <nurike@gmail.com>; +Cc: pgsql-performance
10 aug 2007 kl. 22:58 skrev Nurlan Mukhanov:
> Hello All.
>
> I have a table with ca. 100.000.000 records. The main idea is make
> Partitioning for this table (1000 or 10000 tables).
> Let's take for example.
>
> CREATE TABLE test
> (
> id integer,
> data date not null default now()
> )
> WITHOUT OIDS;
>
> CREATE TABLE test00 ( CHECK ( id%100 = 0 ) ) INHERITS (test);
> CREATE TABLE test01 ( CHECK ( id%100 = 1 ) ) INHERITS (test);
> ...
> CREATE TABLE test09 ( CHECK ( id%100 = 9 ) ) INHERITS (test);
>
> -- RULES
>
> CREATE OR REPLACE RULE test00 AS
> ON INSERT TO test WHERE (NEW.id%100) = 0
> DO INSTEAD INSERT INTO test00 (id) VALUES (NEW.id);
>
> CREATE OR REPLACE RULE test01 AS
> ON INSERT TO test WHERE (NEW.id%100) = 1
> DO INSTEAD INSERT INTO test01 (id) VALUES (NEW.id);
>
> ...
>
> CREATE OR REPLACE RULE test09 AS
> ON INSERT TO test WHERE (NEW.id%100) = 9
> DO INSTEAD INSERT INTO test09 (id) VALUES (NEW.id);
>
> So the main algorithm is to take last digits of ID and put to special
> table. Yes, it is work correct. But when I make a selection query
> database ask all table instead of one
I'm not sure this will make any difference but are you using SET
constraint_exclusion = on; ?
> .
>
> "Aggregate (cost=134.17..134.18 rows=1 width=0)"
> " -> Append (cost=4.33..133.94 rows=90 width=0)"
> " -> Bitmap Heap Scan on test01 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test01_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test02 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test02_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test03 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test03_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test04 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test04_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test05 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test05_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test06 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test06_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test07 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test07_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test08 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test08_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
> " -> Bitmap Heap Scan on test09 (cost=4.33..14.88 rows=10
> width=0)"
> " Recheck Cond: (id = 1)"
> " -> Bitmap Index Scan on test09_id (cost=0.00..4.33
> rows=10 width=0)"
> " Index Cond: (id = 1)"
>
> If change CHECK to
>
> CREATE TABLE test00 ( CHECK ( id = 0 ) ) INHERITS (test);
>
> CREATE TABLE test01 ( CHECK ( id = 1 ) ) INHERITS (test);
>
> ... etc - everything work correct, only one table is asked for data.
Are your first check algorithm causing overlaps?
Cheers,
henrik
>
> But how to implement my idea if ID is always increment and have range
> from 1 to BIGINT?
> How it is possible or is there any variants to store different IDs in
> separated tables when CHECK condition will be used during SELECT or
> DELETE queries?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 6+ messages in thread
* Table partitioning
@ 2011-03-05 09:37 Tobias Brox <tobixen@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tobias Brox @ 2011-03-05 09:37 UTC (permalink / raw)
To: Landreville <landreville@deadtreepages.com>; +Cc: pgsql-performance
Sorry for not responding directly to your question and for changing
the subject ... ;-)
On 4 March 2011 18:18, Landreville <landreville@deadtreepages.com> wrote:
> That is partitioned into about 3000 tables by the switchport_id (FK to
> a lookup table), each table has about 30 000 rows currently (a row is
> inserted every 5 minutes into each table).
Does such partitioning really make sense? My impression is that the
biggest benefit with table partitioning is to keep old "inactive" data
out of the caches. If so, then it doesn't seem to make much sense to
split a table into 3000 active partitions ... unless, maybe, almost
all queries goes towards a specific partitioning.
According to http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html
...
"Query performance can be improved dramatically in certain situations,
particularly when most of the heavily accessed rows of the table are
in a single partition or a small number of partitions. The
partitioning substitutes for leading columns of indexes, reducing
index size and making it more likely that the heavily-used parts of
the indexes fit in memory."
"All constraints on all partitions of the master table are examined
during constraint exclusion, so large numbers of partitions are likely
to increase query planning time considerably. Partitioning using these
techniques will work well with up to perhaps a hundred partitions;
don't try to use many thousands of partitions."
We have started an archiving project internally in our company since
our database is outgrowing the available memory, I'm advocating that
we should look into table partitioning before we do the archiving,
though it seems to be out of the scope of the project group looking
into the archiving. I'm not sure if I should continue nagging about
it or forget about it ;-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Table partitioning
@ 2011-03-05 09:57 Mark Thornton <mthornton@optrak.com>
parent: Tobias Brox <tobixen@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Mark Thornton @ 2011-03-05 09:57 UTC (permalink / raw)
To: pgsql-performance
On 05/03/2011 09:37, Tobias Brox wrote:
> Sorry for not responding directly to your question and for changing
> the subject ... ;-)
>
> On 4 March 2011 18:18, Landreville<landreville@deadtreepages.com> wrote:
>> That is partitioned into about 3000 tables by the switchport_id (FK to
>> a lookup table), each table has about 30 000 rows currently (a row is
>> inserted every 5 minutes into each table).
> Does such partitioning really make sense? My impression is that the
> biggest benefit with table partitioning is to keep old "inactive" data
> out of the caches. If so, then it doesn't seem to make much sense to
> split a table into 3000 active partitions ... unless, maybe, almost
> all queries goes towards a specific partitioning.
If your partitions a loosely time based and you don't want to discard
old data, then surely the number of partitions will grow without limit.
You could have partitions for say the last 12 months plus a single
partition for 'ancient history', but then you have to transfer the
content of the oldest month to ancient each month and change the
constraint on 'ancient'.
Mark
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Table partitioning
@ 2011-03-05 09:59 Mark Thornton <mthornton@optrak.co.uk>
parent: Tobias Brox <tobixen@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Mark Thornton @ 2011-03-05 09:59 UTC (permalink / raw)
To: pgsql-performance
On 05/03/2011 09:37, Tobias Brox wrote:
> Sorry for not responding directly to your question and for changing
> the subject ... ;-)
>
> On 4 March 2011 18:18, Landreville<landreville@deadtreepages.com> wrote:
>> That is partitioned into about 3000 tables by the switchport_id (FK to
>> a lookup table), each table has about 30 000 rows currently (a row is
>> inserted every 5 minutes into each table).
> Does such partitioning really make sense? My impression is that the
> biggest benefit with table partitioning is to keep old "inactive" data
> out of the caches. If so, then it doesn't seem to make much sense to
> split a table into 3000 active partitions ... unless, maybe, almost
> all queries goes towards a specific partitioning.
If your partitions a loosely time based and you don't want to discard
old data, then surely the number of partitions will grow without limit.
You could have partitions for say the last 12 months plus a single
partition for 'ancient history', but then you have to transfer the
content of the oldest month to ancient each month and change the
constraint on 'ancient'.
Mark
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Table partitioning
@ 2011-03-05 10:09 Tobias Brox <tobixen@gmail.com>
parent: Mark Thornton <mthornton@optrak.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Tobias Brox @ 2011-03-05 10:09 UTC (permalink / raw)
To: Mark Thornton <mthornton@optrak.co.uk>; +Cc: pgsql-performance
On 5 March 2011 12:59, Mark Thornton <mthornton@optrak.co.uk> wrote:
> If your partitions a loosely time based and you don't want to discard old
> data, then surely the number of partitions will grow without limit.
True, but is it relevant? With monthly table partitioning it takes
hundreds of years before having "thousands of partitions".
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-03-05 10:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-10 20:58 Fwd: Table Partitioning Nurlan Mukhanov <nurike@gmail.com>
2007-08-18 11:11 ` Henrik <henke@mac.se>
2011-03-05 09:37 Table partitioning Tobias Brox <tobixen@gmail.com>
2011-03-05 09:57 ` Re: Table partitioning Mark Thornton <mthornton@optrak.com>
2011-03-05 09:59 ` Re: Table partitioning Mark Thornton <mthornton@optrak.co.uk>
2011-03-05 10:09 ` Re: Table partitioning Tobias Brox <tobixen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox