Re: All Taxi Services need Index Clustered Heap Append

Colleagues incomprehensible situation: it seems that the statistics for autovacuum analyze is not passed to the slave. After a manual run, vacuum analyze all comes to life. the essence of this is a frequently changing table when I request a replica I get on a big Heap Fetches: 18623 the request is

Re: All Taxi Services need Index Clustered Heap Append

Have you looked at Timescale DB extension on postgresql? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: All Taxi Services need Index Clustered Heap Append

On 02.03.2018 19:30, Darafei "Komяpa" Praliaskouski wrote: Hi, I work at a ride sharing company and we found a simple scenario that Postgres has a lot to improve at. After my talk at pgconf.ru Alexander Korotkov encouraged me to share my story and thoughts in -hackers. S

Re: All Taxi Services need Index Clustered Heap Append

Hello, Would the following custom solution: - a pre-loaded table rows being sorted by id and ts containing null values for other columns, enough free space per block to permit updates in place, - having a (btree or brin) index on (id,ts), - loaded using UPDATEs in spite of INSERTs

Re: All Taxi Services need Index Clustered Heap Append

> On Mar 6, 2018, at 04:57, Craig Ringer wrote: > > On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski > wrote: > > I gave this all some thought and it looks like it all could have not happened > if Postgres was able to cluster heap insertions by (id, ts) index

Re: All Taxi Services need Index Clustered Heap Append

вт, 6 мар. 2018 г. в 4:57, Craig Ringer : > On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski > wrote: > > >> I gave this all some thought and it looks like it all could have not >> happened if Postgres was able to cluster heap insertions by (id, ts) index. >> We're ok with synchronuous_co

Re: All Taxi Services need Index Clustered Heap Append

пн, 5 мар. 2018 г. в 19:48, Ants Aasma : > On Mon, Mar 5, 2018 at 2:11 PM, Darafei "Komяpa" Praliaskouski > wrote: > >> This approach mixes well with hash > >> partitioning. It would be neat indeed if PostgreSQL do something > >> equivalent on its own, and pluggable storage work being done could

Re: All Taxi Services need Index Clustered Heap Append

On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski wrote: > I gave this all some thought and it looks like it all could have not > happened if Postgres was able to cluster heap insertions by (id, ts) index. > We're ok with synchronuous_commit=off, so amplified write won't immediately > hit

Re: All Taxi Services need Index Clustered Heap Append

https://aws.amazon.com/dms/ DMS might be helpful if you need to move off of RDS Alex Kane On Mon, Mar 5, 2018 at 11:48 AM, Ants Aasma wrote: > On Mon, Mar 5, 2018 at 2:11 PM, Darafei "Komяpa" Praliaskouski > wrote: > >> This approach mixes well with hash > >> partitioning. It would be neat i

Re: All Taxi Services need Index Clustered Heap Append

On Mon, Mar 5, 2018 at 2:11 PM, Darafei "Komяpa" Praliaskouski wrote: >> This approach mixes well with hash >> partitioning. It would be neat indeed if PostgreSQL do something >> equivalent on its own, and pluggable storage work being done could >> enable index organized tables that would help. Bu

Re: All Taxi Services need Index Clustered Heap Append

> > This approach mixes well with hash > partitioning. It would be neat indeed if PostgreSQL do something > equivalent on its own, and pluggable storage work being done could > enable index organized tables that would help. But you probably need > something right now. > Fixing glaring issues (no v

Re: All Taxi Services need Index Clustered Heap Append

Hello Darafei, > After my talk at pgconf.ru Alexander Korotkov encouraged me to share my > story and thoughts in -hackers. > [...] > - An append-only table of shape (id uuid, ts timestamp, geom geometry, > heading speed accuracy float, source text). > A btree index on (id, ts). > [...] > select *

Re: All Taxi Services need Index Clustered Heap Append

On 4 March 2018 at 23:05, Ants Aasma wrote: > On Sat, Mar 3, 2018 at 4:53 PM, David Rowley >> It's a good job someone invented HASH partitioning then. >> >> It would be interesting to hear how your benchmarks go using current >> master + the faster partition pruning patchset [1]. Currently, HASH

Re: All Taxi Services need Index Clustered Heap Append

On Sat, Mar 3, 2018 at 4:53 PM, David Rowley wrote: > On 3 March 2018 at 05:30, Darafei "Komяpa" Praliaskouski > wrote: >> Our options were: >> >> - partitioning. Not entirely trivial when your id is uuid. To get visible >> gains, we need to make sure each driver gets their own partition. That

Re: All Taxi Services need Index Clustered Heap Append

On Fri, Mar 2, 2018 at 6:30 PM, Darafei "Komяpa" Praliaskouski wrote: > I gave this all some thought and it looks like it all could have not > happened if Postgres was able to cluster heap insertions by (id, ts) index. > We're ok with synchronuous_commit=off, so amplified write won't immediately >

Re: All Taxi Services need Index Clustered Heap Append

On 3 March 2018 at 05:30, Darafei "Komяpa" Praliaskouski wrote: > Our options were: > > - partitioning. Not entirely trivial when your id is uuid. To get visible > gains, we need to make sure each driver gets their own partition. That would > leave us with 50 000(+) tables, and rumors say that i

All Taxi Services need Index Clustered Heap Append

Hi, I work at a ride sharing company and we found a simple scenario that Postgres has a lot to improve at. After my talk at pgconf.ru Alexander Korotkov encouraged me to share my story and thoughts in -hackers. Story setting: - Amazon RDS (thus vanilla unpatchable postgres), synchronuous_commit