Re: A thought on Index Organized Tables

Gokulakannan Somasundaram <gokul007(at)gmail(dot)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).
> 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.

I think IOT is a good match for overwrite storage systems, but postgres
is a non-overwrite storage systems. If we will update rows in IOT, we need
much more initial page free spaces than index-only scans where we can avoid
key updates with HOT.

Instead, how about excluding columns in primary keys from table data?
We cannot drop those primary keys and cannot seqscan the tables, but
there are no duplication of data, only small overheads (index tuple
headers and ctid links), and would work well with HOT and index-only
scans. If we don't have any non-key columns, that behaves just as IOT.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center