Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 9:32 PM Robert Haas wrote: > > On Thu, Dec 6, 2018 at 10:53 AM Pavel Stehule wrote: > > čt 6. 12. 2018 v 16:26 odesílatel Robert Haas > > napsal: > >> On Thu, Dec 6, 2018 at 10:23 AM Pavel Stehule > >> wrote: > >> > I have a problem to imagine it. When fill factor will

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 17:02 odesílatel Robert Haas napsal: > On Thu, Dec 6, 2018 at 10:53 AM Pavel Stehule > wrote: > > čt 6. 12. 2018 v 16:26 odesílatel Robert Haas > napsal: > >> On Thu, Dec 6, 2018 at 10:23 AM Pavel Stehule > wrote: > >> > I have a problem to imagine it. When fill factor will

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 10:53 AM Pavel Stehule wrote: > čt 6. 12. 2018 v 16:26 odesílatel Robert Haas napsal: >> On Thu, Dec 6, 2018 at 10:23 AM Pavel Stehule >> wrote: >> > I have a problem to imagine it. When fill factor will be low, then there >> > is high risk of high fragmentation - or

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 16:26 odesílatel Robert Haas napsal: > On Thu, Dec 6, 2018 at 10:23 AM Pavel Stehule > wrote: > > I have a problem to imagine it. When fill factor will be low, then there > is high risk of high fragmentation - or there some body should to do > defragmentation. > > I don't

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 10:23 AM Pavel Stehule wrote: > I have a problem to imagine it. When fill factor will be low, then there is > high risk of high fragmentation - or there some body should to do > defragmentation. I don't understand this. -- Robert Haas EnterpriseDB:

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 16:12 odesílatel Robert Haas napsal: > On Thu, Dec 6, 2018 at 2:11 AM Pavel Stehule > wrote: > >> > I am sorry, I know zero about zheap - does zheap use fill factor? if > yes, why? > >> > >> Good question. It is required because tuples can expand (Update tuple > >> to bigger

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 2:11 AM Pavel Stehule wrote: >> > I am sorry, I know zero about zheap - does zheap use fill factor? if yes, >> > why? >> >> Good question. It is required because tuples can expand (Update tuple >> to bigger length). In such cases, we try to perform in-place update >> if

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 8:08 odesílatel Amit Kapila napsal: > On Thu, Dec 6, 2018 at 12:30 PM Pavel Stehule > wrote: > > > > čt 6. 12. 2018 v 7:55 odesílatel Mithun Cy > napsal: > >> > >> On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila > wrote: > >> > > >> > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 12:30 PM Pavel Stehule wrote: > > čt 6. 12. 2018 v 7:55 odesílatel Mithun Cy > napsal: >> >> On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila wrote: >> > >> > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule >> > wrote: >> > > >> > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 7:55 odesílatel Mithun Cy napsal: > On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila > wrote: > > > > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule > wrote: > > > > > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy > napsal: > > >> > > >> COPY command seems to have improved very

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 11:13 AM Amit Kapila wrote: > > On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule wrote: > > > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy > > napsal: > >> > >> COPY command seems to have improved very slightly with zheap in both with > >> size of wal and execution time. I

Re: zheap: a new storage format for PostgreSQL

On Thu, Dec 6, 2018 at 10:03 AM Pavel Stehule wrote: > > čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy > napsal: >> >> COPY command seems to have improved very slightly with zheap in both with >> size of wal and execution time. I also did some tests with insert statement >> where I could see

Re: zheap: a new storage format for PostgreSQL

čt 6. 12. 2018 v 5:02 odesílatel Mithun Cy napsal: > > On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila > wrote: > > I did some testing for performance of COPY command for zheap against heap, > here are my results, > Machine : cthulhu, (is a 8 node numa machine with 500GB of RAM) > server non default

Re: zheap: a new storage format for PostgreSQL

> On Thu, Mar 1, 2018 at 7:39 PM Amit Kapila wrote: I did some testing for performance of COPY command for zheap against heap, here are my results, Machine : cthulhu, (is a 8 node numa machine with 500GB of RAM) server non default settings: shared buffers 32GB, max_wal_size = 20GB, min_wal_size

Re: zheap: a new storage format for PostgreSQL

On Tue, Nov 20, 2018 at 12:53 PM Darafei "Komяpa" Praliaskouski wrote: >> >> > In PostGIS workloads, UPDATE table SET geom = ST_CostyFunction(geom, >> > magicnumber); is one of biggest time-eaters that happen upon initial load >> > and clean up of your data. It is commonly followed by CLUSTER

Re: zheap: a new storage format for PostgreSQL

> > > In PostGIS workloads, UPDATE table SET geom = ST_CostyFunction(geom, > magicnumber); is one of biggest time-eaters that happen upon initial load > and clean up of your data. It is commonly followed by CLUSTER table using > table_geom_idx; to make sure you're back at full speed and no VACUUM

Re: zheap: a new storage format for PostgreSQL

On Mon, Nov 19, 2018 at 6:36 PM Daniel Westermann wrote: > > >Yes, we need to connect to the database for performing rollback > >actions. Once the rollback for that database is over, undo apply > >worker will exit and you should be able to drop the database. > > Thank you, Amit. > Can you have a

Re: zheap: a new storage format for PostgreSQL

>Yes, we need to connect to the database for performing rollback >actions.  Once the rollback for that database is over, undo apply >worker will exit and you should be able to drop the database. Thank you, Amit. Can you have a look at this one? create table t1 ( a text ) partition by list (a);

Re: zheap: a new storage format for PostgreSQL

On Mon, Nov 19, 2018 at 3:59 PM Daniel Westermann wrote: > > > Thanks, it makes difference and keep us motivated for making progress. > +1 > > Is it intended behavior that a database can not be dropped when undo apply is > running in the background? > Yes, we need to connect to the database for

Re: zheap: a new storage format for PostgreSQL

> Thanks, it makes difference and keep us motivated for making progress. +1 Is it intended behavior that a database can not be dropped when undo apply is running in the background? zheap=# update pgbench_accounts set filler = 'bbb' where mod(aid,10) = 0; UPDATE 100 zheap=# rollback;

Re: zheap: a new storage format for PostgreSQL

On Sun, Nov 18, 2018 at 3:42 PM Darafei "Komяpa" Praliaskouski wrote: > > On Sat, Nov 17, 2018 at 8:51 AM Adam Brusselback > wrote: >> >> > I don't know how much what I write on this thread is read by others or >> how useful this is for others who are following this work >> >> I've been

Re: zheap: a new storage format for PostgreSQL

On Sat, Nov 17, 2018 at 8:51 AM Adam Brusselback wrote: > > I don't know how much what I write on this thread is read by others or > how useful this is for others who are following this work > > I've been following this thread and many others like it, silently soaking > it up, because I don't

Re: zheap: a new storage format for PostgreSQL

Adam Brusselback schrieb am Sa., 17. Nov. 2018 um 06:51 Uhr: > > I don't know how much what I write on this thread is read by others or > how useful this is for others who are following this work > > I've been following this thread and many others like it, silently soaking > it up, because I

Re: zheap: a new storage format for PostgreSQL

On Sat, Nov 17, 2018 at 11:21 AM Adam Brusselback wrote: > > > I don't know how much what I write on this thread is read by others or > how useful this is for others who are following this work > > I've been following this thread and many others like it, silently soaking it > up, because I

Re: zheap: a new storage format for PostgreSQL

> I don't know how much what I write on this thread is read by others or how useful this is for others who are following this work I've been following this thread and many others like it, silently soaking it up, because I don't feel like i'd have anything useful to add in most cases. It is very

Re: zheap: a new storage format for PostgreSQL

On Thu, Nov 1, 2018 at 12:13 PM Amit Kapila wrote: > > > Now, we have a working solution for this problem. The extended > transaction slots are stored in TPD pages (those contains only > transaction slot arrays) which are interleaved with regular pages. > For a detailed idea, you can see atop

Re: zheap: a new storage format for PostgreSQL

On Sun, Nov 11, 2018 at 11:55 PM Kuntal Ghosh wrote: > > On Sat, Nov 10, 2018 at 8:51 PM Daniel Westermann > wrote: > > > > >>Thanks. Initializing the variable seems like the right fix here. > > > > ... just had a warning when recompiling from the latest sources on CentOS 7: > > > > labels

Re: zheap: a new storage format for PostgreSQL

On Sat, Nov 10, 2018 at 8:51 PM Daniel Westermann wrote: > > >>Thanks. Initializing the variable seems like the right fix here. > > ... just had a warning when recompiling from the latest sources on CentOS 7: > > labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing > -fwrapv

Re: zheap: a new storage format for PostgreSQL

>>Thanks. Initializing the variable seems like the right fix here. ... just had a warning when recompiling from the latest sources on CentOS 7: labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I../../../../src/include

Re: zheap: a new storage format for PostgreSQL

On 11/5/18 4:00 AM, Amit Kapila wrote: > On Sat, Nov 3, 2018 at 9:30 AM Amit Kapila wrote: >> On Fri, Nov 2, 2018 at 6:41 PM Tomas Vondra >> wrote: >>> I'm sure >>> it's not the only place where we do something like this, and the other >>> places don't trigger the valgrind warning, so how do

Re: zheap: a new storage format for PostgreSQL

On Sat, Nov 3, 2018 at 9:30 AM Amit Kapila wrote: > On Fri, Nov 2, 2018 at 6:41 PM Tomas Vondra > wrote: > > I'm sure > > it's not the only place where we do something like this, and the other > > places don't trigger the valgrind warning, so how do those places do > > this? heapam seems to call

Re: zheap: a new storage format for PostgreSQL

On Fri, Nov 2, 2018 at 6:41 PM Tomas Vondra wrote: > > On 11/02/2018 12:12 PM, Amit Kapila wrote: > > On Thu, Nov 1, 2018 at 7:26 PM Tomas Vondra > > wrote: > >> > >> On 11/01/2018 07:43 AM, Amit Kapila wrote: > >>> > >>> You can find the latest code at https://github.com/EnterpriseDB/zheap >

Re: zheap: a new storage format for PostgreSQL

On 11/02/2018 12:12 PM, Amit Kapila wrote: > On Thu, Nov 1, 2018 at 7:26 PM Tomas Vondra > wrote: >> >> On 11/01/2018 07:43 AM, Amit Kapila wrote: >>> >>> You can find the latest code at https://github.com/EnterpriseDB/zheap >>> >> >> Seems valgrind complains about a couple of places in the

Re: zheap: a new storage format for PostgreSQL

On Thu, Nov 1, 2018 at 7:26 PM Tomas Vondra wrote: > > On 11/01/2018 07:43 AM, Amit Kapila wrote: > > > > You can find the latest code at https://github.com/EnterpriseDB/zheap > > > > Seems valgrind complains about a couple of places in the code - nothing > major, might be noise, but probably

Re: zheap: a new storage format for PostgreSQL

On 11/01/2018 07:43 AM, Amit Kapila wrote: You can find the latest code at https://github.com/EnterpriseDB/zheap Seems valgrind complains about a couple of places in the code - nothing major, might be noise, but probably worth a look. regards -- Tomas Vondra

Re: zheap: a new storage format for PostgreSQL

On Sat, May 26, 2018 at 6:33 PM Amit Kapila wrote: > On Fri, Mar 2, 2018 at 4:05 PM, Alexander Korotkov > wrote: > > It's been a while since we have updated the progress on this project, > so here is an update. > Yet, another update. > This is based on the features that were not > working (as

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 4:05 PM, Alexander Korotkov wrote: > On Fri, Mar 2, 2018 at 1:31 PM, Amit Kapila wrote: >> >> On Fri, Mar 2, 2018 at 1:50 PM, Tsunakawa, Takayuki >> wrote: >> > From: Amit Kapila

Re: zheap: a new storage format for PostgreSQL

On 03/03/18 05:03, Robert Haas wrote: On Fri, Mar 2, 2018 at 5:35 AM, Alexander Korotkov wrote: I would propose "zero-bloat heap" disambiguation of zheap. Seems like fair enough explanation for me without need to rename :) It will be possible to bloat a zheap

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 7:06 PM, Aleksander Alekseev wrote: > Hello Amit, > >> Sometime back Robert has proposed a solution to reduce the bloat in >> PostgreSQL [1] which has some other advantages of its own as well. To >> recap, in the existing heap, we always create a

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 5:35 AM, Alexander Korotkov wrote: > I would propose "zero-bloat heap" disambiguation of zheap. Seems like fair > enough explanation for me without need to rename :) It will be possible to bloat a zheap table in certain usage patterns. For

Re: zheap: a new storage format for PostgreSQL

Hello Amit, > Sometime back Robert has proposed a solution to reduce the bloat in > PostgreSQL [1] which has some other advantages of its own as well. To > recap, in the existing heap, we always create a new version of a tuple on > an update which must eventually be removed by periodic vacuuming

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 11:35 PM, Alexander Korotkov wrote: > On Fri, Mar 2, 2018 at 1:31 PM, Amit Kapila wrote: >> On Fri, Mar 2, 2018 at 1:50 PM, Tsunakawa, Takayuki >> wrote: >> > Wonderful! BTW, what "z"

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 1:31 PM, Amit Kapila wrote: > On Fri, Mar 2, 2018 at 1:50 PM, Tsunakawa, Takayuki > wrote: > > From: Amit Kapila [mailto:amit.kapil...@gmail.com] > >> At EnterpriseDB, we (me and some of my colleagues) are working

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 1:50 PM, Tsunakawa, Takayuki wrote: > From: Amit Kapila [mailto:amit.kapil...@gmail.com] >> At EnterpriseDB, we (me and some of my colleagues) are working from more >> than a year on the new storage format in which only the latest version of

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 2:42 AM, Alexander Korotkov wrote: > > I think results representation should be improved. You show total size of > the database, but it's hard to understand how bloat degree was really > decreased, assuming that there are both update and

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 1:35 PM, Fabien COELHO wrote: > > Hello Amit, > >> At EnterpriseDB, we (me and some of my colleagues) are working from more >> than a year on the new storage format in which only the latest version of >> the data is kept in main storage and the old

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 9:29 AM, Mark Kirkwood wrote: > On 02/03/18 16:53, Alvaro Herrera wrote: > >> I think it was impolite to post this on the very same day the commitfest >> started. We have enough patches as it is ... >> > > To be fair - he did say things like

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 9:23 AM, Alvaro Herrera wrote: > I think it was impolite to post this on the very same day the commitfest > started. We have enough patches as it is ... > I can understand your concern, but honestly, I have no intention to hinder the current

RE: zheap: a new storage format for PostgreSQL

From: Amit Kapila [mailto:amit.kapil...@gmail.com] > At EnterpriseDB, we (me and some of my colleagues) are working from more > than a year on the new storage format in which only the latest version of > the data is kept in main storage and the old versions are moved to an undo > log. We call

Re: zheap: a new storage format for PostgreSQL

Hello Amit, At EnterpriseDB, we (me and some of my colleagues) are working from more than a year on the new storage format in which only the latest version of the data is kept in main storage and the old versions are moved to an undo log. [...] This looks more than great! *We’ve shown the

Re: zheap: a new storage format for PostgreSQL

On 02/03/18 16:53, Alvaro Herrera wrote: I think it was impolite to post this on the very same day the commitfest started. We have enough patches as it is ... To be fair - he did say things like "wanting feedback..." and "shows an example of using pluggable storage.." and for PG 12. If he

Re: zheap: a new storage format for PostgreSQL

I think it was impolite to post this on the very same day the commitfest started. We have enough patches as it is ... -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: zheap: a new storage format for PostgreSQL

On Fri, Mar 2, 2018 at 2:42 AM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Thu, Mar 1, 2018 at 5:09 PM, Amit Kapila > wrote: > >> Preliminary performance results >> --- >> >> *We’ve shown the performance

Re: zheap: a new storage format for PostgreSQL

On Thu, Mar 1, 2018 at 5:09 PM, Amit Kapila wrote: > Preliminary performance results > --- > > *We’ve shown the performance improvement of zheap over heap in a few > different pgbench scenarios. All of these tests were run with

Re: zheap: a new storage format for PostgreSQL

On Thu, Mar 1, 2018 at 9:00 PM, Satyanarayana Narlapuram wrote: > >>> Cons > >>> --- >>> 1. Deletes can be somewhat expensive. >>> 2. Transaction aborts will be expensive. >>> 3. Updates that update most of the indexed columns can be somewhat >>>

Re: zheap: a new storage format for PostgreSQL

On 01.03.2018 16:30, Satyanarayana Narlapuram wrote: Given transaction aborts are expensive, is there any impact on the crash recovery? In InnoDB/XtraDB, which has used the "move old row versions to UNDO log" since the very beginning, rollbacks are indeed costly, and especially so on

Re: zheap: a new storage format for PostgreSQL

recovery? Did you perform any tests on the recovery duration? Thanks, Satya From: Amit Kapila <amit.kapil...@gmail.com> Sent: Thursday, March 1, 2018 7:05:12 AM To: PostgreSQL Hackers Subject: Re: zheap: a new storage format for PostgreSQL On Thu,

Re: zheap: a new storage format for PostgreSQL

On Thu, Mar 1, 2018 at 7:39 PM, Amit Kapila wrote: > > Preliminary performance results > --- > I have not used plain text mode in my previous email due to which performance results might not be clear in some email clients, so