Thread: Potential BRIN Index Corruption

Potential BRIN Index Corruption

From
Huan Ruan
Date:
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


Re: Potential BRIN Index Corruption

From
Alvaro Herrera
Date:
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.




Re: Potential BRIN Index Corruption

From
Huan Ruan
Date:
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.

Re: Potential BRIN Index Corruption

From
Huan Ruan
Date:
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:
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.

Re: Potential BRIN Index Corruption

From
Tomas Vondra
Date:
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



Re: Potential BRIN Index Corruption

From
Huan Ruan
Date:
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