Marking indexes out of date (WAS: loading data, creating indexes, clustering, vacuum) feature request?

From: Glen Parker <glenebob(at)nwlink(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Marking indexes out of date (WAS: loading data, creating indexes, clustering, vacuum) feature request?
Date: 2006-12-08 20:49:30
Message-ID: 4579CFDA.1050102@nwlink.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Angva wrote:
> Looking for a small bit of advice...
>
> I have a script that updates several tables with large amounts of data.
> Before running the updates, it drops all indexes for optimal
> performance. When the updates have finished, I run the following
> procedure:
>
> recreate the indexes
> cluster the tables
> vacuum full analyze on the tables

Hi all,

I'd like to see a general way to take indexes off line without actually
losing their definitions. For example, something like "ALTER TABLE [EN
| DIS] ABLE INDEXES", "ALTER INDEX [EN | DIS] ABLE", etc. This could
also be used internally when a backend encounters an error
reading/writing an index. Rather than refusing to execute queries, it
could just ignore indexes it knows are disabled or bad in some way and
re-plan as needed.

This would have two benefits. First, the above scenerio would be much
simpler. Rather than dropping and re-creating new indexes, you could
just disable and then re-enable them without having any knowledge of
their structure. Secondly, it would allow us to put indexes in an
alternate table space on a non-redundant volume and, in the case of a
drive failure, be able to limp along, and get the system back to normal
simply by replacing the disk and issuing a REINDEX command.

I realize there are a couple gotchas with this. For example, what to do
with unique indexes? Perhaps a backend would still need to refuse to do
update/inserts on a table with degraded unique indexes, unless the index
was disabled explicitly? And then, refuse to rebuild/re-enable the
index as normal if non-unique values found?

Thx for considering :-)

-Glen Parker

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mikael Carneholm 2006-12-08 21:04:33 Re: Performance figures from DbMail list
Previous Message Glen Parker 2006-12-08 20:49:29 pg_dump: a way to not dump indexes and other objects, or a way to not create them on restore, feature request?