Thread: 9.5 new features

(sorry to interrupt the discussion on CoC's and social justice, but...)

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....




--
john r pierce, recycling bits in santa cruz

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

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

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

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

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

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

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
theBRIN 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
unneededat planning time, then BRIN can eliminate blocks from a scan of a single relation (or rather "pages_per_range")
duringexecution 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

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
isstored 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
thatare not clustered along the index and it wouldn't make sense to have more than one BRIN index. 

Thomas

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
infois 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