Re: [HACKERS] Physical append-only tables

2016-11-28 Thread Masahiko Sawada
On Mon, Nov 28, 2016 at 9:05 AM, Jim Nasby wrote: > On 11/24/16 8:18 AM, Bruce Momjian wrote: >>> >>> What if we used BRIN to find heap pages where the new row was in the >>> page BRIN min/max range, and the heap page had free space. Only if >>> that >>>

Re: [HACKERS] Physical append-only tables

2016-11-27 Thread Jim Nasby
On 11/24/16 8:18 AM, Bruce Momjian wrote: What if we used BRIN to find heap pages where the new row was in the page BRIN min/max range, and the heap page had free space. Only if that fails do we put is somewhere else in the heap. That would certainly be useful. You'd have to

Re: [HACKERS] Physical append-only tables

2016-11-24 Thread Bruce Momjian
On Thu, Nov 24, 2016 at 10:13:30AM +0100, Magnus Hagander wrote: > On Thu, Nov 24, 2016 at 2:26 AM, Bruce Momjian wrote: > > On Mon, Nov 14, 2016 at 08:43:12PM +, Greg Stark wrote: > > That said, I don't think the "maintain clustering a bit better using > >

Re: [HACKERS] Physical append-only tables

2016-11-24 Thread Magnus Hagander
On Thu, Nov 24, 2016 at 2:26 AM, Bruce Momjian wrote: > On Mon, Nov 14, 2016 at 08:43:12PM +, Greg Stark wrote: > > That said, I don't think the "maintain clustering a bit better using > > BRIN" is a bad idea. It's just the bit about turning a table > > append-only to deal

Re: [HACKERS] Physical append-only tables

2016-11-23 Thread Bruce Momjian
On Mon, Nov 14, 2016 at 08:43:12PM +, Greg Stark wrote: > That said, I don't think the "maintain clustering a bit better using > BRIN" is a bad idea. It's just the bit about turning a table > append-only to deal with update-once data that I think is overreach. What if we used BRIN to find

Re: [HACKERS] Physical append-only tables

2016-11-15 Thread Magnus Hagander
On Mon, Nov 14, 2016 at 9:43 PM, Greg Stark wrote: > On Sun, Nov 13, 2016 at 3:45 PM, Magnus Hagander > wrote: > > For a scenario like this, would it make sense to have an option that > could > > be set on an individual table making it physical append only?

Re: [HACKERS] Physical append-only tables

2016-11-14 Thread Greg Stark
On Sun, Nov 13, 2016 at 3:45 PM, Magnus Hagander wrote: > For a scenario like this, would it make sense to have an option that could > be set on an individual table making it physical append only? Basically > VACUUM would run as normal and clean up the old space when rows are

Re: [HACKERS] Physical append-only tables

2016-11-14 Thread Magnus Hagander
On Mon, Nov 14, 2016 at 3:39 PM, Tom Lane wrote: > Magnus Hagander writes: > > What I'm talking about is something that would be a lot simpler than > > auto-clustering. I'm not even talking about trying to detect if the row > was > > about to go into the

Re: [HACKERS] Physical append-only tables

2016-11-14 Thread Tom Lane
Magnus Hagander writes: > What I'm talking about is something that would be a lot simpler than > auto-clustering. I'm not even talking about trying to detect if the row was > about to go into the right place -- this might be expensive and certainly > more complicated. I'm

Re: [HACKERS] Physical append-only tables

2016-11-14 Thread Magnus Hagander
On Mon, Nov 14, 2016 at 2:35 AM, Alvaro Herrera wrote: > Magnus Hagander wrote: > > > But then consider the same table. Except rows are typically updated once > or > > twice when they are new, and *then* go read only. And we also have a > > process that at some point

Re: [HACKERS] Physical append-only tables

2016-11-13 Thread Alvaro Herrera
Magnus Hagander wrote: > But then consider the same table. Except rows are typically updated once or > twice when they are new, and *then* go read only. And we also have a > process that at some point deletes *some* old rows (but not all - in fact, > only a small portion). > > In this case, the

Re: [HACKERS] Physical append-only tables

2016-11-13 Thread Thomas Munro
On Mon, Nov 14, 2016 at 4:45 AM, Magnus Hagander wrote: > For a scenario like this, would it make sense to have an option that could > be set on an individual table making it physical append only? Basically > VACUUM would run as normal and clean up the old space when rows are

[HACKERS] Physical append-only tables

2016-11-13 Thread Magnus Hagander
Scenario: Large table, *mostly* insert+select only, either a sequence or a timestamp column set to current time. This is an ideal usecase for a BRIN index. Awesome. But then consider the same table. Except rows are typically updated once or twice when they are new, and *then* go read only. And