Postgresql automatic reindex identified as cause I think?

Database load spiked and this has dramatically increased error rates and degraded times on responses. Automatic re-indexing was enabled over the weekend. A re-index of the routes table changed the statistics on the route table resulting in much less efficient queries to the table. A manual re-analyze of the table fixed the issue. The automatic re-indexing has been disabled.

I am more interested in what is automatic reindex, why enable it, how it broke things, and so on. Can someone please enlighten me?

The precise root cause was Postgres statistics on a given table were completely off. This caused, as you well pointed out, that a frequent query that normally takes 1-2ms started timing out (timeout is 15s). This caused an error spike and slow down across most of the read-only replicas.

There's correlation with the reindex operation, which affected the index that provided the necessary speedup on the operation, and the incident.

The reindex creates a concurrent index on the table and drops the old index afterwards. How it precisely affected the statistics is not determined yet.

There's more information on the incident issue. FYI: https://gitlab.com/gitlab-com/gl-infra/production/-/issues/2...

Update: the cause seems to be determined by the fact that the index was a functional index. In this case, table statistics are not applicable and the index requires its own statistics, which can only be generated via ANALYZE (manual or via autovacuum, ofc).

Disclaimer: I work at OnGres, and we help GitLab with PostgreSQL Support.

tangentially related, but have you ever had to deal with statistics on a table indexed on (x, date), where there are a few million rows added each day, and x could be [0,1000], with a distribution like 80% of rows owing to only a few values of x.

ran into a situation like this where after enough days of data had accumulated, postgres would eventually fall behind on updating stats, such that a week could lapse without stats being updated, causing the query planner to think no rows existed within that time range. This would result in a nested loop instead of a more efficient hash join, causing a query to take 2 hours instead of 2 seconds.

increasing the number of rows sampled with set statistics didn't seem to help. wound up running a cronjob to inspect pg_stats, and manually running analyze when enough days had lapsed without most_common_vals being updated.