From 23287f08579f5760c56f41f609957bc2a73f7984 Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Thu, 25 Feb 2021 15:00:02 -0500 Subject: [PATCH] Lp 1703658: Repair DB Upgrade Remove an errant semicolon in the db upgrade script. Add release notes. Signed-off-by: Jason Stephenson --- .../upgrade/XXXX.data.dont_get_the_gist.sql | 2 +- .../Architecture/GIST_to_GIN_indexes_FTS.adoc | 43 +++++++++++++++++++ 2 files changed, 44 insertions(+), 1 deletion(-) create mode 100644 docs/RELEASE_NOTES_NEXT/Architecture/GIST_to_GIN_indexes_FTS.adoc diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql index cd97165622..eef84dc5fa 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql @@ -68,7 +68,7 @@ BEGIN 'metabib_series_field_entry_index_vector_idx', 'metabib_combined_series_field_entry_index_vector_idx', 'metabib_full_rec_index_vector_idx' - ); + ) LOOP -- Move existing index out of the way so there's no difference between new databases and upgraded databases EXECUTE FORMAT('ALTER INDEX %I.%I RENAME TO %I_gist', ind.sch, ind.idx, ind.idx); diff --git a/docs/RELEASE_NOTES_NEXT/Architecture/GIST_to_GIN_indexes_FTS.adoc b/docs/RELEASE_NOTES_NEXT/Architecture/GIST_to_GIN_indexes_FTS.adoc new file mode 100644 index 0000000000..9d82d460e7 --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/Architecture/GIST_to_GIN_indexes_FTS.adoc @@ -0,0 +1,43 @@ +Migration From GIST to GIN Indexes for Full Text Search +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Evergreen now uses GIN indexes for full text search in PostgreSQL. +GIN indexes offer better performance than GIST. For more information +on the differences in the two index types, please refer to the +https://www.postgresql.org/docs/current/textsearch-indexes.html[PostgreSQL +documentation]. + +An upgrade script is provided as part of this migration. If you +upgrade normally from a previous release of Evergreen, this upgrade +script should run as part of the upgrade process. The migration +script recommends that you run a `VACUUM ANALYZE` in Postgresql on the +tables that had the indexes changed. The migration process does not +do this for you, so you should do it as soon as is convenient after +the upgrade. + +Updating Your Own Indexes ++++++++++++++++++++++++++ + +If you have added your own full text indexes of type GIST, and you +wish to migrate them to GIN, you may do so. The following query, when +run in your Evergreen databsase after the migration from GIST to GIN, +will identify the remaining GIST indexes in your database: + +[source,sql] +---------------------------------------- +SELECT schemaname, indexname +FROM pg_indexes +WHERE indexdef ~* 'gist'; +---------------------------------------- + +If the above query produces output, you can run the next query to +output a SQL script to migrate the remaining indexes from GIST to GIN: + +[source,sql] +---------------------------------------- +SELECT 'DROP INDEX ' || schemaname || '.' || indexname || E';\n' || + REGEXP_REPLACE(indexdef, 'gist', 'gin', 'i') || E';\n' || + 'VACUUM ANAlYZE ' || schemaname || '.' || tablename || ';' +FROM pg_indexes +WHERE indexdef ~* 'gist'; +---------------------------------------- -- 2.43.2