Postgresql: Command runbook for DBA

Postgresql runbook for a DBA:

Steps to perform "cluster" command in postgres.... after brief data collection

Ensure to enable putty log...

1) Connect to postgres user:

command:

su - postgres

2) Find out the postgres process running:

command:

ps -ef|grep -i postgres

3) echo $PATH and $PGDATA variable

command:

echo $PATH

echo $PGDATA

4) Find out the location of psql

command:

which psql

5) Collect the cron job details

command:

crontab -l

6) Collect df -h output

command:

df -h

7) Connect to psql

command:

psql

8) Collect the db list

command:

\l

or 

select a.oid

,a.datname

,b.rolname owner

,a.encoding

,a.dattablespace

,a.datcollate

,a.datctype

,a.datacl

from pg_database a

,pg_authid b

where b.oid=a.datdba;

9) Connect to the db where the table is located

command:

\c <dbname>

10) View the schema list

command:

select * from pg_catalog.pg_namespace;

or

\dn+

11) show your search path first

command:

show search_path;

12) Set the search path the candidate schema

command:

SET search_path TO <candidate_schema>;

13) show your search path again [remember not to disconnect or exit this connection]

command:

show search_path;

14) List the tables along with their sizes

command:

\d+

15) Collect more details on the table

command:

select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='<candidate_schema>' order by 1;

select relname table_name

,relnamespace schema_name

,reltype relation_type

,relowner owner

,relpages pages_or_blocks

,reltuples numrows

,relhasindex has_index

from pg_class

where relnamespace=(select oid from pg_namespace where nspname=<candidate_schema>')

and relname='<candidate_table>' order by 2,1;

16) Take description details

command:

\d <candidate table_name>;

17) Let us examine the index status

command:

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='<candidate_schema>'

and c.schemaname=d.nspname

and c.tablename='<candidate_table>'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

18) Now secure backup of the candidate table [in case needed, we can revert both table structure and data]

command:

pg_dump -U <candidate_username> -d <candidate_database> -t <candidate_schema>.<candidate_table> > <candidate_mountspace>/pgtst_schema_pgtst_tbl2.sql

19) Let us run cluster command: << outage begins here

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

cluster verbose <candidate_schemaname>.<candidate_tablename> using <candidate_index>;

20) Let us run analyze

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

analyze verbose <candidate_schemaname>.<candidate_tablename>;

20) Let us examine the index status again

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='<candidate_schema>'

and c.schemaname=d.nspname

and c.tablename='<candidate_table>'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

21) Let us examine the table status again

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='<candidate_schema>' order by 1;

select relname table_name

,relnamespace schema_name

,reltype relation_type

,relowner owner

,relpages pages_or_blocks

,reltuples numrows

,relhasindex has_index

from pg_class

where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema')

and relname='<candidate_table>' order by 2,1;

\dn+

This closes the command summary for clustering operation.

Postgresql: What happens to the other index than the one used for cluster table command?

Objective: Let us examine the impact of the cluster table command on the other indexes than the one used for cluster command (in case of a table has more than 1 index).

create a table with 2 indexes and perform cluster operation and observe what happens to the other index which isnt participating in the cluster operation

SET search_path TO pgtst_schema;
create table pgtst_tbl2
(
id int primary key
,name text
,age int
);
create index pgtst_tbl2_idx_age on pgtst_tbl2(age);

Actual output:

pgtst_db=> SET search_path TO pgtst_schema;
SET

pgtst_db=> show search_path;
-[ RECORD 1 ]-------------
search_path | pgtst_schema

pgtst_db=> create table pgtst_tbl2
pgtst_db-> (
pgtst_db(> id int primary key
pgtst_db(> ,name text
pgtst_db(> ,age int
pgtst_db(> );
CREATE TABLE

pgtst_db=> create index pgtst_tbl2_idx_age on pgtst_tbl2(age);
CREATE INDEX

pgtst_db=> \d pgtst_tbl2
          Table "pgtst_schema.pgtst_tbl2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           |          |
 age    | integer |           |          |
Indexes:
    "pgtst_tbl2_pkey" PRIMARY KEY, btree (id)
    "pgtst_tbl2_idx_age" btree (age)
pgtst_db=>

Now load data:

insert into pgtst_tbl2
SELECT id,name,mod(id,100)
FROM pgtst_tbl1
where id < 1000
order by random();

pgtst_db=> insert into pgtst_tbl2
pgtst_db-> SELECT id,name,mod(id,100)
pgtst_db-> FROM pgtst_tbl1
pgtst_db-> where id < 1000
pgtst_db-> order by random();
INSERT 0 999

pgtst_db=> commit;
WARNING:  there is no transaction in progress
COMMIT

pgtst_db=>
pgtst_db=> select * from pgtst_tbl2 limit 10;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------
id   | 704
name | CTST
age  | 4
-[ RECORD 2 ]--------------------------------------------------------------------------------------------
id   | 42
name | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
age  | 42
-[ RECORD 3 ]--------------------------------------------------------------------------------------------
id   | 512
name | CHKCHKCHKTST
age  | 12
-[ RECORD 4 ]--------------------------------------------------------------------------------------------
id   | 38
name | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
age  | 38
-[ RECORD 5 ]--------------------------------------------------------------------------------------------
id   | 567
name | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
age  | 67
-[ RECORD 6 ]--------------------------------------------------------------------------------------------
id   | 605
name | CHTST
age  | 5
-[ RECORD 7 ]--------------------------------------------------------------------------------------------
id   | 450
name | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
age  | 50
-[ RECORD 8 ]--------------------------------------------------------------------------------------------
id   | 85
name | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
age  | 85
pgtst_db=>

Rows arent sorted. So this time, we will evaluate the status of both the indexes and also when we do cluster we will increase the fillfactor.

pg_indexes
pg_class
select c.schemaname
,c.tablename
,c.indexname
,b.indisunique
,b.indisprimary
,b.indisclustered
,b.indisvalid
,b.indisready
,b.indislive
from pg_class a
,pg_index b
,pg_indexes c
where a.oid=b.indexrelid
and a.relnamespace=(select oid from pg_namespace where nspname='pgtst_schema')
and c.schemaname='pgtst_schema'
and c.indexname=a.relname
;

Actual output:

pgtst_db=> select c.schemaname
pgtst_db-> ,c.tablename
pgtst_db-> ,c.indexname
pgtst_db-> ,b.indisunique
pgtst_db-> ,b.indisprimary
pgtst_db-> ,b.indisclustered
pgtst_db-> ,b.indisvalid
pgtst_db-> ,b.indisready
pgtst_db-> ,b.indislive
pgtst_db-> from pg_class a
pgtst_db-> ,pg_index b
pgtst_db-> ,pg_indexes c
pgtst_db-> where a.oid=b.indexrelid
pgtst_db-> and a.relnamespace=(select oid from pg_namespace where nspname='pgtst_schema')
pgtst_db-> and c.schemaname='pgtst_schema'
pgtst_db-> and c.indexname=a.relname
pgtst_db-> ;

  schemaname  |     tablename     |       indexname        | indisunique | indisprimary | indisclustered | indisvalid | in
disready | indislive
--------------+-------------------+------------------------+-------------+--------------+----------------+------------+---
---------+-----------
 pgtst_schema | pgtst_tbl1        | pgtst_tbl1_pkey        | t           | t            | f              | t          | t
         | t
 pgtst_schema | PGTST_TBL1        | PGTST_TBL1_pkey        | t           | t            | f              | t          | t
         | t
 pgtst_schema | pgtst_tbl1_random | pgtst_tbl1_random_pkey | t           | t            | t              | t          | t
         | t
 pgtst_schema | pgtst_tbl2        | pgtst_tbl2_pkey        | t           | t            | f              | t          | t
         | t
 pgtst_schema | pgtst_tbl2        | pgtst_tbl2_idx_age     | f           | f            | f              | t          | t
         | t
(5 rows)
pgtst_db=>

So we see is index valid (indisvalid) and is index ready (indisready) flags hold correct. All the indexes are valid and are ready for the usage.
Let us now do 2 tasks...
1) Adjust fillfactor to 80 for the table
2) Run cluster command and check the index on age column for its status

alter table pgtst_schema.pgtst_tbl2 set (fillfactor=80);

Actual output:

pgtst_db=> select reloptions from pg_class where relname='pgtst_tbl2';
   reloptions
-----------------
 {fillfactor=80}
(1 row)
pgtst_db=>

Now we have 2 options - vaccum full on the table impacted or running cluster command.

We run cluster:
Actual output:

pgtst_db=> cluster pgtst_tbl2 using pgtst_tbl2_pkey;
CLUSTER
pgtst_db=> select * from pgtst_tbl2 limit 10;
 id |    name    | age
----+------------+-----
  1 | T          |   1
  2 | TS         |   2
  3 | TST        |   3
  4 | CTST       |   4
  5 | CHTST      |   5
  6 | CHKTST     |   6
  7 | CHKCTST    |   7
  8 | CHKCHTST   |   8
  9 | CHKCHKTST  |   9
 10 | CHKCHKCTST |  10
(10 rows)
pgtst_db=>

pgtst_db=> select c.schemaname
pgtst_db-> ,c.tablename
pgtst_db-> ,c.indexname
pgtst_db-> ,b.indisunique
pgtst_db-> ,b.indisprimary
pgtst_db-> ,b.indisclustered
pgtst_db-> ,b.indisvalid
pgtst_db-> ,b.indisready
pgtst_db-> ,b.indislive
pgtst_db-> from pg_class a
pgtst_db-> ,pg_index b
pgtst_db-> ,pg_indexes c
pgtst_db-> where a.oid=b.indexrelid
pgtst_db-> and a.relnamespace=(select oid from pg_namespace where nspname='pgtst_schema')
pgtst_db-> and c.schemaname='pgtst_schema'
pgtst_db-> and c.indexname=a.relname
pgtst_db-> ;
  schemaname  |     tablename     |       indexname        | indisunique | indisprimary | indisclustered | indisvalid | in
disready | indislive
--------------+-------------------+------------------------+-------------+--------------+----------------+------------+---
---------+-----------
 pgtst_schema | pgtst_tbl1        | pgtst_tbl1_pkey        | t           | t            | f              | t          | t
         | t
 pgtst_schema | PGTST_TBL1        | PGTST_TBL1_pkey        | t           | t            | f              | t          | t
         | t
 pgtst_schema | pgtst_tbl1_random | pgtst_tbl1_random_pkey | t           | t            | t              | t          | t
         | t
 pgtst_schema | pgtst_tbl2        | pgtst_tbl2_idx_age     | f           | f            | f              | t          | t
         | t
 pgtst_schema | pgtst_tbl2        | pgtst_tbl2_pkey        | t           | t            | t              | t          | t
         | t
(5 rows)

We see now pgtst_tbl2_idx_age is still valid and ready. Also note pgtst_tbl2_pkey became clustered true vs false last time before clustering done using this sql.
So this means clustering doesnt affect other indexes in the table.

pgtst_db=> \dt+
                                               List of relations
    Schema    |       Name        | Type  |   Owner   | Persistence | Access method |    Size    | Description
--------------+-------------------+-------+-----------+-------------+---------------+------------+-------------
 pgtst_schema | PGTST_TBL1        | table | pgtst_usr | permanent   | heap          | 8192 bytes |
 pgtst_schema | pgtst_tbl1        | table | pgtst_usr | permanent   | heap          | 825 MB     |
 pgtst_schema | pgtst_tbl1_random | table | pgtst_usr | permanent   | heap          | 828 MB     |
 pgtst_schema | pgtst_tbl2        | table | pgtst_usr | permanent   | heap          | 120 kB     |
(4 rows)
pgtst_db=>

Thanks

Foot notes... later additions:

Index status examination sql:

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='pgtst_schema'

and c.schemaname=d.nspname

and c.tablename='pgtst_tbl1_random'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

pgtst_db=# select c.schemaname

pgtst_db-# ,c.tablename

pgtst_db-# ,c.indexname

pgtst_db-# ,b.indisunique

pgtst_db-# ,b.indisprimary

pgtst_db-# ,b.indisclustered

pgtst_db-# ,b.indisvalid

pgtst_db-# ,b.indisready

pgtst_db-# ,b.indislive

pgtst_db-# from pg_class a

pgtst_db-# ,pg_index b

pgtst_db-# ,pg_indexes c

pgtst_db-# ,pg_namespace d

pgtst_db-# where d.nspname='pgtst_schema'

pgtst_db-# and c.schemaname=d.nspname

pgtst_db-# and c.tablename='pgtst_tbl1_random'

pgtst_db-# and c.indexname=a.relname

pgtst_db-# and a.oid=b.indexrelid

pgtst_db-# and a.relnamespace=d.oid

pgtst_db-# ;

  schemaname  |     tablename     |       indexname        | indisunique | indisprimary | indisclustered | indisvalid | in

disready | indislive

--------------+-------------------+------------------------+-------------+--------------+----------------+------------+---

---------+-----------

 pgtst_schema | pgtst_tbl1_random | pgtst_tbl1_random_pkey | t           | t            | t              | t          | t

         | t

(1 row)

Postgresql: How to fix no sorted output of a table in postgresql

Objective: Perform a simple select (without any preference) and see how sorted the data is... if the default sort isnt good for you. How to fix it in postgresql?

Connection details:

pgtst_db=> \c
You are now connected to database "pgtst_db" as user "pgtst_usr".
pgtst_db=>

List of tables found in the test db:

pgtst_db=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

pgtst_db=> SET search_path TO pgtst_schema;
SET

pgtst_db=> show search_path;
 search_path
--------------
 pgtst_schema
(1 row)

pgtst_db=> \d+
                                           List of relations
    Schema    |    Name    | Type  |   Owner   | Persistence | Access method |    Size    | Description
--------------+------------+-------+-----------+-------------+---------------+------------+-------------
 pgtst_schema | PGTST_TBL1 | table | pgtst_usr | permanent   | heap          | 8192 bytes |
 pgtst_schema | pgtst_tbl1 | table | pgtst_usr | permanent   | heap          | 825 MB     |
(2 rows)
pgtst_db=>

pgtst_db=> select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='pgtst_schema' order by 1;
  schemaname  | tablename  | tableowner | hasindexes
--------------+------------+------------+------------
 pgtst_schema | pgtst_tbl1 | pgtst_usr  | t
 pgtst_schema | PGTST_TBL1 | pgtst_usr  | t
(2 rows)
pgtst_db=>

select relname table_name
,relnamespace schema_name
,reltype relation_type
,relowner owner
,relpages pages_or_blocks
,reltuples numrows
,relhasindex has_index
from pg_class
where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
   table_name    | schema_name | relation_type | owner | pages_or_blocks |   numrows    | has_index
-----------------+-------------+---------------+-------+-----------------+--------------+-----------
 PGTST_TBL1      |       24582 |         24592 | 16388 |               0 |           -1 | t
 PGTST_TBL1_pkey |       24582 |             0 | 16388 |               1 |            0 | f
 pgtst_tbl1      |       24582 |         24585 | 16388 |          105556 | 9.999327e+06 | t <<<< 105556*8192/1024/1024 => 824.65MB
 pgtst_tbl1_pkey |       24582 |             0 | 16388 |           27421 | 9.999327e+06 | f
(4 rows)
pgtst_db=>

Row sorted or not?

pgtst_db=> select * from pgtst_schema.pgtst_tbl1 limit 100 offset 100;
 id  |                                                name
-----+-----------------------------------------------------------------------------------------------------
 101 | T
 102 | TS
 103 | TST
 104 | CTST
 105 | CHTST
 106 | CHKTST
 107 | CHKCTST
 108 | CHKCHTST
 109 | CHKCHKTST
 110 | CHKCHKCTST
 111 | CHKCHKCHTST
 112 | CHKCHKCHKTST
 113 | CHKCHKCHKCTST
 114 | CHKCHKCHKCHTST
 115 | CHKCHKCHKCHKTST
 116 | CHKCHKCHKCHKCTST
 117 | CHKCHKCHKCHKCHTST
 118 | CHKCHKCHKCHKCHKTST
 119 | CHKCHKCHKCHKCHKCTST
 120 | CHKCHKCHKCHKCHKCHTST
 121 | CHKCHKCHKCHKCHKCHKTST
 122 | CHKCHKCHKCHKCHKCHKCTST
 123 | CHKCHKCHKCHKCHKCHKCHTST
 124 | CHKCHKCHKCHKCHKCHKCHKTST
 125 | CHKCHKCHKCHKCHKCHKCHKCTST
 126 | CHKCHKCHKCHKCHKCHKCHKCHTST
 127 | CHKCHKCHKCHKCHKCHKCHKCHKTST
 128 | CHKCHKCHKCHKCHKCHKCHKCHKCTST
 129 | CHKCHKCHKCHKCHKCHKCHKCHKCHTST
 130 | CHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 
Execution plan:
 
 pgtst_db-> select * from pgtst_schema.pgtst_tbl1 limit 100 offset 100;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Limit  (cost=2.06..4.11 rows=100 width=54)
   ->  Seq Scan on pgtst_tbl1  (cost=0.00..205549.27 rows=9999327 width=54)
(2 rows)
pgtst_db=>
 
>>>>> looks like the rows are sorted. I have used limit and offset togather to examine the row sorting nature after 100 rows. The way we loaded the data (using generate_series) ensured the records in sorted form.

But before we move to the next step to assess this row sorting, let us actually examine the rowaddress etc..

In oracle terms a scattered read or FTS is the equivalent form of a "Seq Scan" on postgresql db.

Now let us use a mehtod to collapse the row sort...

CREATE TABLE pgtst_tbl1_random AS SELECT *
FROM pgtst_tbl1
ORDER BY random();
 
Actual:

pgtst_db=> CREATE TABLE pgtst_tbl1_random AS SELECT *
pgtst_db-> FROM pgtst_tbl1
pgtst_db-> ORDER BY random();
SELECT 10000000
pgtst_db=> \d pgtst_tbl1_random
      Table "pgtst_schema.pgtst_tbl1_random"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 name   | text    |           |          |
pgtst_db=>
pgtst_db=>

We lost the index, let us create a primary key index on id column.

alter table pgtst_tbl1_random add primary key (id);

pgtst_db=> alter table pgtst_tbl1_random add primary key (id);
ALTER TABLE
pgtst_db=>
pgtst_db=> \d pgtst_tbl1_random
      Table "pgtst_schema.pgtst_tbl1_random"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           |          |
Indexes:
    "pgtst_tbl1_random_pkey" PRIMARY KEY, btree (id)
pgtst_db=>
pgtst_db-> where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
       table_name       | schema_name | relation_type | owner | pages_or_blocks |   numrows    | has_index
------------------------+-------------+---------------+-------+-----------------+--------------+-----------
 PGTST_TBL1             |       24582 |         24592 | 16388 |               0 |           -1 | t
 PGTST_TBL1_pkey        |       24582 |             0 | 16388 |               1 |            0 | f
 pgtst_tbl1             |       24582 |         24585 | 16388 |          105556 | 9.999327e+06 | t
 pgtst_tbl1_pkey        |       24582 |             0 | 16388 |           27421 | 9.999327e+06 | f
 pgtst_tbl1_random      |       24582 |         24599 | 16388 |          105786 |        1e+07 | t
 pgtst_tbl1_random_pkey |       24582 |             0 | 16388 |           27422 |        1e+07 | f
(6 rows)
pgtst_db=>

If you see the first data load is autovaccumed by the postgresql process.
Anyway we analyzed it one more time.

pgtst_db=> analyze pgtst_tbl1_random;
ANALYZE
pgtst_db=>

Let us query the table using limit and see how sorted is the data.

select * from pgtst_tbl1_random limit 100 rows;

pgtst_db=> select * from pgtst_tbl1_random limit 100;
   id    |                                                name
---------+-----------------------------------------------------------------------------------------------------
 6288820 | CHKCHKCHKCHKCHKCHTST
 6979227 | CHKCHKCHKCHKCHKCHKCHKCHKTST
 1395170 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 2664160 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 1690706 | CHKTST
 6525700 |
 4397957 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 1699603 | TST
 4810266 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 7223631 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 1136186 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 3626291 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 1698698 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
    7635 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 4468453 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 1401870 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 1091503 | TST
 6794916 | CHKCHKCHKCHKCTST
 9804103 | TST
 9133509 | CHKCHKTST
 6616994 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 8108546 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 3642179 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 2511005 | CHTST
 8485883 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 7165948 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 1888675 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 8336776 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 1526729 | CHKCHKCHKCHKCHKCHKCHKCHKCHTST
  252438 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST

>>>> the data is a mess.

pgtst_db=> explain select * from pgtst_tbl1_random limit 100;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit  (cost=0.00..2.06 rows=100 width=54)
   ->  Seq Scan on pgtst_tbl1_random  (cost=0.00..205785.22 rows=9999922 width=54)
(2 rows)
pgtst_db=>
Let us try to use index to see if the data is arriving as sorted....

explain select * from pgtst_tbl1_random where id < 100;
pgtst_db=> explain select * from pgtst_tbl1_random where id < 100;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on pgtst_tbl1_random  (cost=5.17..377.82 rows=95 width=54)
   Recheck Cond: (id < 100)
   ->  Bitmap Index Scan on pgtst_tbl1_random_pkey  (cost=0.00..5.15 rows=95 width=0)
         Index Cond: (id < 100)
(4 rows)
pgtst_db=>

We seem to use the index, which obviously will be in sorted order. Let us see the data...

pgtst_db=> select * from pgtst_tbl1_random where id < 100;
 id |                                                name
----+-----------------------------------------------------------------------------------------------------
 92 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 73 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 70 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 47 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 21 | CHKCHKCHKCHKCHKCHKTST
 51 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 11 | CHKCHKCHTST
 94 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 50 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
  4 | CTST
 86 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 15 | CHKCHKCHKCHKTST
 75 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 22 | CHKCHKCHKCHKCHKCHKCTST
 74 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 59 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 64 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 48 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 28 | CHKCHKCHKCHKCHKCHKCHKCHKCTST
  7 | CHKCTST
 66 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 83 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 97 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 40 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 69 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
 31 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
 39 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
  1 | T
 80 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
 60 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
pgtst_db=>

So the data isnt sorted, since we didnt really sort the output using order by clause. The query engine printed the records as when it was available.
Now the objective is to get this output sorted.....

cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;

Fired the above command around 1:22pm IST or as per the pg_stat_activity output it shows "query_start      | 2022-10-24 13:27:57.759631+05:30".

The time now - 01:50PM IST 24Oct2022. Looking at the pg_stat_activity, I see we have the command is still inpogress. Not sure how much is done and is left.

Fired another query in the meantime selecting the table pgtst_tbl1_random, the select query is stuck and it says is lock wait.

postgres=# \x on
Expanded display is on.

postgres=# select * from pg_stat_activity;
...
-[ RECORD 3 ]----+----------------------------------------------------------------
datid            | 16390
datname          | pgtst_db
pid              | 1776
leader_pid       |
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2022-10-24 13:44:37.650432+05:30
xact_start       | 2022-10-24 13:48:53.235688+05:30
query_start      | 2022-10-24 13:48:53.235688+05:30
state_change     | 2022-10-24 13:48:53.239728+05:30
wait_event_type  | Lock
wait_event       | relation
state            | active
backend_xid      |
backend_xmin     | 747
query_id         |
query            | select * from pgtst_schema.pgtst_tbl1_random limit 10;
backend_type     | client backend
-[ RECORD 4 ]----+----------------------------------------------------------------
datid            | 16390
datname          | pgtst_db
pid              | 1356
leader_pid       |
usesysid         | 16388
usename          | pgtst_usr
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2022-10-24 12:51:48.161825+05:30
xact_start       | 2022-10-24 13:27:57.759631+05:30
query_start      | 2022-10-24 13:27:57.759631+05:30
state_change     | 2022-10-24 13:27:57.759714+05:30
wait_event_type  | IO
wait_event       | DataFileRead
state            | active
backend_xid      | 747
backend_xmin     | 747
query_id         |
query            | cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
backend_type     | client backend
-[ RECORD 5 ]----+----------------------------------------------------------------
datid            | 5
datname          | postgres
pid              | 1850
leader_pid       |
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2022-10-24 13:49:52.123753+05:30
xact_start       | 2022-10-24 13:51:22.655539+05:30
query_start      | 2022-10-24 13:51:22.655539+05:30
state_change     | 2022-10-24 13:51:22.656856+05:30
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 747
query_id         |
query            | select * from pg_stat_activity;
backend_type     | client backend
.. [trimmed to show only necessary records]
postgres=#

pgtst_db=> cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
INFO:  clustering "pgtst_schema.pgtst_tbl1_random" using index scan on "pgtst_tbl1_random_pkey"
INFO:  "pgtst_schema.pgtst_tbl1_random": found 0 removable, 10000000 nonremovable row versions in 105786 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 181.25 s, system: 882.18 s, elapsed: 2869.87 s.
CLUSTER
pgtst_db=>

Nearly 1hr it took to transform 862MB of data. The query we ran in other session fetched the records in sorted order. Like we mentioned it held an exclusive lock and didnt allow even select statements on the table.

The poor part is there is no inbuild tool with postgresql to track the progress of the cluster operation. I have checked statio, stat, class tables, none of them showed any values changing.

pgtst_db=# select * from pgtst_schema.pgtst_tbl1_random limit 10;
 id |    name
----+------------
  1 | T
  2 | TS
  3 | TST
  4 | CTST
  5 | CHTST
  6 | CHKTST
  7 | CHKCTST
  8 | CHKCHTST
  9 | CHKCHKTST
 10 | CHKCHKCTST
(10 rows)
pgtst_db=#

Now the pg_stat_activity shows...

postgres=# select * from pg_stat_activity where pid in (1356,1850);
-[ RECORD 1 ]----+----------------------------------------------------------------
datid            | 16390
datname          | pgtst_db
pid              | 1356
leader_pid       |
usesysid         | 16388
usename          | pgtst_usr
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2022-10-24 12:51:48.161825+05:30
xact_start       |
query_start      | 2022-10-24 13:27:57.759631+05:30
state_change     | 2022-10-24 14:16:08.812693+05:30 <<<<<< not when active the timestamp matches the query start time
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      |
backend_xmin     |
query_id         |
query            | cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
backend_type     | client backend
-[ RECORD 2 ]----+----------------------------------------------------------------
datid            | 5
datname          | postgres
pid              | 1850
leader_pid       |
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2022-10-24 13:49:52.123753+05:30
xact_start       | 2022-10-24 14:19:27.687243+05:30
query_start      | 2022-10-24 14:19:27.687243+05:30
state_change     | 2022-10-24 14:19:27.688594+05:30
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 748
query_id         |
query            | select * from pg_stat_activity where pid in (1356,1850);
backend_type     | client backend
postgres=#

Let us analyze the table and check the explain output:

pgtst_db=> analyze verbose pgtst_tbl1_random;
INFO:  analyzing "pgtst_schema.pgtst_tbl1_random"
INFO:  "pgtst_tbl1_random": scanned 30000 of 105883 pages, containing 2833232 live rows and 0 dead rows; 30000 rows in sample, 9999703 estimated total rows
ANALYZE
pgtst_db=>

pgtst_db=# explain select * from pgtst_schema.pgtst_tbl1_random limit 10;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit  (cost=0.00..0.21 rows=10 width=54)
   ->  Seq Scan on pgtst_tbl1_random  (cost=0.00..205880.03 rows=9999703 width=54)
(2 rows)
pgtst_db=#
pgtst_db=> select relname table_name
pgtst_db-> ,relnamespace schema_name
pgtst_db-> ,reltype relation_type
pgtst_db-> ,relowner owner
pgtst_db-> ,relpages pages_or_blocks
pgtst_db-> ,reltuples numrows
pgtst_db-> ,relhasindex has_index
pgtst_db-> from pg_class
pgtst_db-> where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
       table_name       | schema_name | relation_type | owner | pages_or_blocks |   numrows    | has_index
------------------------+-------------+---------------+-------+-----------------+--------------+-----------
 PGTST_TBL1             |       24582 |         24592 | 16388 |               0 |           -1 | t
 PGTST_TBL1_pkey        |       24582 |             0 | 16388 |               1 |            0 | f
 pgtst_tbl1             |       24582 |         24585 | 16388 |          105556 | 9.999327e+06 | t
 pgtst_tbl1_pkey        |       24582 |             0 | 16388 |           27421 | 9.999327e+06 | f
 pgtst_tbl1_random      |       24582 |         24599 | 16388 |          105883 | 9.999703e+06 | t
 pgtst_tbl1_random_pkey |       24582 |             0 | 16388 |           27422 | 9.999703e+06 | f
(6 rows)
pgtst_db=>

<<<< notice not much change in statistics.  The number of blocks increased a bit (100+)
As such as cluster operation wasnt trackable, but after the operation is finished the necessary tables were updated.

pgtst_db=# select * from pg_stat_user_tables where schemaname='pgtst_schema';
 relid |  schemaname  |      relname      | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n
_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum |         last
_autovacuum          |           last_analyze           |         last_autoanalyze         | vacuum_count | autovacuum_cou
nt | analyze_count | autoanalyze_count
-------+--------------+-------------------+----------+--------------+----------+---------------+-----------+-----------+--
---------+---------------+------------+------------+---------------------+--------------------+-------------+-------------
---------------------+----------------------------------+----------------------------------+--------------+---------------
---+---------------+-------------------
 24597 | pgtst_schema | pgtst_tbl1_random |        8 |     30000120 |        4 |      10000099 |  10000000 |         0 |
       0 |             0 |    9999703 |          0 |                   0 |                  0 |             | 2022-10-24 1
3:17:50.696014+05:30 | 2022-10-24 14:26:57.748114+05:30 | 2022-10-24 13:17:54.87431+05:30  |            0 |
 1 |             2 |                 1
 24590 | pgtst_schema | PGTST_TBL1        |        1 |            0 |        0 |             0 |         0 |         0 |
       0 |             0 |          0 |          0 |                   0 |                  0 |             |
                     |                                  |                                  |            0 |
 0 |             0 |                 0
 24583 | pgtst_schema | pgtst_tbl1        |        7 |     10000413 |        0 |             0 |  10000000 |         0 |
       0 |             0 |    9999327 |          0 |                   0 |                  0 |             | 2022-10-23 2
1:48:25.73182+05:30  | 2022-10-23 23:09:11.140771+05:30 | 2022-10-23 21:48:29.010948+05:30 |            0 |
 1 |             2 |                 1
(3 rows)
pgtst_db=#

pgtst_db=> \d+

                                               List of relations

    Schema    |       Name        | Type  |   Owner   | Persistence | Access method |    Size    | Description

--------------+-------------------+-------+-----------+-------------+---------------+------------+-------------

 pgtst_schema | PGTST_TBL1        | table | pgtst_usr | permanent   | heap          | 8192 bytes |

 pgtst_schema | pgtst_tbl1        | table | pgtst_usr | permanent   | heap          | 825 MB     |

 pgtst_schema | pgtst_tbl1_random | table | pgtst_usr | permanent   | heap          | 827 MB     |

(3 rows)

pgtst_db=>

This closes this blog.

Thank you!