Thread: How indexes are updated

To whom it may concern:

I think I have proven to myself that when a new record is added to a table it is added to the end of the table.  Also,
whena record in a table is updated it is also moved to the bottom of the table.   

Can someone tell me when the indexes on the columns in the table are updated.  Is the insertion of an index record
insertedbefore the new record is added to the bottom of the table?  Or is the new record added to the table  first and
thenthe databse figures out the insertion in the index?  Thanks in advance.  

Bob Powell

"Bob Powell" <Bob@hotchkiss.org> writes:
> I think I have proven to myself that when a new record is added to a table it is added to the end of the table.
Also,when a record in a table is updated it is also moved to the bottom of the table.   

Only if there's not any free space that could hold the record earlier in
the table.  (The free space has to not only exist, but be entered in the
FSM, though, so there are cases where there might be free space
available but the backend doesn't know about it.)

> Can someone tell me when the indexes on the columns in the table are
> updated.

Immediately after insertion of the record.

            regards, tom lane

> I think I have proven to myself that when a new record is added to
> a table it is added to the end of the table.  Also, when a record
> in a table is updated it is also moved to the bottom of the table.

Not necessarily. Other things like vacuums, other users in-progress
transactions/rollbacks, etc. will influence things as well.

For instance, say you are the only user of your database and you start
with a new table, insert 1000 records, delete the first 900 you
entered then vacuum the table (freeing the unused space for reuse but
not returning it to the filesystem).

Now if you insert a new record and "select * from yourtable" the new
record will probably be at the top (depending on record size, block
sizes, etc.).

Now while I would suspect this indicates that the record is physically
at the beginning of the database's on-disk file, the fact that it
appears first in the output is no proof of this because whenever a
behavior is not specified by the SQL specs or explicitly by the user
query it is left to the "whim" of the database. Without an "order by"
there is no guarantee what the output order will be nor whether the
behavior will remain constant from version to version.

There is not necessarily a 1:1 correlation between tables and indexes
and files on the disk. The DB designers can merge, split, scatter,
buffer, etc. the data onto disk(s) in whatever way gets the job done.
It's up to the user to explicitly request the order of attributes and
tuples where that is necessary.

> Can someone tell me when the indexes on the columns in the table
> are updated.  Is the insertion of an index record inserted before
> the new record is added to the bottom of the table?  Or is the new
> record added to the table  first and then the databse figures out
> the insertion in the index?  Thanks in advance.

I can't. But just like the above, it's up to the internal design of
the DB (and with the solution of the index-growth problem promised
for 7.4 the internal behavior of indexes will most likely change).

Cheers,
Steve