PostgreSQL indices missing in production

[ralf-berger]

There are indices missing on PostgreSQL tables in production (see https://github.com/ude-soco/CourseMapper-webserver/discussions/1219). I ran the following query in both environments to check for differences in user-created indices:

SELECT tablename, indexname
FROM pg_indexes
WHERE tablename NOT LIKE 'pg_%' AND indexname NOT LIKE '%_pkey'
ORDER BY tablename, indexname

Preview environment (edge)

disambiguations  disambiguations_title
disambiguations  unique_disambiguations
embeddings       embeddings_title
embeddings       unique_embeddings
page_categories  page_categories_category_name
page_categories  page_categories_page_title
page_categories  unique_page_categories
pages            pages_links
pages            pages_title
pages            unique_pages
redirects        redirects_title
redirects        unique_redirects

Production environment

disambiguations  disambiguations_title
disambiguations  unique_disambiguations
embeddings       embeddings_title
embeddings       unique_embeddings
pages            unique_pages
redirects        unique_redirects

[ralf-berger]

@jeanqussa Do you have any idea why some of the PostgreSQL table indices might be missing in production?

Could it be that the init container imports the dump but terminates the PostgreSQL instance before it is done actually indexing the data?

[jeanqussa]

That should not be the case. The indices are part of the dump. If the dump is the same and the image is the same, then the only thing different is the environment. Do containers in both environments have the same amount of storage/memory resources available?

[ralf-berger]

Do containers in both environments have the same amount of storage/memory resources available?

Yes.

[ralf-berger]

Is there a way to make sure index creation doesn't fail silently? Maybe utilizing the equivalent of

• pg_restore --schema-only && pg_restore --data-only,
• pg_restore --exit-on-error, or
• pg_restore --single-transaction (not sure about memory/storage requirements)?