I had replied by email to this but it bounced:
<issues@roundup-tracker.org>: host bugs.python.org[167.71.181.142] said: 454
    4.7.1 <issues@roundup-tracker.org>: Relay access denied (in reply to RCPT
    TO command)

Well here it goes:

On Mon, Feb 19, 2024 at 11:59:34PM +0000, John Rouillard wrote:
>
> Should this be a tri-value:
>
>   True - add an index
>   Unique - add an index with a unique constraint (issue 2551113)
>   False - default don't add an index on the column
>
> How should this look at the calling level? Strings "true", "unique"
> "false" isn't clean.  It allows misspelling compared to boolean
> True/False values enforced by the interpreter.  Maybe some type of
> enumeration (maybe python 3 only??) the interpreter can catch?

How about 'Yes, 'Unique', 'No'?
looks cleaner than two booleans plus 'Unique'?

Hmm, if we're thinking about index creation: How about indeces over
multiple columns? Maybe make this an attribute of a class not of a
property?

I'm usually putting these into initial_data.py in the top-level
directory of a tracker, something along the lines of

if hasattr(db, 'sql'):
    db.sql('create index _it_issue_status_idx on _it_issue (_status);')

But I *do* have more complicated ones like

    db.sql \
        ('create index _measurement_date_idx_ '
         'on _measurement using btree '
         '( _sensor'
         ', __retired__'
         ', (_measurement._date is not NULL) desc'
         ', _date desc'
         ', id'
         ');'
        )

I'm not sure we can generically formulate the latter one...

We might document index creation in sql as a permitted use-case...

Sometimes when tracking down a performance problem I'm experimenting
with indeces and most often document the result in initial_data.py but
there it is almost never really tested... because I'm dynamically
creating the index on an installed tracker.

Ralf