Potential BRIN Index Corruption
Hi All
We have a table with 623 million records. It appears a BRIN index of this
table on a timestamp column is missing some records, as illustrated below
in a cut-down version with additional columns and indices omitted.
We cannot work out a reproducible case but we have a copy of the offending
database. I was hoping to know
1. if anyone else has experienced similar issues
2. if anyone can shed some light on what to collect in order to fire a
useful bug report
Version
- centos-release-7-7.1908.0.el7.centos.x86_64
- PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-39), 64-bit
Table DDL
CREATE TABLE large_table_with_623m_records (
date_with_btree_index date,
ts_with_brin_index timestamp without time zone not null
);
CREATE INDEX date_bree_index ON large_table_with_623m_records
USING btree (date_with_btree_index COLLATE pg_catalog."default"
, date_with_btree_index);
CREATE INDEX ts_brin_index ON large_table_with_623m_records
USING brin (ts_with_brin_index);
Query
SELECT
*
FROM large_table_with_623m_records
WHERE
ts_with_brin_index >= '2018-06-29 12:12:50' AND ts_with_brin_index <
'2018-06-29 12:13:00'
AND date_with_btree_index = '2013-05-21'
This query uses Index Scan on date_bree_index and correctly returns 1
record that has ts_with_brin_index = '2018-06-29 12:12:58:081'.
If I remove the last line (AND date_with_btree_index = '2013-05-21'), the
query uses Bitmap Index Scan on ts_brin_index and incorrectly returns 0
record.
After a reindex of ts_brin_index, both variations of the query correctly
return 1 record.
Thanks
Huan
On 2020-Nov-26, Huan Ruan wrote:
Hi All
We cannot work out a reproducible case but we have a copy of the offending
database. I was hoping to know
I think the way to debug this would be to see what WAL records have been
emitted for the index, using pageinspect to find the problem index
tuple.
Use 'select ctid rrom large_table_with_623m_records where ...' to
pinpoint the unindexed tuple's page number; see when (in LSN) was that
tuple written; inspect WAL surroundings looking for updates (or lack
thereof) for the BRIN index. Use pageinspect to examine raw brin data.
Show quoted text
Thanks Alvaro for pointing me to pageinspect. I will give it a try and
report back. It might take a few days. I knew this module but have never
tried it before.
HI Alvaro
Unfortunately those records were written a while ago and we no longer keep
their WAL logs. Thanks for your help anyway.
Huan
On Fri, 27 Nov 2020 at 08:40, Huan Ruan <leohuanruan@gmail.com> wrote:
Show quoted text
Thanks Alvaro for pointing me to pageinspect. I will give it a try and
report back. It might take a few days. I knew this module but have never
tried it before.
On 12/9/20 12:07 AM, Huan Ruan wrote:
HI Alvaro
Unfortunately those records were written a while ago and we no longer keep
their WAL logs. Thanks for your help anyway.
Can you estimate when roughly the records were written? E.g. by using a
rough estimate of WAL or XIDs generated per day, or something like that.
Maybe there was some I/O issue in that time period and a couple writes
got lost, or something like that.
Are there any other corrupted indexes on the table?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi Tomas
The records have a timestamp column so we do know the time they were
written. We didn't find any I/O issues that match that time but
unfortunately as it's been a while we are not confident with that finding.
Are there any other corrupted indexes on the table?
That was one of my first questions too. I don't see any physical errors in
pg log so not sure if there are other corruptions. One thing we consider
doing is to turn on checksums.
Regards
Huan