BTREE indexes to be case sensitive and GIN are case insensitive

[wakibi]

By their nature, BTREE indexes are case sensitive and that should be included in our documentation. Removing the case sensitivity on BTREEs removes the performance gains they were used in the first place. Users need made be aware of this

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[k8hughes]

I think we still need this.

[k8hughes]

To be able to work on this we'd need to know the collumns that are most searched by for it to be efficent.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[wakibi]

There is a blocker here, some of the fields to be indexed are rather too long and the DB can not index them. Suggestions on how to handle this are much welcome @akmiller01 @dean-breed @bill-anderson

[bill-anderson]

You don't want BTREE indexes on titles and descriptions, do you? In my old money (of my particular old currency) we had cross-reference indexes for free text fields. Is this GiST in new money?

[wakibi]

That's right @bill-anderson. GiST and GIN are to be used for those extremely long text columns, but they too have a max string length (which is about 8000 characters) they can be applied to. Unfortunately, some of our data exceeds that length!

[bill-anderson]

So what I think we should do is move this into an ETL discussion to limit all free-text fields to, say, 5000 characters. I don't think that will affect the integrity of the data. But we should do a bit of research on the long culprits.

[dean-breed]

@wakibi Would it be possible to get a list of reporting organisations and columns which would be truncated?

[dean-breed]

@wakibi Can I get this list?

[edwinmp]

@dean-breed sorry Chris is on leave ... let me stick this on Slack where he can't miss it.

[wakibi]

@dean-breed Find attached. I have added all those with more than 7500 (my reasoning is those approaching that figure could easily soon hit the 8000 ceiling as well).
long_columns.csv

[dean-breed]

@wakibi - I could do something on this myself but to avoid mistake likelihood. Could you output the reporting org reference also?

[wakibi]

@wakibi - I could do something on this myself but to avoid mistake likelihood. Could you output the reporting org reference also?

long_columns.csv

Attached. I had forgotten to format the first one.

[dean-breed]

Hi Chris @wakibi, is it possible to output this list as part of the ETL process? I could then include the information in the data quality file?

[wakibi]

Hi @dean-breed, do you want it sent as an email attachment?

[dean-breed]

That would be great if it could. Or just stored in a repo somewhere. Either works @wakibi