A thought on Index Organized Tables
in [PgSql]
|
Prev: [COMMITTERS] pgsql: Oops, don't forget to rewind the directory before scanning it to
Next: Time travel on the buildfarm
From: Heikki Linnakangas on 22 Feb 2010 01:51 Gokulakannan Somasundaram wrote: > Hi, > As you all know, Index Organized tables are a way by which we can > automatically cluster the data based on the primary key. While i was > thinking about an implementation for postgres, it looks like an impossible > with the current ideologies. In an IOT, if a record gets updated, we need to > mark the old row as deleted immediately, as we do with any other table. But > since Postgres supports user defined data types and if they happen to be a > broken data type, then we have an unstable IOT.(as there is no guarantee, we > might hit the same record) > This was the reason for which, the proposal on creating indexes with > snapshot was rejected. > May i get a little clarification on this issue? Will we be supporting > the IOT feature in postgres in future? What seems like the best path to achieve the kind of performance benefits that IOTs offer is allowing index-only-scans using the visibility map. I worked on that last summer, but unfortunately didn't have the time to finish anything. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Gokulakannan Somasundaram on 22 Feb 2010 03:18 Heikki, I had a quick look at the discussion on visibility map design. The main differences as i see it are a) IOT has both table and index in one structure. So no duplication of data b) With visibility maps, we have three structures a) Table b) Index c) Visibility map. So the disk footprint of the same data will be higher in postgres ( 2x + size of the visibility map). c) More than that, inserts and updates will incur two extra random i/os every time. - one for updating the table and one for updating the visibility map. Are we going to ignore these differences? Thanks, Gokul. On Mon, Feb 22, 2010 at 12:21 PM, Heikki Linnakangas < heikki.linnakangas(a)enterprisedb.com> wrote: > Gokulakannan Somasundaram wrote: > > Hi, > > As you all know, Index Organized tables are a way by which we can > > automatically cluster the data based on the primary key. While i was > > thinking about an implementation for postgres, it looks like an > impossible > > with the current ideologies. In an IOT, if a record gets updated, we need > to > > mark the old row as deleted immediately, as we do with any other table. > But > > since Postgres supports user defined data types and if they happen to be > a > > broken data type, then we have an unstable IOT.(as there is no guarantee, > we > > might hit the same record) > > This was the reason for which, the proposal on creating indexes with > > snapshot was rejected. > > May i get a little clarification on this issue? Will we be supporting > > the IOT feature in postgres in future? > > What seems like the best path to achieve the kind of performance > benefits that IOTs offer is allowing index-only-scans using the > visibility map. I worked on that last summer, but unfortunately didn't > have the time to finish anything. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
From: Greg Stark on 22 Feb 2010 05:29 On Mon, Feb 22, 2010 at 8:18 AM, Gokulakannan Somasundaram <gokul007(a)gmail.com> wrote: > a) IOT has both table and index in one structure. So no duplication of data > b) With visibility maps, we have three structures a) Table b) Index c) > Visibility map. So the disk footprint of the same data will be higher in > postgres ( 2x + size of the visibility map). These sound like the same point to me. I don't think we're concerned with footprint -- only with how much of that footprint actually needs to be scanned. So if we have a solution allowing the scan to only need to look at the index then the extra footprint of the table doesn't cost anything at run-time. And the visibility map is very small. I think you would be better off looking for incremental improvements rather than major architectural changes like having no heap for a table. There are so many design decisions hinged on having a heap that it would be impractical to rethink them all. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Gokulakannan Somasundaram on 22 Feb 2010 06:01 Forgot to include the group... On Mon, Feb 22, 2010 at 4:31 PM, Gokulakannan Somasundaram < gokul007(a)gmail.com> wrote: > > >> These sound like the same point to me. I don't think we're concerned >> with footprint -- only with how much of that footprint actually needs >> to be scanned. So if we have a solution allowing the scan to only need >> to look at the index then the extra footprint of the table doesn't >> cost anything at run-time. And the visibility map is very small. >> >> > Yep.. They are one and the same... > Just wanted a clarification on the design goals going forward. > > Thanks, > Gokul. > >
From: Alvaro Herrera on 22 Feb 2010 09:38
Greg Stark escribi�: > On Mon, Feb 22, 2010 at 8:18 AM, Gokulakannan Somasundaram > <gokul007(a)gmail.com> wrote: > > a) IOT has both table and index in one structure. So no duplication of data > > b) With visibility maps, we have three structures a) Table b) Index c) > > Visibility map. So the disk footprint of the same data will be higher in > > postgres ( 2x + size of the visibility map). > > These sound like the same point to me. I don't think we're concerned > with footprint -- only with how much of that footprint actually needs > to be scanned. So if we have a solution allowing the scan to only need > to look at the index then the extra footprint of the table doesn't > cost anything at run-time. And the visibility map is very small. Moreover, the visibility map is already there. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |