amcheck is a PostgreSQL extension that helps verify the integrity of database tables and indexes. It helps detect storage corruption, damaged index structures, and inconsistencies before they become problems. Database administrators can use it as part of maintenance to confirm that important database objects remain healthy.
In this article, we will install the amcheck extension, explore every function it provides, understand the purpose of each argument, and run examples on sample tables and indexes. By the end, you will know when to use each function and how to verify the integrity of B-tree indexes, GIN indexes, and heap tables in PostgreSQL.
Check the current version of postgres like this.
select version();
Result:
version
-------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg22.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)
Check that the extension can be installed in postgres like this.
select * from pg_available_extensions where name = 'amcheck';
Result:
name | default_version | installed_version | comment
---------+-----------------+-------------------+--------------------------------------------
amcheck | 1.5 | | functions for verifying relation integrity
(1 row)
Now create the extension.
create extension amcheck ;
Check the installed version of this extension like this.
select * from pg_available_extensions where name = 'amcheck';
Result:
name | default_version | installed_version | comment
---------+-----------------+-------------------+--------------------------------------------
amcheck | 1.5 | 1.5 | functions for verifying relation integrity
(1 row)
Check the functionalities of this extension by using the command below.
\dx+ amcheck
Result:
Objects in extension "amcheck"
Object description
---------------------------------------------------------------------
function bt_index_check(regclass)
function bt_index_check(regclass,boolean)
function bt_index_check(regclass,boolean,boolean)
function bt_index_parent_check(regclass)
function bt_index_parent_check(regclass,boolean)
function bt_index_parent_check(regclass,boolean,boolean)
function bt_index_parent_check(regclass,boolean,boolean,boolean)
function gin_index_check(regclass)
function verify_heapam(regclass,boolean,boolean,text,bigint,bigint)
(9 rows)
The amcheck extension gives four types of functions that help to check the health of Postgresql indexes and tables.
The bt_index_check function checks inside indexes and finds any corruption. The bt_index_parent_check function goes deeper; it also checks that the parent and child pages of a B-tree index are linked correctly. The gin_index_check function checks that GIN (Generalized Inverted Index) structures stay consistent. The verify_heapam function checks heap tables by validating each page and tuple for storage problems.
Let’s inspect each function's metadata like this.
Function definitions of each function from the amcheck extension
\df+ bt_index_check
Result:
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------
Schema | public
Name | bt_index_check
Result data type | void
Argument data types | index regclass
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_check
Description |
-[ RECORD 2 ]-------+------------------------------------------------------------
Schema | public
Name | bt_index_check
Result data type | void
Argument data types | index regclass, heapallindexed boolean
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_check
Description |
-[ RECORD 3 ]-------+------------------------------------------------------------
Schema | public
Name | bt_index_check
Result data type | void
Argument data types | index regclass, heapallindexed boolean, checkunique boolean
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_check
Description |
Now, we can see the same function here with three separate arguments.
\df+ bt_index_parent_check
Result:
List of functions
-[ RECORD 1 ]-------+---------------------------------------------------------------------------------
Schema | public
Name | bt_index_parent_check
Result data type | void
Argument data types | index regclass
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_parent_check
Description |
-[ RECORD 2 ]-------+---------------------------------------------------------------------------------
Schema | public
Name | bt_index_parent_check
Result data type | void
Argument data types | index regclass, heapallindexed boolean
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_parent_check
Description |
-[ RECORD 3 ]-------+---------------------------------------------------------------------------------
Schema | public
Name | bt_index_parent_check
Result data type | void
Argument data types | index regclass, heapallindexed boolean, rootdescend boolean
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_parent_check
Description |
-[ RECORD 4 ]-------+---------------------------------------------------------------------------------
Schema | public
Name | bt_index_parent_check
Result data type | void
Argument data types | index regclass, heapallindexed boolean, rootdescend boolean, checkunique boolean
Type | func
Volatility | volatile
Parallel | restricted
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | bt_index_parent_check
Description |
\df+ gin_index_check
Result:
List of functions
-[ RECORD 1 ]-------+--------------------
Schema | public
Name | gin_index_check
Result data type | void
Argument data types | index regclass
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | gin_index_check
Description |
\df+ verify_heapam
Result :
List of functions
-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | public
Name | verify_heapam
Result data type | SETOF record
Argument data types | relation regclass, on_error_stop boolean DEFAULT false, check_toast boolean DEFAULT false, skip text DEFAULT 'none'::text, startblock bigint DEFAULT NULL::bigint, endblock bigint DEFAULT NULL::bigint, OUT blkno bigint, OUT offnum integer, OUT attnum integer, OUT msg text
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres
Language | c
Internal name | verify_heapam
Description |
Now, create some sample tables for testing the functionalities of the am_check extension.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department TEXT,
salary NUMERIC,
skills TEXT[],
notes TEXT
);
Insert some sample values.
INSERT INTO employees(name, department, salary, skills, notes)
SELECT
'Employee_' || g,
CASE
WHEN g % 3 = 0 THEN 'HR'
WHEN g % 3 = 1 THEN 'IT'
ELSE 'Sales'
END,
30000 + random() * 70000,
ARRAY[
CASE WHEN g % 2 = 0 THEN 'Python' ELSE 'Java' END,
CASE WHEN g % 3 = 0 THEN 'PostgreSQL' ELSE 'Linux' END
],
'Employee number ' || g
FROM generate_series(1,10000) g;
Create indexes.
CREATE INDEX idx_emp_name
ON employees(name);
CREATE INDEX idx_emp_salary
ON employees(salary);
Check the structure of the employee table by using the command below.
\d+ employees
Result:
Table "public.employees"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+---------+-----------+----------+---------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('employees_id_seq'::regclass) | plain | | |
name | text | | | | extended | | |
department | text | | | | extended | | |
salary | numeric | | | | main | | |
skills | text[] | | | | extended | | |
notes | text | | | | extended | | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (id)
"idx_emp_name" btree (name)
"idx_emp_salary" btree (salary)
Not-null constraints:
"employees_id_not_null" NOT NULL "id"
Access method: heap
Now, create a gin index based on the column named skills.
CREATE INDEX idx_emp_skills
ON employees
USING GIN(skills);
Check all the index definitions from the pg_indexes catalogue like this.
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename='employees';
Result:
indexname | indexdef
----------------+-------------------------------------------------------------------------
employees_pkey | CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (id)
idx_emp_name | CREATE INDEX idx_emp_name ON public.employees USING btree (name)
idx_emp_salary | CREATE INDEX idx_emp_salary ON public.employees USING btree (salary)
idx_emp_skills | CREATE INDEX idx_emp_skills ON public.employees USING gin (skills)
(4 rows)
1. bt_index_check
Now, use the first function named bt_index_check with a single first argument.
SELECT bt_index_check('employees_pkey');Result:
bt_index_check
----------------
(1 row)
Here, the first argument is the name of the index. It will return empty if there is no issue in this index.
Now, check other indexes like this.
SELECT bt_index_check('idx_emp_name');Result:
bt_index_check
----------------
(1 row)
Try another one.
SELECT bt_index_check('idx_emp_salary');Result:
bt_index_check
----------------
(1 row)
Now, use this function with the first two arguments.
SELECT bt_index_check(
'idx_emp_name',
true
);
Result:
bt_index_check
----------------
(1 row)
Here, the first argument is the name of the index, and the second argument is called heapallindexed. This is a boolean indicating that all heap pages have their corresponding entry in this index.
Check another index like this.
SELECT bt_index_check(
'idx_emp_salary',
true
);
Result:
bt_index_check
----------------
(1 row)
We can also pass false to the heapallindexed argument like this.
SELECT bt_index_check(
'idx_emp_name',
false
);
Result:
bt_index_check
----------------
(1 row)
Now, use the third version of this function that contains three arguments.
SELECT bt_index_check(
'employees_pkey',
true,
true
);
Result:
bt_index_check
----------------
(1 row)
Here, the third argument indicates the uniqueness of this index.
We can also pass false to this third argument of uniqueness like this.
SELECT bt_index_check(
'idx_emp_name',
true,
false
);
Result:
bt_index_check
----------------
(1 row)
The second function provided by the extension is bt_index_parent_check. Unlike bt_index_check, which only verifies the internal consistency of a B-tree index, bt_index_parent_check performs a more comprehensive validation by also checking the parent-child relationships between index pages. This ensures that the B-tree structure is correctly linked and helps detect more complex forms of index corruption.
2.bt_index_parent_check
Now, try to use the second function like this.
SELECT bt_index_parent_check(
'idx_emp_name'
);
Result :
bt_index_parent_check
-----------------------
(1 row)
Here, the first argument defines the name of the index.
Now, try this function with two arguments.
SELECT bt_index_parent_check(
'idx_emp_name',
true
);
Result:
bt_index_parent_check
-----------------------
(1 row)
The second argument indicates the heapallindexed boolean.
Now, try to use the function with three arguments.
SELECT bt_index_parent_check(
'employees_pkey',
true,
true
);
Result :
bt_index_parent_check
-----------------------
(1 row)
Here, the third parameter is rootdescend.
This parameter controls how bt_index_parent_check verifies the parent-child links in the B-tree index.
- rootdescend = false (default)
Performs the normal parent-child consistency check, which is sufficient for most cases.
Performs an additional verification by descending from the root page to each child page to confirm that every page is reachable through the correct path in the B-tree. This provides a more thorough validation and can detect certain structural corruptions that the normal check might miss.
Try this function with 4 arguments.
SELECT bt_index_parent_check(
'employees_pkey',
true,
true,
true
);
Result:
bt_index_parent_check
-----------------------
(1 row)
The fourth argument indicates the uniqueness of the index.
We can also pass boolean values based on our needs.
SELECT bt_index_parent_check(
'idx_emp_salary',
true,
false,
false
);
Result:
bt_index_parent_check
-----------------------
(1 row)
3.gin_index_check
We can also check the integrity of the gin indexes by using the function named gin_index_check.
SELECT gin_index_check(
'idx_emp_skills'
);
Result:
gin_index_check
-----------------
(1 row)
4.verify_heapam
The next function is verify_heapam.
It has mainly 10 arguments.
| Argument | Description |
| relation regclass | indicates the heap table to be checked for corruption |
| on_error_stop boolean | Determines whether the verification should stop immediately when corruption is found (true) or continue checking the remaining table (false, default). |
| check_toast boolean | Specifies whether the related toast table should also be verified. Set to true to check large values stored outside the main table. The default is false. |
| skip text | Specifies any checks that should be skipped during verification. The default value is none, and it means all checks are performed. |
| startblock bigint | Specifies the first table block (page) from which verification should begin. If NULL, the scan starts from the beginning of the table. |
| endblock bigint | Specifies the last table block (page) to verify. If NULL, the scan continues until the end of the table. |
| OUT blkno bigint | Returns the block number where a problem is detected. |
| OUT offnum integer | Returns the tuple (row) offset within the block where the issue was found. |
| OUT attnum integer | Returns the attribute (column) number related to the detected problem, if applicable. |
| OUT msg text | Returns a descriptive message explaining the detected corruption or integrity issue. |
SELECT *
FROM verify_heapam(
relation => 'employees',
on_error_stop => false,
check_toast => true,
skip => 'none',
startblock => 0,
endblock => 0
);
Result:
blkno | offnum | attnum | msg
-------+--------+--------+-----
(0 rows)
The amcheck extension provides a simple and reliable way to verify the health of PostgreSQL tables and indexes.
Functions such as bt_index_check, bt_index_parent_check, gin_index_check, and verify_heapam allow you to examine different database structures and detect corruption at an early stage.
Running these checks as part of routine database maintenance can improve reliability and help identify storage issues before they affect production workloads. Understanding how each function works also makes it easier to choose the appropriate integrity check for different database objects.