Re: Invalid indexes should not consume update overhead

On 2016-07-17 23:06, Rader, David wrote:
>> That seems pretty pointless. Why not actually drop the index, then?
>>
>> The only reason I can think of is that there is value in representing
>> that indexes should continue to have optimizer statistics (that would
>> happen for expression indexes in Postgres) without actually paying for
>> the ongoing maintenance of the index during write statements. Even
>> that seems like kind of a stretch, though.
>
> There's some DBA benefit in that the index disable also disables
> constraints and foreign keys that depend on the index. instead of
> having to drop and recreate dependent objects you can leave all the
> definitions in place but disabled. So it makes laziness easier.
>
> Of course then you have to be sure that your data is right when you bulk
> load since the engine is not enforcing it.

To make it clear - I don't postulate disabling indexes used for data
integrity - when the index can't be dropped, as for example some foreign
key depends on it, or table primary key is based on it, then it should
not be possible to turn it off.

Also I don't postulate turning them back on without doing a full reindex
- I just need to do this reindex for multiple indexes in parallel.

What I'd like to have isn't really different than just dropping the
indexes and recreating them back after bulk update. It's just that this
operation is not very safe:
- you have to save them somewhere else - using for example pg_dump,
- pg_dump is often not available or is in wrong version,
- when saving these indexes in some temporary directory you risk loosing
them in case of a failure or crash,
- provided that you're trying to code some application upgrade script,
it's hard to tell what to do when previous execution crashed - when you
save indexes again you risk overwriting your save with empty or
incomplete data; when you don't, then you can't be sure if it was from
some previous execution and there were some schema changes since.

It's just it's now hard to prepare this for support team in sufficiently
reliable way, so that they can do this bulk update on their own.

And believe me - this trick works when you have a large table with large
number (like tens) of indexes. Even more so if these indexes are of
unicode text data.

--
Regards,
Tomasz "Tometzky" Ostrowski