FWD » Core Development » Database

The legacy UNIQUE INDEX transforms into UNIQUE INDEX in SQL. Recently, we discovered that queries over UNIQUE INDEXes needs to be sorted by recid if they are equal on the indexed fields (this can be possible due to NULL values which are not considered equal per se). Because, of that, we were tempted to add a trailing recid to the UNIQUE INDEXes as well, but this way the uniqueness check was broken.

ATM, FWD trunk is emitting queries with trailing recid in the order by, but the UNIQUE INDEXes don't have such trailing recid. For PostgreSQL, the solution was to use INCLUDE syntax to include the recid and avoid paying the price of index look-ups.

This task suggests another approach of making the unique indexes non-unique, append recid at the end, but also add a UNIQUE CONSTRAINT over the indexed fields (except recid). The first steps are to do some performance analysis to understand whether the database engines are working fast even with UNIQUE CONSTRAINT. My initial guts are that UNIQUE INDEX es are more powerful in the planner than UNIQUE CONSTRAINT s, but hopefully the DB engines we support are smart enough to make use of the UNIQUE CONSTRAINT in their planner.

Incipient research shows that UNIQUE CONSTRAINT is mostly a sugar syntax for an UNIQUE INDEX. The point is that UNIQUE CONSTRAINT is ensured by creating an anonymous unique index under the hood that is able to check uniqueness fast. However, INSERT/UPDATE/DELETE statements are slower this way. It seems like having an UNIQUE CONSTRAINT or an UNIQUE INDEX is the same thing.

All-in-all, I think the write performance downgrade is too much to "duplicate" legacy unique indexes: one unique and one non-unique with trailing recid.

One small notice: In PostgreSQL, there is https://www.postgresql.org/docs/current/sql-set-constraints.html which allows deferring the constraint checking at the commit time. This basically is closer to the 4GL validation timing.