Implement a way to build an index without blocking concurrent data modifications

[hvlad]

Currently, when index is build, any modifications of table data is not allowed. This is required to create correct index not missing new keys inserted during the build.

The time when such read lock is required could be significantly shortened.

[aafemt]

Time can be shortened up to zero, IMHO. Consider this:

1. Index get state "active but unusable".
2. DML operations work with it as usual but selects ignore it.
3. Background table scan is running to add any missing node.

[hvlad]

This way creates less dense b-tree and could be much slower than our fast_load().

Currently I thinking on combined approach:
at 1st stage engine build "main" b-tree using table snapshot and fast_load(), user attachments maintains separate ("small") b-tree with usual DML activity;
at 2nd stage "main" b-tree is "published" as index b-tree and maintained by user attachments as usual, engine merges "small" b-tree into "main" b-tree;
after merge finishes, index is allowed to use in SELECT's.

Not sure how to handle deletion of index keys on 1st stage.

[aafemt]

This way creates less dense b-tree and could be much slower than our fast_load().

Yes, this is the price for uninterrupted DB operations which may be acceptable.

Not sure how to handle deletion of index keys on 1st stage.

1st stage is running in snapshot mode so garbage collection is blocked and node deletions shouldn't occur at all, no?

[hvlad]

I have first working version and want to agree some user-visible details.

1. Syntax.

• create index:
  CREATE [...] INDEX <name> ON ... [CONCURRENTLY]

• rebuild existing index:
  ALTER INDEX <name> ACTIVE [CONCURRENTLY]

[hvlad]

2. Uniqueness handling

I see no good way to correctly check uniqueness while index build is in progress. Therefore uniqueness check runs after index is fully created.
Also, I consider as bad idea to throw away good working index right after hard build work just because it contains some duplicates that could be fixed later.

Therefore I offer to mark such unique index as not validated (but still unique) and implement a way to fix this later.
Of course not requiring read-only lock for a table.
Below is description of how it looks from user POV:

• right after CREATE UNIQUE INDEX ... CONCURRENTLY statement run, index metadata is marked as UNIQUE NOT VALIDATED
• while index is built, check for duplicate keys not performed
• while index is built, concurrent user's INSERT and UPDATE statements do check for duplicates, but only on limited set of keys (keys that was inserted since index build started)
• after completion of index build, engine perform uniqueness validation check
• if no duplicates found, index metadata is marked as UNIQUE (no special mark for VALIDATED state is required)
• if duplicates found, it reports as warnings (not errors!) and index not removed, index metadata remains in UNIQUE NOT VALIDATED state
• not validated index still doesn't allow to enter new duplicates
• index could be validated at any time later using new statement ALTER INDEX <name> VALIDATE UNIQUE
• ALTER INDEX <name> VALIDATE UNIQUE run uniqueness validation check and mark index as UNIQUE or throws usual isc_unique_key_violation error

[aafemt]

1. Syntax.

If you don't mind I would prefer ONLINE just for Oracle interoperability.

Also, I consider as bad idea to throw away good working index right after hard build work just because it contains some duplicates that could be fixed later.

Currently building of an unique index is aborted on duplicates. I see no reason for concurrent building to behave differently. I.e. if a DML creates violation during build - DML is aborted with an error. If index build finds a violation - the building is aborted with an error. Simple and robust.

[mrotteveel]

If you don't mind I would prefer ONLINE just for Oracle interoperability.

Personally, I'd prefer the PostgreSQL syntax, which also uses concurrently, but in a different position.

[hvlad]

In my opinion all secondary optional parameters should be placed at the end or, at least, after the name.
Also, for me
CREATE INDEX name ON table(columns) CONCURRENTLY
looks more natural than
CREATE INDEX CONCURRENTLY name ON table(columns)
I.e. anything between INDEX and name looks weird.

[AlexPeshkoff]

On 7/4/26 19:50, Vlad Khorsun wrote: *hvlad* left a comment (FirebirdSQL/firebird#7967) <#7967 (comment)> In my opinion all secondary optional parameters should be placed at the end or, at least, after the |name|. Also, for me |CREATE INDEX name ON table(columns) CONCURRENTLY | looks more natural than |CREATE INDEX CONCURRENTLY name ON table(columns) | I.e. anything between |INDEX| and |name| looks weird.

Also placing new option in the end helps avoid backward incompatibility if some index was called CONCURRENTLY. What about

If you don't mind I would prefer |ONLINE| just for Oracle interoperability.

I see nothing bad if we support both.

[mrotteveel]

> In my opinion all secondary optional parameters should be placed at > the end or, at least, after the |name|. > Also, for me > |CREATE INDEX name ON table(columns) CONCURRENTLY | > looks more natural than > |CREATE INDEX CONCURRENTLY name ON table(columns) | > I.e. anything between |INDEX| and |name| looks weird. > Also placing new option in the end helps avoid backward incompatibility if some index was called CONCURRENTLY.

OK, I retract my proposal to use the PostgreSQL syntax.

What about > > If you don't mind I would prefer |ONLINE| just for Oracle > interoperability. > I see nothing bad if we support both.

It unnecessarily adds another keyword and a choice in the syntax, either we keep CONCURRENTLY, or we use ONLINE, not both, please.

[dyemanov]

I think the initially suggested CREATE [...] INDEX <name> ON ... [CONCURRENTLY] looks like the best option. While we could support both, this looks like unnecessary confusion to me.

[AlexPeshkoff]

On 7/5/26 10:24, Dmitry Yemanov wrote: *dyemanov* left a comment (FirebirdSQL/firebird#7967) <#7967 (comment)> I think the initially suggested |CREATE [...] INDEX <name> ON ... [CONCURRENTLY]| looks like the best option. While we could support both, this looks like unnecessary confusion to me.

OK, I do not insist.