{"id":3548,"date":"2023-09-04T19:31:20","date_gmt":"2023-09-04T18:31:20","guid":{"rendered":"https:\/\/kudzia.eu\/b\/?p=3548"},"modified":"2023-09-04T19:31:20","modified_gmt":"2023-09-04T18:31:20","slug":"bug-in-postgresqls-gin-index","status":"publish","type":"post","link":"https:\/\/kudzia.eu\/b\/2023\/09\/bug-in-postgresqls-gin-index\/","title":{"rendered":"bug in postgresql’s GIN index"},"content":{"rendered":"\n

it’s a story without happy end. i’m absolutely sure there’s an index-corruption bug in PostgreSQL 13; since i have not seen any mention of it being fixed – it’s likely in the following releases as well. i’ve tried to get attention to it<\/a> on the pgsql-bugs mailing list but i failed. likely because i’ve never found a way to reliably reproduce it. it happens on our production system, after few days or weeks full of intensive inserts and updates.<\/p>\n\n\n\n

we have a table with structure as below:<\/p>\n\n\n\n

CREATE TABLE public.entity (\r\n    entity_id bigint NOT NULL,\r\n    attribute_value_ids integer[] NOT NULL,\r\n    attribute_name_ids integer[] NOT NULL,\r\n[..]\r\n);\r\n\r\nALTER TABLE ONLY public.entity ADD CONSTRAINT entity_pkey PRIMARY KEY\r\n(entity_id);\r\nCREATE INDEX entity_attribute_name_ids_gin ON public.entity USING gin\r\n(attribute_name_ids public.gin__int_ops);\r\nCREATE INDEX entity_attribute_value_ids_gin ON public.entity USING gin\r\n(attribute_value_ids public.gin__int_ops);\r\n\r<\/code><\/pre>\n\n\n\n
such a table holds ~50M rows; we make few million of UPDATEs per day, all of them change values of columns covered with GIN indices. <\/p>\n\n\n\n
after a while query that uses GIN index starts returning rows that don’t meet the criteria:<\/p>\n\n\n\n
SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{4980}' )\r\n<\/code><\/pre>\n\n\n\n
would returns rows which DO NOT have 4980 in the attribute_name_ids array.<\/p>\n\n\n\n
more embarrassingly – query as below, which should never return any row, returns some:<\/p>\n\n\n\n
SELECT entity_id FROM entity WHERE ( attribute_name_ids && '{4980}' )\r\nAND NOT ( (attribute_name_ids||0) && '{4980}') ;\n<\/code><\/pre>\n\n\n\n
how do we live with this madness? every few hours we run below and pretend that problem isn’t that big. <\/p>\n\n\n\n
REINDEX INDEX CONCURRENTLY entity_attribute_value_ids_gin<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
within few months we’ll upgrade our servers to PostgreSQL 15, turn off the reindexing cron job and test heavily to see if we can spot similar behavior.<\/p>\n","protected":false},"excerpt":{"rendered":"
it’s a story without happy end. i’m absolutely sure there’s an index-corruption bug in PostgreSQL 13; since i have not seen any mention of it being fixed – it’s likely in the following releases as well. i’ve tried to get attention to it on the pgsql-bugs mailing list but i failed. likely because i’ve never […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[121,51],"tags":[],"class_list":["post-3548","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-unimportant"],"_links":{"self":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3548","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/comments?post=3548"}],"version-history":[{"count":1,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3548\/revisions"}],"predecessor-version":[{"id":3549,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3548\/revisions\/3549"}],"wp:attachment":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/media?parent=3548"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/categories?post=3548"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/tags?post=3548"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}