9.5 new features

On 23 January 2016 at 09:49, John R Pierce <pierce@hogranch.com> wrote:

one of my coworkers says he thought that 9.5 has some enhancements in
partitioning, but looking at the release notes I don't see anything specific
? do BRIN's play into partitioned tables ?

in our case, we partition very large 'event' tables by week with 6 month
retention....

BRIN can be seen as a form of "automatic partitioning", and I have
seen it described as such in documents relating to the BRIN project,
so perhaps that description has made its way further afield and that's
maybe what your coworker heard about.

If you view the inheritance partitioning feature as a method of
eliminating scans of partitions which can be proved unneeded at
planning time, then BRIN can eliminate blocks from a scan of a single
relation (or rather "pages_per_range") during execution time. So I
agree with the "automatic partitioning" description.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/22/2016 7:13 PM, David Rowley wrote:

On 23 January 2016 at 09:49, John R Pierce<pierce@hogranch.com> wrote:

one of my coworkers says he thought that 9.5 has some enhancements in
partitioning, but looking at the release notes I don't see anything specific
? do BRIN's play into partitioned tables ?

in our case, we partition very large 'event' tables by week with 6 month
retention....

BRIN can be seen as a form of "automatic partitioning", and I have
seen it described as such in documents relating to the BRIN project,
so perhaps that description has made its way further afield and that's
maybe what your coworker heard about.

If you view the inheritance partitioning feature as a method of
eliminating scans of partitions which can be proved unneeded at
planning time, then BRIN can eliminate blocks from a scan of a single
relation (or rather "pages_per_range") during execution time. So I
agree with the "automatic partitioning" description.

ok, but it doesn't deal with our use case of needing to bulk delete a 6

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/23/2016 04:42 AM, John R Pierce wrote:

On 1/22/2016 7:13 PM, David Rowley wrote:

BRIN can be seen as a form of "automatic partitioning", and I have
seen it described as such in documents relating to the BRIN project,
so perhaps that description has made its way further afield and that's
maybe what your coworker heard about.

If you view the inheritance partitioning feature as a method of
eliminating scans of partitions which can be proved unneeded at
planning time, then BRIN can eliminate blocks from a scan of a single
relation (or rather "pages_per_range") during execution time. So I
agree with the "automatic partitioning" description.

ok, but it doesn't deal with our use case of needing to bulk delete a 6

I can't really parse the end of that sentence, but you are correct that
BRIN does not help at all with partition dropping. Think of it more as
a Seq Scan optimization.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/23/2016 12:35 AM, Vik Fearing wrote:

ok, but it doesn't deal with our use case of needing to bulk delete a 6

I can't really parse the end of that sentence, but you are correct that
BRIN does not help at all with partition dropping. Think of it more as
a Seq Scan optimization.

yeah, it was supposed to say, bulk delete 6 month old data once a week,
while the database is still under a full production load of new data.

anyways, yeah, BRIN sounds very cool for very large tables with
relatively stable data.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/23/2016 10:28 AM, John R Pierce wrote:

On 1/23/2016 12:35 AM, Vik Fearing wrote:

ok, but it doesn't deal with our use case of needing to bulk delete a 6

I can't really parse the end of that sentence, but you are correct that
BRIN does not help at all with partition dropping. Think of it more as
a Seq Scan optimization.

yeah, it was supposed to say, bulk delete 6 month old data once a week,
while the database is still under a full production load of new data.

anyways, yeah, BRIN sounds very cool for very large tables with
relatively stable data.

I have found it particularly efficient when the BRIN index contains all
columns of the table. Just using it on one or two columns is not a win
over btree.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 23 January 2016 at 22:41, Vik Fearing <vik@2ndquadrant.fr> wrote:

On 01/23/2016 10:28 AM, John R Pierce wrote:

anyways, yeah, BRIN sounds very cool for very large tables with
relatively stable data.

I have found it particularly efficient when the BRIN index contains all
columns of the table. Just using it on one or two columns is not a win
over btree.

I've found it to be very useful for very large INSERT only tables with
a column which increments with each insert, e.g. a timestamp. This
allows large portions on the table to be skipped during a scan, and
also maintains sequential read speeds which I don't think would work
quite as efficiently with btree index performing heap lookups.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sounds like a great feature.

How can it be tested?

I am particularly thinking of window servers and effect of this on a live system and any performance issues.

It is an exciting feature. Thanks guys.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Rowley
Sent: 23 January 2016 03:14
To: John R Pierce
Cc: PostgreSQL
Subject: Re: [GENERAL] 9.5 new features

On 23 January 2016 at 09:49, John R Pierce <pierce@hogranch.com> wrote:

one of my coworkers says he thought that 9.5 has some enhancements in
partitioning, but looking at the release notes I don't see anything specific
? do BRIN's play into partitioned tables ?

in our case, we partition very large 'event' tables by week with 6
month retention....

BRIN can be seen as a form of "automatic partitioning", and I have seen it described as such in documents relating to the BRIN project, so perhaps that description has made its way further afield and that's maybe what your coworker heard about.

If you view the inheritance partitioning feature as a method of eliminating scans of partitions which can be proved unneeded at planning time, then BRIN can eliminate blocks from a scan of a single relation (or rather "pages_per_range") during execution time. So I agree with the "automatic partitioning" description.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

http://www.postgresql.org/docs/9.5/interactive/brin-intro.html

62.1. Introduction
...
"A block range is a group of pages that are physically adjacent in the
table; for each block range, some summary info is stored by the index."

From the above, may I presume that it is best to cluster (or sort), the
table based on the intended BRIN
column(s) before actually creating the index to insure the pages are
adjacent? If so, should that not be included in the documentation, instead
of implied?

On Sat, Jan 23, 2016 at 5:49 AM, FarjadFarid(ChkNet) <
farjad.farid@checknetworks.com> wrote:

Sounds like a great feature.

How can it be tested?

I am particularly thinking of window servers and effect of this on a live
system and any performance issues.

It is an exciting feature. Thanks guys.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:
pgsql-general-owner@postgresql.org] On Behalf Of David Rowley
Sent: 23 January 2016 03:14
To: John R Pierce
Cc: PostgreSQL
Subject: Re: [GENERAL] 9.5 new features

On 23 January 2016 at 09:49, John R Pierce <pierce@hogranch.com> wrote:

one of my coworkers says he thought that 9.5 has some enhancements in
partitioning, but looking at the release notes I don't see anything

specific

? do BRIN's play into partitioned tables ?

in our case, we partition very large 'event' tables by week with 6
month retention....

BRIN can be seen as a form of "automatic partitioning", and I have seen it
described as such in documents relating to the BRIN project, so perhaps
that description has made its way further afield and that's maybe what your
coworker heard about.

If you view the inheritance partitioning feature as a method of
eliminating scans of partitions which can be proved unneeded at planning
time, then BRIN can eliminate blocks from a scan of a single relation (or
rather "pages_per_range") during execution time. So I agree with the
"automatic partitioning" description.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson schrieb am 23.01.2016 um 16:27:

http://www.postgresql.org/docs/9.5/interactive/brin-intro.html

62.1. Introduction
...
"A block range is a group of pages that are physically adjacent in the table; for each block range, some summary info is stored by the index."

From the above, may I presume that it is best to cluster (or sort),
the table based on the intended BRIN column(s) before actually
creating the index to insure the pages are adjacent? If so, should
that not be included in the documentation, instead of implied?

That is something I am also curious about. If that was true, it would mean that BRIN indexes couldn't be used on tables that are not clustered along the index and it wouldn't make sense to have more than one BRIN index.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I just noticed this old thread.

Thomas Kellerer wrote:

62.1. Introduction
...
"A block range is a group of pages that are physically adjacent in the table; for each block range, some summary info is stored by the index."

From the above, may I presume that it is best to cluster (or sort),
the table based on the intended BRIN column(s) before actually
creating the index to insure the pages are adjacent? If so, should
that not be included in the documentation, instead of implied?

That is something I am also curious about. If that was true, it would
mean that BRIN indexes couldn't be used on tables that are not
clustered along the index

There's no hard requirement that values must be clustered. If the
values are clustered, that's the best case scenario for BRIN and things
will be very quick. However, clustering a table is a slow operation and
requires locking the table, so I don't recommend that.

But strict correlation isn't really necessary either -- you just need
the values to be grouped together. To illustrate, consider this
simplistic case: table has four pages, all the values in the first page
have col1=999, page 2 has all col1=1, page 3 has all col1=1500, page 4
has col1=-1000. There's little correlation there, but a BRIN index with
pages_per_range=1 can still help a query that looks for col1 > 500
execute optimally.

it wouldn't make sense to have more than one BRIN index.

Well, you can put all the columns in a single index, and it works just
like if you had one index for each column. However, if you want a BRIN
index that's more detailed for certain columns than others, you can use
different pages_per_range settings on multiple indexes.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general