Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 30, 2025 at 4:39 PM Japin Li wrote: > > On Wed, 30 Jul 2025 at 14:49, Peter Smith wrote: > > On Fri, Jul 25, 2025 at 1:58 PM Japin Li wrote: > >> > >> On Wed, 23 Jul 2025 at 14:07, Peter Smith wrote: > >> > On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: > >> > ... > > ... > >> >>

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, Aug 01, 2025 at 05:18:11PM +1000, Peter Smith wrote: > On Wed, Jul 30, 2025 at 9:07 PM Japin Li wrote: > > > ... > > 3. > > I've also found that the VCI index is not working. Is this the expected > > behavior? > > > > [local]:3209161 postgres=# \d+ t > >

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 30, 2025 at 9:07 PM Japin Li wrote: > ... > 3. > I've also found that the VCI index is not working. Is this the expected > behavior? > > [local]:3209161 postgres=# \d+ t > Table "public.t" > Column | Type | Collation | Nullable | Default

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Thu, Jul 31, 2025 at 6:20 PM Japin Li wrote: > ... > > 1. > > I've identified another TRAP failure. Here are the reproduction steps: > > > > rm -rf demo > > initdb -D demo > > cat <>demo/postgresql.auto.conf > > shared_preload_libraries = 'vci' > > max_worker_processes = '20' > > EOF > > > > pg

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, 30 Jul 2025 at 19:07, Japin Li wrote: > On Tue, 29 Jul 2025 at 06:57, Peter Smith wrote: >> Here are the latest v15 patches. >> >> Changes include: >> >> PATCH 0002. >> - README now says user should not tamper with VCI internal relations >> - fixes/test the VACUUM bug -- fix provided by J

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 30, 2025 at 9:07 PM Japin Li wrote: > ... > 2. > +Internal Relation Types: > +- -1: TID relation (maps CRID to original TID) > +- -2: NULL vector (bit array for NULL values) > +- -3: Delete vector (bit array for deleted records) > +- -5: TID-CRID mapping table > +- -9: Data WOS (b

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, 29 Jul 2025 at 06:57, Peter Smith wrote: > Here are the latest v15 patches. > > Changes include: > > PATCH 0002. > - README now says user should not tamper with VCI internal relations > - fixes/test the VACUUM bug -- fix provided by Japin [1] > - fixes/tests the reported segv for attempted

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, 30 Jul 2025 at 14:49, Peter Smith wrote: > On Fri, Jul 25, 2025 at 1:58 PM Japin Li wrote: >> >> On Wed, 23 Jul 2025 at 14:07, Peter Smith wrote: >> > On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: >> > ... > ... >> >> >> >> Or is it by design that users are unable to read the internal

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, Jul 25, 2025 at 1:58 PM Japin Li wrote: > > On Wed, 23 Jul 2025 at 14:07, Peter Smith wrote: > > On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: > > ... ... > >> > >> Or is it by design that users are unable to read the internal relations? > >> > > > > IIUC, those VCI internal relations

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 23, 2025 at 2:12 PM Peter Smith wrote: > > On Wed, Jul 23, 2025 at 1:58 PM Japin Li wrote: > > > > After some investigation, I found that the VACUUM assertion failures were > > caused by an uninitialized HeapTupleFreeze structure. PFA. > > > > Hey, great! Thanks very much for your fi

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: > ... > > 1. > I encountered another crash while checking VCI's internal relations. > > rm -rf demo > initdb -D demo > cat shared_preload_libraries = 'vci' > max_worker_processes = '20' > EOF > > pg_ctl -D demo start > >

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, 23 Jul 2025 at 14:07, Peter Smith wrote: > On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: > ... >> 1. >> I encountered another crash while checking VCI's internal relations. >> >> rm -rf demo >> initdb -D demo >> cat > shared_preload_libraries = 'vci' >> max_w

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 23, 2025 at 1:58 PM Japin Li wrote: > > > Hi, Peter > > On Tue, 22 Jul 2025 at 17:46, Peter Smith wrote: > > Hi. > > > > Here are the latest v14 patches. > > > > Changes include: > > > > PATCH 0002. > > - Fixes the enable_seqscan PANIC bug reported by Japin [1] > > - Adds a new regres

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: ... > 1. > I encountered another crash while checking VCI's internal relations. > > rm -rf demo > initdb -D demo > cat shared_preload_libraries = 'vci' > max_worker_processes = '20' > EOF > > pg_ctl -D demo start > > cat

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi, Peter On Tue, 22 Jul 2025 at 17:46, Peter Smith wrote: > Hi. > > Here are the latest v14 patches. > > Changes include: > > PATCH 0002. > - Fixes the enable_seqscan PANIC bug reported by Japin [1] > - Adds a new regression test case for this > > == > [1] > https://www.postgresql.org/mess

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, 22 Jul 2025 at 17:46, Peter Smith wrote: > Hi. > > Here are the latest v14 patches. > > Changes include: > > PATCH 0002. > - Fixes the enable_seqscan PANIC bug reported by Japin [1] > - Adds a new regression test case for this > > == > [1] > https://www.postgresql.org/message-id/ME0P3

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, Jul 11, 2025 at 7:18 PM Japin Li wrote: > ... > > Hi, Peter > > 1. > I'm curious if you encountered the following warning during compilation: > > /home/japin/Codes/pg/master/build/../contrib/vci/include/vci_ros.h:745:9: > warning: result of comparison of constant 65536 with expression of

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Mon, 14 Jul 2025 at 18:47, Peter Smith wrote: > Hi Japin, > > Thanks for your README questions. > > On Fri, Jul 11, 2025 at 7:18 PM Japin Li wrote: > ... >> >> 3. >> In the README, 'TID' seems to have conflicting definitions: >> Transaction ID (2.1) vs. tuple physical identifier (2.3.1). >> >>

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Mon, Jul 14, 2025 at 2:08 PM Peter Smith wrote: > > Hi Shveta, > > Thanks for your README questions. > > On Fri, Jul 11, 2025 at 1:46 PM shveta malik wrote: > > > > Thank You for working on this. I started going through the README and > > tried running simple tests, have few concerns: > > > >

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi Japin, Thanks for your README questions. On Fri, Jul 11, 2025 at 7:18 PM Japin Li wrote: ... > > 3. > In the README, 'TID' seems to have conflicting definitions: > Transaction ID (2.1) vs. tuple physical identifier (2.3.1). > > Could you confirm the intended meaning? Suggest using 'XID' for T

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi Shveta, Thanks for your README questions. On Fri, Jul 11, 2025 at 1:46 PM shveta malik wrote: > > Thank You for working on this. I started going through the README and > tried running simple tests, have few concerns: > > 1) > I am not able to understand section 4.2 'WOS-to-ROS conversion'. Wh

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, 11 Jul 2025 at 16:31, Peter Smith wrote: > Hi. Here is the latest patch set v12* > > Main differences are: > > Patch 0001 (core) > - removed SizeOfIptrData macro, as reported by Tomas [1] and Japin [2] > > Patch 0002 (vci module) > - Made fixes so the "ROS Control Worker" (for background W

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, Jul 11, 2025 at 1:46 PM shveta malik wrote: > > Thank You for working on this. I started going through the README and > tried running simple tests, have few concerns: > > 1) > I am not able to understand section 4.2 'WOS-to-ROS conversion'. When > whiteout-WOS says 'delete 4', what does th

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Thank You for working on this. I started going through the README and tried running simple tests, have few concerns: 1) I am not able to understand section 4.2 'WOS-to-ROS conversion'. When whiteout-WOS says 'delete 4', what does that mean? 4 is CRID, TXID? And when does delete-vector X represents

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Thu, 10 Jul 2025 at 17:51, Peter Smith wrote: > On Thu, Jul 10, 2025 at 4:07 PM Japin Li wrote: > ... >> I'm still trying to understand the patches. >> >> diff --git a/src/include/storage/itemptr.h b/src/include/storage/itemptr.h >> index 74b87a9..d97d1c5 100644 >> --- a/src/include/storage/it

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Thu, Jul 10, 2025 at 4:07 PM Japin Li wrote: ... > Thanks for updating the patch! > > I find another bug when using VCI index as following: > > 2025-07-10 12:17:15.087 CST [2027312] PANIC: unexpected index access method > call : "vci_beginscan" > 2025-07-10 12:17:15.087 CST [2027312] STATEMEN

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, 08 Jul 2025 at 10:11, Peter Smith wrote: > Hi. Here is the latest patch set. > > Main differences are: > > Patch 0001 (core) > - removed embedded vci code from autovacuum.c / postinit.c (now these > are same as master) > > Patch 0002 (vci module) > - a few changes to facilitate the fix to

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Sat, May 24, 2025 at 7:29 AM Tomas Vondra wrote: > > Hello Iwata-san, > ... > autovacuum.c > -- > - Why does this need a custom autovacuum launcher, or what's the purpose > of this code? is there a special autovacuum launcher for VCI indexes? > > - Again, explicit references to

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, Jul 4, 2025 at 4:03 PM Japin Li wrote: ... > When trying vic, I received the following error: > > TRAP: failed Assert("TransactionIdIsNormal(xmax)"), File: > "/data/Codes/pg/master/build/../src/backend/access/heap/heapam.c", Line: > 7373, PID: 1719347 > postgres: japin postgres [local]

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, 02 Jul 2025 at 16:34, Peter Smith wrote: > On Wed, Jul 2, 2025 at 10:22 AM Aya Iwata (Fujitsu) > wrote: >> > ... >> >> > reloptions.c >> > -- >> > - Changes that should be moved to the contrib module Why should in-core >> > reloptions know about this? See for example how "

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jul 2, 2025 at 10:22 AM Aya Iwata (Fujitsu) wrote: > ... > > > reloptions.c > > -- > > - Changes that should be moved to the contrib module Why should in-core > > reloptions know about this? See for example how "contrib/bloom" defined > > reloptions. > > Thanks. We are stud

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, Jul 1, 2025 at 1:17 PM Peter Smith wrote: > > On Sat, Jun 28, 2025 at 12:24 AM Timur Magomedov > wrote: > ... > > Thanks for updates. > > I was trying to move some code from Postgres core patch to contrib/vci. > > Started with moving VCI options from StdRdOptions struct and > > reloptions

RE: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi Tomas-san, > > - Divide the patch more and more > > - to allow committers to consider pros and cons and push one by one > > - 15 patches is the maximum amount > > - Separate features to some committable group > > > > Yes. Both parts need to be divided into small (but meaningful) pieces.

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Sat, Jun 28, 2025 at 12:24 AM Timur Magomedov wrote: ... > Thanks for updates. > I was trying to move some code from Postgres core patch to contrib/vci. > Started with moving VCI options from StdRdOptions struct and > reloptions.c, reloptions.h files into contrib/vci like Tomas suggested > earl

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, 2025-06-25 at 12:22 +1000, Peter Smith wrote: > Hi. > > Here are the latest v9* patches. These have following changes: > > 0001 > -  fixes some of the minor quirks reported by Tomas [1]. > > 0002 > - fixes to added address some of Timur's feedback/patches [2] > - test code updated to rem

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, 2025-06-25 at 13:13 +1000, Peter Smith wrote: > Hi Timur, Thanks for your feedback! Hello Peter! > Hmm. IIUC the “supported types” code was written this way for the > following reason. Out of an over-abundance (?) of caution, the > original patch authors wanted to call the function > 'vc

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, Jun 20, 2025 at 4:09 AM Timur Magomedov wrote: > > Hello Peter! > > Thank you for working on VCI updates. > Here are some proposals for small improvements: Hi Timur, Thanks for your feedback! The newly posted v9-0002 addresses some of your comments. Details are below. > > Since hothash

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Thu, 2025-06-19 at 14:14 +1000, Peter Smith wrote: > Here are the v8 patches. The main changes are as follows: > > v8-0001 VCI - changes to postgres core > - same > > v8-0002 VCI module - main > - extracted "compression" related code from this main patch > - applied Timur's top-up patch [1] re

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Tue, Jun 10, 2025 at 6:02 PM Peter Smith wrote: > > Hi Timur. > > On Wed, Jun 4, 2025 at 11:47 PM Timur Magomedov > wrote: > > > > Hello Peter, > > > > I've tried running VCI, the idea looks great to me. Thank you and > > Iwata-san for working on this feature. Looked at the source code of VCI

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Thu, May 29, 2025 at 11:30 PM Timur Magomedov wrote: > > Hi Peter, > I've noticed there are changes in Postgres code v4 patch that rollback > the commit [1]. That commit optimizes TupleHashEntryData struct size > and amount of memory allocations which improves performance (see > discussion [2])

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Sat, May 24, 2025 at 7:29 AM Tomas Vondra wrote: > ... > > What I really miss in the current patch is some sort of READMEs with > > - high-level design of the VCI indexes > > - description of the consistency/visibility model (does it behave the > same way as querying the heap, can it be out of

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jun 4, 2025 at 11:47 PM Timur Magomedov wrote: > > I've tried running VCI, the idea looks great to me. Thank you and > Iwata-san for working on this feature. Looked at the source code of VCI > module, the patch is huge but here are some ideas I hope could be > useful for community. Made so

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jun 4, 2025 at 5:19 PM Jim Nasby wrote: > What I can imagine is "VCI" as a "proxy" TAM on top of heap, keeping the >> columnar format in a separate fork. And using either that from custom >> scans, or the heap as a fallback for cases not supported by VCI. >> > > Yeah, there'd definitely n

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Wed, Jun 4, 2025 at 1:16 PM Tomas Vondra wrote: > On 6/4/25 19:59, Jim Nasby wrote: > > > > > > On Fri, May 23, 2025 at 4:29 PM Tomas Vondra > > wrote: > > > > Also, Alvaro seemed to think TAM is the way to go, and in order to > keep > > the OLTP performance he

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On Fri, May 23, 2025 at 4:29 PM Tomas Vondra wrote: > Also, Alvaro seemed to think TAM is the way to go, and in order to keep > the OLTP performance he suggested to use both heap and VCI at the same > time, in different "forks". I'm not sure how would that work, or if we > can already do that - A

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

On 6/4/25 19:59, Jim Nasby wrote: > > > On Fri, May 23, 2025 at 4:29 PM Tomas Vondra > wrote: > > Also, Alvaro seemed to think TAM is the way to go, and in order to keep > the OLTP performance he suggested to use both heap and VCI at the same > time, in diffe

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hello Peter, I've tried running VCI, the idea looks great to me. Thank you and Iwata-san for working on this feature. Looked at the source code of VCI module, the patch is huge but here are some ideas I hope could be useful for community. Made some patches on top of v4 that can be squashed into fu

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi Timur. On Thu, May 29, 2025 at 11:30 PM Timur Magomedov wrote: > > Hi Peter, > I've noticed there are changes in Postgres code v4 patch that rollback > the commit [1]. That commit optimizes TupleHashEntryData struct size > and amount of memory allocations which improves performance (see > disc

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi Peter, I've noticed there are changes in Postgres code v4 patch that rollback the commit [1]. That commit optimizes TupleHashEntryData struct size and amount of memory allocations which improves performance (see discussion [2]). Can we use leave TupleHashEntryData as is and make new VCI-specific

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hello Iwata-san, Thanks for presenting the patch at pgconf.dev last week, and for the discussions at the APFS session. As promised I did a quick review of the patch this week, trying to understand the design, the trade-offs etc. I'm still trying to understand the patch, so please correct me when

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi hackers, I will share the notes on the discussions in PGConf.dev. Thanks all for participation. Feedback on the Advanced Patch Feedback Session; - A Basic idea that allows both OLTP/OLAP workloads on the same table is OK - Buffering mechanism has already been used by existing code GIN, I

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

my opinion. I could be wrong. >> -Original Message- >> From: Yura Sokolov >> Sent: Wednesday, January 15, 2025 11:44 PM >> To: pgsql-hackers@lists.postgresql.org >> Subject: Re: [WIP]Vertical Clustered Index (columnar store extension) - take2 >> >>

RE: [WIP]Vertical Clustered Index (columnar store extension) - take2

ximum search performance. But I'm not very particular about this. Comments are welcome. Best regards, Aya Iwata FUJITSU LIMITED > -Original Message- > From: Yura Sokolov > Sent: Wednesday, January 15, 2025 11:44 PM > To: pgsql-hackers@lists.postgresql.org > Subject:

RE: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hi Alvaro san, I am sorry for my late reply. I continue to work on proposing VCI feature to the community. > I think this is definitely an important and welcome development. > I'm looking forward to patches in this area. Thank you! I am currently preparing to share VCI designs with PGConf.dev.

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

07.10.2024 17:53, Aya Iwata (Fujitsu) wrote: Hi All, Suggestions == When analyzing real-time data collected by PostgreSQL, it can be difficult to tune the current PostgreSQL server for satisfactory performance. Therefore, we propose Vertical Clustered Indexing (VCI), an in-memory

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Hello, I came across this email by chance while looking for something else. On 2024-Oct-07, Aya Iwata (Fujitsu) wrote: > Therefore, we propose Vertical Clustered Indexing (VCI), an in-memory > column store function that holds data in a state suitable for business > analysis and is also expected

[WIP]Vertical Clustered Index (columnar store extension) - take2

Hi All, Suggestions == When analyzing real-time data collected by PostgreSQL, it can be difficult to tune the current PostgreSQL server for satisfactory performance. Therefore, we propose Vertical Clustered Indexing (VCI), an in-memory column store function that holds data in a state su