Обсуждение: 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
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?
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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