How to Use the Boolean Type Relation Options in PostgreSQL

PostgreSQL provides several relation options that can be enabled or disabled using boolean values. These options are defined inside the PostgreSQL source code file named reloptions.c using the boolRelOpts structure. These relation options help control the behavior of tables, indexes, and views.

Inside PostgreSQL source code, the following structure defines the supported boolean relation options.

static relopt_bool boolRelOpts[] =
{
	{
		{
			"autosummarize",
			"Enables automatic summarization on this BRIN index",
			RELOPT_KIND_BRIN,
			AccessExclusiveLock
		},
		false
	},
	{
		{
			"autovacuum_enabled",
			"Enables autovacuum in this relation",
			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
			ShareUpdateExclusiveLock
		},
		true
	},
	{
		{
			"user_catalog_table",
			"Declare a table as an additional catalog table, e.g. for the purpose of logical replication",
			RELOPT_KIND_HEAP,
			AccessExclusiveLock
		},
		false
	},
	{
		{
			"fastupdate",
			"Enables \"fast update\" feature for this GIN index",
			RELOPT_KIND_GIN,
			AccessExclusiveLock
		},
		true
	},
	{
		{
			"security_barrier",
			"View acts as a row security barrier",
			RELOPT_KIND_VIEW,
			AccessExclusiveLock
		},
		false
	},
	{
		{
			"security_invoker",
			"Privileges on underlying relations are checked as the invoking user, not the view owner",
			RELOPT_KIND_VIEW,
			AccessExclusiveLock
		},
		false
	},
	{
		{
			"deduplicate_items",
			"Enables \"deduplicate items\" feature for this btree index",
			RELOPT_KIND_BTREE,
			ShareUpdateExclusiveLock
		},
		true
	},
	{{NULL}}
};

Understanding Boolean Relation Options

Boolean relation options accept only two values:

• True or
• false

These options are usually applied using the WITH (...) clause while creating tables, indexes, or views.

Example:

WITH (autovacuum_enabled = true)

The relation option changes the internal behavior of PostgreSQL for that specific relation.

1. autovacuum_enabled

The autovacuum_enabled option controls whether PostgreSQL autovacuum runs on a table.

Autovacuum is responsible for:

• Cleaning dead tuples
• Preventing table bloat
• Updating statistics
• Avoiding transaction ID wraparound issues

By default, this option is enabled.

Example with autovacuum enabled

CREATE TABLE employees (
    id serial,
    name text,
    salary int
)
WITH (
    autovacuum_enabled = true
);

Checking table details:

\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 |             |              | 
 salary | integer |           |          |                                       | plain    |             |              | 
Not-null constraints:
    "employees_id_not_null" NOT NULL "id"
Access method: heap
Options: autovacuum_enabled=true

You can also see the reloptions of specific tables by using the below query.

Example :

select * from pg_class where relname = 'employees';

Result :

-[ RECORD 1 ]-------+--------------------------
oid                 | 24779
relname             | employees
relnamespace        | 2200
reltype             | 24781
reloftype           | 0
relowner            | 10
relam               | 2
relfilenode         | 24779
reltablespace       | 0
relpages            | 0
reltuples           | -1
relallvisible       | 0
relallfrozen        | 0
reltoastrelid       | 24784
relhasindex         | f
relisshared         | f
relpersistence      | p
relkind             | r
relnatts            | 3
relchecks           | 0
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | d
relispartition      | f
relrewrite          | 0
relfrozenxid        | 886
relminmxid          | 1
relacl              | 
reloptions          | {autovacuum_enabled=true}
relpartbound        | 

Here you can see the table’s applied relation option inside curly brackets like this.

Example with autovacuum disabled

CREATE TABLE logs (
    id serial,
    message text
)
WITH (
    autovacuum_enabled = false
);

Checking table details:

\d+ logs

Result:

                                                       Table "public.logs"
 Column  |  Type   | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description 
---------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id      | integer |           | not null | nextval('logs_id_seq'::regclass) | plain    |             |              | 
 message | text    |           |          |                                  | extended |             |              | 
Not-null constraints:
    "logs_id_not_null" NOT NULL "id"
Access method: heap
Options: autovacuum_enabled=false

When autovacuum is disabled, PostgreSQL will not automatically clean dead tuples for that table. This can improve performance in some write-heavy temporary workloads, but it may also cause severe table bloat if not managed manually.

2. user_catalog_table

The user_catalog_table option marks a normal user table as an additional catalog table.

This option is mainly useful internally for PostgreSQL features such as logical replication and catalog-related checks.

Example

CREATE TABLE custom_catalog (
    id int,
    setting text
)
WITH (
    user_catalog_table = true
);

Checking relation information:

SELECT
    relname,
    reloptions,
    relkind,
    relisshared
FROM pg_class
WHERE relname = 'custom_catalog';

Result:

   relname     |        reloptions         | relkind | relisshared
----------------+---------------------------+---------+-------------
 custom_catalog | {user_catalog_table=true} | r       | f

In PostgreSQL source code, there is a function named:

IsCatalogRelationOid()

This function is used internally to check whether a relation behaves like a catalog relation. Although the table still appears as a normal heap relation (relkind = r), PostgreSQL internally treats it differently because of this option.

3. Fastupdate

The fastupdate option belongs to GIN indexes. GIN indexes normally use a pending list mechanism for faster inserts. This behavior is controlled using fastupdate.

• true - Inserts go to the pending list first
• false - Inserts directly update the main GIN structure

Creating tables

CREATE TABLE docs_fast (
    id serial,
    content text
);
CREATE TABLE docs_slow (
    id serial,
    content text
);

Creating GIN indexes

CREATE INDEX gin_fast_idx
ON docs_fast
USING GIN (to_tsvector('english', content))
WITH (
    fastupdate = true
);
CREATE INDEX gin_slow_idx
ON docs_slow
USING GIN (to_tsvector('english', content))
WITH (
    fastupdate = false
);

Performance Test

\timing on

Insert into fastupdate enabled table

INSERT INTO docs_fast(content)
SELECT
    md5(g::text) || ' postgresql gin fastupdate benchmark'
FROM generate_series(1, 1000000) g;

Result:

Time: 12417.975 ms

Insert into fastupdate disabled table

INSERT INTO docs_slow(content)
SELECT
    md5(g::text) || ' postgresql gin fastupdate benchmark'
FROM generate_series(1, 1000000) g;

Result:

Time: 21347.861 ms

The result clearly shows that enabling fastupdate significantly improves insert performance for GIN indexes.

4. Security_barrier

The security_barrier option is used with views. This prevents PostgreSQL from pushing user conditions into the underlying table scan in unsafe ways. It is mainly used for security-sensitive views.

Create table

CREATE TABLE employees (
    id INT,
    name TEXT,
    salary INT
);

Insert data

INSERT INTO employees VALUES
(1, 'Alice', 40000),
(2, 'Bob', 70000),
(3, 'Charlie', 90000);

Create test function

CREATE FUNCTION leak_salary(INT)
RETURNS BOOLEAN
AS $$
BEGIN
    RAISE NOTICE 'Salary = %', $1;
    RETURN true;
END;
$$ LANGUAGE plpgsql;

Normal view without security barrier

CREATE VIEW high_salary_emp AS
SELECT * FROM employees
WHERE salary > 50000;

Execution plan:

EXPLAIN VERBOSE
SELECT *
FROM high_salary_emp
WHERE leak_salary(salary);

Result:

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Seq Scan on public.employees  (cost=0.00..325.00 rows=133 width=40)
   Output: employees.id, employees.name, employees.salary
   Filter: ((employees.salary > 50000) AND leak_salary(employees.salary))
(3 rows)

Here PostgreSQL merged both conditions together.

View with security_barrier enabled

CREATE VIEW secure_high_salary_emp
WITH (security_barrier=true)
AS
SELECT * FROM employees
WHERE salary > 50000;

Execution plan:

EXPLAIN VERBOSE
SELECT *
FROM secure_high_salary_emp
WHERE leak_salary(salary);

Result:

                                          QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Subquery Scan on secure_high_salary_emp  (cost=0.00..129.00 rows=133 width=40)
   Output: secure_high_salary_emp.id, secure_high_salary_emp.name, secure_high_salary_emp.salary
   Filter: leak_salary(secure_high_salary_emp.salary)
   ->  Seq Scan on public.employees  (cost=0.00..25.00 rows=400 width=40)
         Output: employees.id, employees.name, employees.salary
         Filter: (employees.salary > 50000)
(6 rows)

Here PostgreSQL first applies the view filter and only then applies the external condition. This prevents unsafe predicate pushdown and protects sensitive rows.

5. Security_invoker

Normally, PostgreSQL views execute using the permissions of the view owner. The security_invoker option changes this behavior so that permissions are checked using the invoking user instead.

Create table

CREATE TABLE employees (
    id INT,
    name TEXT,
    salary INT
);

Insert values

INSERT INTO employees VALUES
(1, 'Alice', 50000),
(2, 'Bob', 70000);

Create roles

CREATE ROLE manager LOGIN PASSWORD '123';
CREATE ROLE trainee LOGIN PASSWORD '123';

Grant table access only to manager:

GRANT SELECT ON employees TO manager;

Create normal view

CREATE VIEW emp_view AS
SELECT * FROM employees;

Grant access to trainee:

GRANT SELECT ON emp_view TO trainee;

Now login as trainee: Before doing that , you need to set the login related settings in the hba file. You can see the path of hba file like this.

postgres=# show hba_file ;

Result :

              hba_file               
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)

Open the file and add the login configuration data for the roles named manager and trainee like this

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             manager                                 trust
local   all             trainee                                 trust

Restart the postgresql server

Sudo systemctl restart postgresql

Login as trainee user

psql -U trainee -d postgres
postgres=> SELECT * FROM emp_view;

Result :

 id | name  | salary 
----+-------+--------
  1 | Alice |  50000
  2 | Bob   |  70000
(2 rows)

Even though the trainee does not have permission on the table, the query works because the view owner permissions are used.

Create security_invoker view

CREATE VIEW emp_view
WITH (security_invoker = true)
AS
SELECT * FROM employees;

Grant permission:

GRANT SELECT ON emp_view TO trainee;

Now login as trainee:

psql -U trainee -d postgres
SELECT * FROM emp_view;

Result:

ERROR: permission denied for table employees

Now PostgreSQL checks the underlying table permissions using the invoking user. Grant table permission:

GRANT SELECT ON employees TO trainee;

Now the query works:

SELECT * FROM emp_view;

Result:

id | name  | salary
----+-------+--------
  1 | Alice |  50000
  2 | Bob   |  70000

This option is very useful for secure permission handling.

6. Deduplicate_items

The deduplicate_items option belongs to B-Tree indexes. This feature reduces index size when many duplicate values exist.

Create table

CREATE TABLE employees (
    id serial,
    department text
);

Insert 1 million rows

INSERT INTO employees (department)
SELECT
    CASE
        WHEN i % 3 = 0 THEN 'HR'
        WHEN i % 3 = 1 THEN 'IT'
        ELSE 'SALES'
    END
FROM generate_series(1, 1000000) s(i);

Create index with deduplication enabled

CREATE INDEX idx_dept_dedup
ON employees(department)
WITH (deduplicate_items = on);

Create index with deduplication disabled

CREATE INDEX idx_dept_nodedup
ON employees(department)
WITH (deduplicate_items = off);

Compare index sizes

SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname IN (
    'idx_dept_dedup',
    'idx_dept_nodedup'
);

Result:

    relname      | pg_size_pretty
------------------+----------------
 idx_dept_dedup   | 6792 kB
 idx_dept_nodedup | 21 MB

The deduplicated index uses significantly less disk space.

7. Autosummarize

The autosummarize relation option is used with BRIN indexes in PostgreSQL. BRIN stands for Block Range Index. Unlike B-Tree indexes, BRIN indexes do not store an entry for every row. Instead, they store summarized information for groups of table pages called page ranges.

Because of this design, BRIN indexes are very small and efficient for large tables where data is naturally ordered, such as timestamps, IDs, or log records. The autosummarize option controls whether PostgreSQL automatically creates summaries for newly added page ranges.

• autosummarize = truePostgreSQL automatically summarizes new ranges during autovacuum operations.
• autosummarize = falseNew ranges remain unsummarized until manual summarization is performed.

Creating Test Table

CREATE TABLE sensor_logs(
    id BIGSERIAL,
    temperature INT,
    created_at TIMESTAMP
);

Insert Initial Data

INSERT INTO sensor_logs(temperature, created_at)
SELECT
    (random()*100)::INT,
    NOW() + (g || ' seconds')::INTERVAL
FROM generate_series(1,2000000) g;

Create the pageinspect extension

Create extension pageinspect;

BRIN Index with autosummarize Disabled

Create BRIN Index

CREATE INDEX brin_no_auto_idx
ON sensor_logs
USING brin(created_at)
WITH (
    autosummarize = false
);

Check Existing BRIN Summary Entries

SELECT count(*)
FROM brin_page_items(
    get_raw_page('brin_no_auto_idx', 2),
    'brin_no_auto_idx'
);

Result:

count
-------
   100

At this stage, the BRIN index contains summaries for the currently existing page ranges.

Insert Additional Rows

INSERT INTO sensor_logs(temperature, created_at)
SELECT
    (random()*100)::INT,
    NOW() + (g || ' seconds')::INTERVAL
FROM generate_series(1,500000) g;

Check BRIN Summaries Again

SELECT count(*)
FROM brin_page_items(
    get_raw_page('brin_no_auto_idx', 2),
    'brin_no_auto_idx'
);

Result:

count
-------
   100

Even after inserting 500,000 new rows, the summary count remains unchanged. This happens because autosummarize is disabled, so PostgreSQL does not automatically summarize the newly created page ranges.

Manually Summarize New Values

SELECT brin_summarize_new_values('brin_no_auto_idx');

Result:

brin_summarize_new_values
---------------------------
                         1

Check Summary Count After Manual Summarization

SELECT count(*)
FROM brin_page_items(
    get_raw_page('brin_no_auto_idx', 2),
    'brin_no_auto_idx'
);

Result:

count
-------
   125

Now PostgreSQL created summaries for the newly inserted page ranges. This demonstrates that when autosummarize is disabled, manual maintenance becomes necessary.

BRIN Index with autosummarize Enabled

Create New BRIN Index

CREATE INDEX brin_auto_idx
ON sensor_logs
USING brin(created_at)
WITH (
    autosummarize = true
);

Check Initial Summary Count

SELECT count(*)
FROM brin_page_items(
    get_raw_page('brin_auto_idx', 2),
    'brin_auto_idx'
);

Result:

count
-------
   125

Insert Additional Rows

INSERT INTO sensor_logs(temperature, created_at)
SELECT
    (random()*100)::INT,
    NOW() + (g || ' seconds')::INTERVAL
FROM generate_series(1,500000) g;

Check Summary Count Immediately

SELECT count(*)
FROM brin_page_items(
    get_raw_page('brin_auto_idx', 2),
    'brin_auto_idx'
);

Result:

count
-------
   125

Immediately after insertion, the summary count does not change because autovacuum has not yet processed the new ranges.

Run VACUUM

VACUUM;

Check Summary Count Again

SELECT count(*)
FROM brin_page_items(
    get_raw_page('brin_auto_idx', 2),
    'brin_auto_idx'
);

Result:

count
-------
   149

After VACUUM execution, PostgreSQL automatically summarized the new page ranges because autosummarize is enabled. When autosummarize is disabled:

• Newly inserted page ranges remain unsummarized
• Manual calls to brin_summarize_new_values() are required
• Index maintenance becomes a manual responsibility

When autosummarize is enabled:

• PostgreSQL automatically summarizes new ranges
• VACUUM and autovacuum handle maintenance
• BRIN indexes remain updated with less manual effort

For large datasets, enabling autosummarize helps keep BRIN indexes efficient while reducing administrative overhead.

Boolean relation options in PostgreSQL provide powerful low-level controls for tables, indexes, and views. These options are defined internally in PostgreSQL source code inside reloptions.c, but they are also fully accessible through SQL. The examples demonstrated how these options affect:

• Autovacuum behavior
• GIN index insert performance
• B-Tree index storage optimization
• View security handling
• Catalog relation behavior
• BRIN summarization

Understanding these options is extremely useful for PostgreSQL developers, database administrators, and people exploring PostgreSQL internals and source code. The boolRelOpts structure is a good starting point for anyone interested in understanding how PostgreSQL internally manages configurable relation behaviors.