How to Configure Enum-Based Table Options in PostgreSQL

In postgres, we have different kinds of table options related to different data types. So here we are, going to explore the enum type table options in PostgreSQL.

In postgres source code, there is a file named reloptions.c

File path :

src/backend/access/common/reloptions.c

In this file, we can see different relation options for different data types. Each type has a separate structure here.

static relopt_bool boolRelOpts[] - relation options based on boolean data type
static relopt_ternary ternaryRelOpts[] - relation options based on ternary data type
static relopt_int intRelOpts[] - relation options based on integer data type
static relopt_real realRelOpts[]  - relation options based on real data type
static relopt_enum enumRelOpts[] -  relation options based on enum data type
static relopt_string stringRelOpts[] -  relation options based on string data type

Here, we are looking in more detail at the relation options based on the enum data type.

There are three relation options related to the enum data types. Look at this structure named

static relopt_enum enumRelOpts[] =
{
	{
		{
			"vacuum_index_cleanup",
			"Controls index vacuuming and index cleanup",
			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
			ShareUpdateExclusiveLock
		},
		StdRdOptIndexCleanupValues,
		STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO,
		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
	},
	{
		{
			"buffering",
			"Enables buffering build for this GiST index",
			RELOPT_KIND_GIST,
			AccessExclusiveLock
		},
		gistBufferingOptValues,
		GIST_OPTION_BUFFERING_AUTO,
		gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
	},
	{
		{
			"check_option",
			"View has WITH CHECK OPTION defined (local or cascaded).",
			RELOPT_KIND_VIEW,
			AccessExclusiveLock
		},
		viewCheckOptValues,
		VIEW_OPTION_CHECK_OPTION_NOT_SET,
		gettext_noop("Valid values are \"local\" and \"cascaded\".")
	},
	/* list terminator */
	{{NULL}}
};

1.vacuum_index_cleanup

This parameter is mainly used to determine whether the vacuum needs to clean the index during the execution of the vacuum process. This option has three possible values, with the default value set to AUTO.

Three values :

• On
• Off
• Auto

Create a table for testing purposes.

CREATE TABLE employees (
    id int,
    name text
) WITH (vacuum_index_cleanup = off);

Insert the values.

INSERT INTO employees
SELECT i, 'Employee-' || i
FROM generate_series(1,100000) i;

Create an index

CREATE INDEX idx_emp_id ON employees(id);

Check the size of the table and also the index.

SELECT pg_size_pretty(pg_relation_size('employees'));
 SELECT pg_size_pretty(pg_relation_size('idx_emp_id'));
SELECT pg_size_pretty(pg_total_relation_size('employees'));

Just perform the update query to produce dead tuples.

UPDATE employees SET id = id + 1000000 WHERE id <= 500000;
UPDATE employees SET id = id + 1000000 WHERE id >= 500000;

Check the dead tuples count.

SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'employees';
  relname  | n_live_tup | n_dead_tup 
-----------+------------+------------
 employees |    1000000 |    1500000

Now, perform the vacuum operation and check its messages info.

vacuum verbose employees ;

Result :

INFO:  vacuuming "postgres.public.employees"
INFO:  finished vacuuming "postgres.public.employees": index scans: 0
pages: 0 removed, 15923 remain, 9555 scanned (60.01% of total), 0 eagerly scanned
tuples: 0 removed, 400134 remain, 0 are dead but not yet removable
removable cutoff: 14790, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan bypassed: 9554 pages from table (60.00% of total) have 1500000 dead item identifiers
avg read rate: 542.709 MB/s, avg write rate: 0.000 MB/s
buffer usage: 14451 hits, 4679 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 307 bytes, 0 buffers full
system usage: CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.06 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_136500"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_136500": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 14790, which was 0 XIDs old when operation ended
new relfrozenxid: 14790, which is 66 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 88.778 MB/s, avg write rate: 0.000 MB/s
buffer usage: 9 hits, 1 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Now alter the table option like this

alter table employees set (vacuum_index_cleanup = 'on');

Now update some records and execute a vacuum.

UPDATE employees SET id = id + 1000000 WHERE id <= 500000;
UPDATE employees SET id = id + 1000000 WHERE id >= 500000;

Now execute vacuum

vacuum verbose employees ;

Result :

INFO:  vacuuming "postgres.public.employees"
INFO:  finished vacuuming "postgres.public.employees": index scans: 1
pages: 0 removed, 12739 remain, 12739 scanned (100.00% of total), 0 eagerly scanned
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 14924, which was 0 XIDs old when operation ended
new relfrozenxid: 14863, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 12739 pages set all-visible, 6368 pages set all-frozen (0 were all-visible)
index scan needed: 6370 pages from table (50.00% of total) had 1000000 dead item identifiers removed
index "idx_emp_id": pages: 6857 in total, 2742 newly deleted, 4112 currently deleted, 1370 reusable
avg read rate: 257.581 MB/s, avg write rate: 260.717 MB/s
buffer usage: 50204 hits, 6326 reads, 6403 dirtied
WAL usage: 33693 records, 5 full page images, 8041343 bytes, 0 buffers full
system usage: CPU: user: 0.10 s, system: 0.03 s, elapsed: 0.19 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_136532"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_136532": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 14924, which was 0 XIDs old when operation ended
new relfrozenxid: 14924, which is 66 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 142.045 MB/s, avg write rate: 0.000 MB/s
buffer usage: 24 hits, 2 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

The main difference is:

• Case 1: vacuum_index_cleanup = off

index scans: 0

index scan bypassed: 9554 pages from table (60.00% of total)

have 1500000 dead item identifiers

• Case 2: vacuum_index_cleanup = on

index scan needed: 6370 pages from table (50.00% of total) had 1000000 dead item identifiers removed

index "idx_emp_id": pages: 6857 in total, 2742 newly deleted, 4112 currently deleted, 1370 reusable

2. buffering

You can see the rel option named buffering related codepart in the reloptions.c file from the postgres source code.

{
       {
           "buffering",
           "Enables buffering build for this GiST index",
           RELOPT_KIND_GIST,
           AccessExclusiveLock
       },
       gistBufferingOptValues,
       GIST_OPTION_BUFFERING_AUTO,
       gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
   },

Let’s check each rel option named buffering, and its usage in different queries.

Create the table.

CREATE TABLE locations_off (
    id serial PRIMARY KEY,
    name text,
    coord point
);

Insert some random values.

INSERT INTO locations_off (name, coord)
SELECT
    'place_' || i,
    point(random() * 100, random() * 100)
FROM generate_series(1, 1000000) AS i;

Use threloptions. mands of timing in postgres to track how much time each query execution takes.

\timing

Create the index with buffering mode off.

CREATE INDEX idx_locations_off_coord ON locations_off USING gist (coord)
WITH (buffering = off);

Execution time :

Time: 1763.064 ms (00:01.763)

Check the reloption is correct by checking the postgres catalogue named pg_class.’

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'idx_locations_off_coord';
         relname         |   reloptions    
-------------------------+-----------------
 idx_locations_off_coord | {buffering=off}
(1 row)

Check the size of the index we created.

 select pg_size_pretty(pg_total_relation_size('idx_locations_off_coord'));
 pg_size_pretty 
----------------
 64 MB
(1 row)

Now, let’s try the same types of query with the buffering mode on.

CREATE TABLE locations_on (
    id serial PRIMARY KEY,
    name text,
    coord point
);

Insert the values.

INSERT INTO locations_on (name, coord)
SELECT
    'place_' || i,
    point(random() * 100, random() * 100)
FROM generate_series(1, 1000000) AS i;

Create the index with the buffering mode on

CREATE INDEX idx_locations_on_coord ON locations_on USING gist (coord)
WITH (buffering = on);

Execution time :

Time: 4901.518 ms (00:04.902)

Check the rel option value from the pg catalogue named pg_class to ensure that the buffering mode is on.

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'idx_locations_on_coord';
        relname         |   reloptions   
------------------------+----------------
 idx_locations_on_coord | {buffering=on}
(1 row)

Check the size of the index.

select pg_size_pretty(pg_total_relation_size('idx_locations_off_coord'));
 pg_size_pretty 
----------------
 64 MB
(1 row)

Now use the auto mode for the buffering mode.

CREATE TABLE locations_auto (
    id serial PRIMARY KEY,
    name text,
    coord point
);

Insert the values.

INSERT INTO locations_auto (name, coord)
SELECT
    'place_' || i,
    point(random() * 100, random() * 100)
FROM generate_series(1, 1000000) AS i;

Create the gist index with the buffering mode as auto.

CREATE INDEX idx_locations_auto_coord ON locations_auto USING gist (coord)
WITH (buffering = auto);

Execution time :

Time: 1657.507 ms (00:01.658)

Check the rel option to ensure that the buffering mode value from the pg catalogue named pg class is auto or not.

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'idx_locations_auto_coord';
         relname          |    reloptions    
--------------------------+------------------
 idx_locations_auto_coord | {buffering=auto}
(1 row)

Check the size of the index.

select pg_size_pretty(pg_total_relation_size('idx_locations_auto_coord'));
 pg_size_pretty 
----------------
 64 MB
(1 row)

There are mainly three values for this reloption

• Off - Here, when we create a gist index with the buffering mode as off, each record is inserted into the gist index one at a time.
• On - When we use the buffering mode as on, now the tuples are actually not directly inserted into the index pages. Instead of this, we collect the tuples in gist index build buffers and group them and later insert into the index pages.
• Auto - This is a selection mode where its default value is off, and whenever they can choose buffer mode based on the scenarios.

We can see the execution time difference in the three buffering modes.

Buffering mode - off - execution time : Time: 1763.064 ms (00:01.763)

Buffering mode - on - execution time : Time: 4901.518 ms (00:04.902)

Buffering mode - auto - execution time : Time: 1657.507 ms (00:01.658)

3.check_option

This rel option can be used only with the views, and we have two options with CASCADED CHECK OPTION and LOCAL CHECK OPTION;

You can see the check_option-related code in the reloptions.c file like this

{
       {
           "check_option",
           "View has WITH CHECK OPTION defined (local or cascaded).",
           RELOPT_KIND_VIEW,
           AccessExclusiveLock
       },
       viewCheckOptValues,
       VIEW_OPTION_CHECK_OPTION_NOT_SET,
       gettext_noop("Valid values are \"local\" and \"cascaded\".")
   },

Create a simple view with no check option.

CREATE TABLE employees
(
    emp_id      SERIAL PRIMARY KEY,
    emp_name    TEXT,
    department  TEXT,
    salary      NUMERIC
);

Insert the values.

INSERT INTO employees (emp_name, department, salary)
VALUES
('Alice', 'HR', 50000),
('Bob', 'IT', 65000),
('Charlie', 'IT', 72000),
('David', 'Sales', 45000),
('Emma', 'HR', 52000);

Now, create a normal view.

CREATE VIEW it_employees AS
SELECT *
FROM employees
WHERE department = 'IT';

Now check the records from the view.

select * from it_employees ;
 emp_id | emp_name | department | salary 
--------+----------+------------+--------
      2 | Bob      | IT         |  65000
      3 | Charlie  | IT         |  72000
(2 rows)

Now insert through the view.

INSERT INTO it_employees
(emp_name, department, salary)
VALUES
('John', 'HR', 60000);

Now, check the record again.

select * from it_employees ;

Result :

 emp_id | emp_name | department | salary 
--------+----------+------------+--------
      2 | Bob      | IT         |  65000
      3 | Charlie  | IT         |  72000
(2 rows)

Check the records from the table.

select * from employees;

Result :

 emp_id | emp_name | department | salary 
--------+----------+------------+--------
      1 | Alice    | HR         |  50000
      2 | Bob      | IT         |  65000
      3 | Charlie  | IT         |  72000
      4 | David    | Sales      |  45000
      5 | Emma     | HR         |  52000
      6 | John     | HR         |  60000
(6 rows)

Now, you can see that the newly inserted value through the view is correctly inserted into the table named employees.

Drop the view and create a new view with the check option LOCAL.

DROP VIEW it_employees;
CREATE VIEW it_employees AS
SELECT *
FROM employees
WHERE department='IT'
WITH LOCAL CHECK OPTION;

Ensure that the created view has the check option set to local.

SELECT relname,
       reloptions
FROM pg_class
WHERE relname='it_employees';

Result :

   relname    |      reloptions      
--------------+----------------------
 it_employees | {check_option=local}
(1 row)

Now, insert the values through the view with the same department name.

INSERT INTO it_employees
(emp_name, department, salary)
VALUES
('Kevin', 'IT', 70000);

Now, check the values from the table.

select * from it_employees ;

Result :

 emp_id | emp_name | department | salary 
--------+----------+------------+--------
      2 | Bob      | IT         |  65000
      3 | Charlie  | IT         |  72000
      7 | Kevin    | IT         |  70000
(3 rows)

Now, insert a record through the view with a different department name.

INSERT INTO it_employees
(emp_name, department, salary)
VALUES
('Mark', 'HR', 60000);

Now, you get an issue like this.

ERROR:  new row violates check option for view "it_employees"
DETAIL:  Failing row contains (8, Mark, HR, 60000).

You can see that the newly inserted record fails due to the check option as local. This validation is the main purpose of the local check option. It actually tries to verify the record before insertion, and it only inserts the values into the table based on the view condition.

Now, try to update a record from the view.

UPDATE it_employees
SET department='HR'
WHERE emp_name='Bob';

Result :

ERROR:  new row violates check option for view "it_employees"
DETAIL:  Failing row contains (2, Bob, HR, 65000).

You get an error like this because of the validation of the local check option.

Now, change the check option to cascaded by using the alter command.

ALTER VIEW it_employees
SET (check_option=cascaded);

Now, ensure that the value of the check option has changed or not.

 SELECT relname,
       reloptions
FROM pg_class
WHERE relname='it_employees';

Result :

   relname    |       reloptions        
--------------+-------------------------
 it_employees | {check_option=cascaded}
(1 row)

Now, create a view with the department named it, and also use the check option as local.

CREATE VIEW it_view AS
SELECT *
FROM employees
WHERE department='IT'
WITH LOCAL CHECK OPTION;

Now, create a view with the salary condition and also use the cascaded check option, and create the new view by inheriting from the previous view.

CREATE VIEW high_salary_it AS
SELECT *
FROM it_view
WHERE salary>60000
WITH CASCADED CHECK OPTION;

Check the values of the view with the check option cascaded.

SELECT * FROM high_salary_it;

Result :

 id | name  | department | salary 
----+-------+------------+--------
  1 | Alice | IT         |  70000
(1 row)

Now, try to insert a new record that satisfies the cascaded check option view condition but not the local check option view condition.

INSERT INTO high_salary_it
(name,department,salary)
VALUES
('Kevin','HR',80000);

Now, you can see the error because it satisfies the cascaded view condition, and it didn't satisfy the local check option view condition.

You get a validation error like this because of the cascaded check option.

ERROR:  new row violates check option for view "it_view"
DETAIL:  Failing row contains (4, Kevin, HR, 80000).

Many of the postgres users have only known the basics of view like creating a view, or materialized views. But postgres is a feature-rich database that we can use the hidden features like these to improve our database functionality and also data correctness. As a postgres developer or a database administrator, should know these types of enum table options in postgres. Postgres also provides so many table options in postgres. By using these hidden features in postgres we can ensure the quality of data in the database.