Postgresql: Example Table & Index creation

Postgresql: Example Table & Index creation:

Since we dont have any schema by the name of the user, we prefix the table_name with schema name.

Current search order is

pgtst_db=> SELECT reset_val FROM pg_settings WHERE name='search_path';

    reset_val
-----------------
 "$user", public
(1 row)
pgtst_db=>

So we better prefix the schema name for the table

Query:

create table pgtst_schema.pgtst_tbl1
(
id int PRIMARY KEY
,name text
);

Actual output:
pgtst_db=> create table pgtst_schema.pgtst_tbl1
pgtst_db-> (
pgtst_db(> id int PRIMARY KEY
pgtst_db(> ,name text
pgtst_db(> );
CREATE TABLE

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

Now load the data:

INSERT INTO pgtst_schema.pgtst_tbl1
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (1,10000000) s(i);

Actual output:

pgtst_db=> INSERT INTO pgtst_schema.pgtst_tbl1
pgtst_db-> SELECT i, lpad('TST',mod(i,100),'CHK')
pgtst_db-> FROM generate_series (1,10000000) s(i);
INSERT 0 10000000
pgtst_db=> commit;
WARNING:  there is no transaction in progress
COMMIT
pgtst_db=> select * from pgtst_schema.pgtst_tbl1 limit 4;
 id | name
----+------
  1 | T
  2 | TS
  3 | TST
  4 | CTST
(4 rows)
pgtst_db=>

Looks like this form of insert autocommits the data. After a session disconnect and reconnect,the records stayed intact.
Now as a next step, let us try create the same table in upper case.. see what happens.

create table pgtst_schema.PGTST_TBL1
(
id int PRIMARY KEY
,name text
);

Actual output:
pgtst_db=> create table pgtst_schema.PGTST_TBL1
pgtst_db-> (
pgtst_db(> id int PRIMARY KEY
pgtst_db(> ,name text
pgtst_db(> );
ERROR:  relation "pgtst_tbl1" already exists
pgtst_db=>

Let us see what happens with a double quote:
create table pgtst_schema."PGTST_TBL1"
(
id int PRIMARY KEY
,name text
);
Actual output:
pgtst_db=> create table pgtst_schema."PGTST_TBL1"
pgtst_db-> (
pgtst_db(> id int PRIMARY KEY
pgtst_db(> ,name text
pgtst_db(> );
CREATE TABLE
pgtst_db=>
pgtst_db=> \d pgtst_schema.PGTST_TBL1
          Table "pgtst_schema.pgtst_tbl1" <<<<<<<<<< this is the old table created asis
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           |          |
Indexes:
    "pgtst_tbl1_pkey" PRIMARY KEY, btree (id)
pgtst_db=> \d pgtst_schema."PGTST_TBL1" <<<<<<<<<< this is the table created in upper case using double quote
          Table "pgtst_schema.PGTST_TBL1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           |          |
Indexes:
    "PGTST_TBL1_pkey" PRIMARY KEY, btree (id) <<<<<<<<<<< notice primary key is also presented in the same way as table in upper case
pgtst_db=>

To report tables created in 1 schema in 1 db (remember we can only search within 1 db at a time).
Query:
select relname table_name
,relnamespace schema_name
,relowner owner
,reltablespace tablespace_name
,relpages pages_or_blocks
,reltuples numrows
,relhasindex
,relispartition
from pg_class where relnamespace=(select oid from pg_catalog.pg_namespace where nspname='pgtst_schema');

Actuals:

pgtst_db=> select relname table_name
,relnamespace schema_name
,relowner owner
,reltablespace tablespace_name
,relpages pages_or_blocks
,reltuples numrows
,relhasindex
,relispartition
from pg_class where relnamespace=(select oid from pg_catalog.pg_namespace where nspname='pgtst_schema');
   table_name    | schema_name | owner | tablespace_name | pages_or_blocks |   numrows    | relhasindex | relispartition
-----------------+-------------+-------+-----------------+-----------------+--------------+-------------+----------------
 pgtst_tbl1      |       24582 | 16388 |               0 |          105556 | 9.999805e+06 | t           | f
 pgtst_tbl1_pkey |       24582 | 16388 |               0 |           27421 | 9.999805e+06 | f           | f
 PGTST_TBL1      |       24582 | 16388 |               0 |               0 |           -1 | t           | f
 PGTST_TBL1_pkey |       24582 | 16388 |               0 |               1 |            0 | f           | f
(4 rows)
pgtst_db=>

Looks like the load process completed the statistics already (autovaccum is run on first load to a table). See the empty table has no stats vs the load operation conducted has stats (1E7 records loaded and is very close in stats).

To further check stats:

select * from pg_stats where tablename = 'pgtst_tbl1' and attname = 'id';
Output before analyze:
pgtst_db=> select * from pg_stats where tablename = 'pgtst_tbl1' and attname = 'id';
  schemaname  | tablename  | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_fre
qs |

                            histogram_bounds

                                                                     | correlation | most_common_elems | most_common_elem_
freqs | elem_count_histogram
--------------+------------+---------+-----------+-----------+-----------+------------+------------------+----------------
---+----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------+-------------+-------------------+------------------
------+----------------------
 pgtst_schema | pgtst_tbl1 | id      | f         |         0 |         4 |         -1 |                  |
   | {156,104830,204915,305656,402850,508047,603642,712567,820443,914049,999382,1107188,1197709,1300843,1404060,1498751,16
13690,1717426,1812051,1902599,2003833,2108384,2208139,2308392,2407809,2500963,2600916,2709441,2811190,2917441,3020007,3113
697,3208785,3308437,3394937,3500984,3609057,3705837,3800867,3898775,3985270,4077592,4169467,4274066,4396954,4496672,459106
6,4710673,4804941,4904091,5007196,5096640,5198073,5301897,5403772,5506418,5599459,5685302,5784512,5895119,5999010,6093677,
6193182,6295391,6390728,6506512,6618820,6714824,6813310,6909462,7014543,7114580,7214750,7318266,7427030,7528108,7620389,77
19166,7814411,7902081,7996622,8095802,8201862,8292996,8388931,8489376,8593882,8694557,8792601,8887583,9003811,9093857,9191
572,9296306,9392551,9491007,9589388,9692515,9791524,9890398,9998508} |           1 |                   |
      |
(1 row)

Analyze command:

pgtst_db=> analyze pgtst_schema.pgtst_tbl1;
ANALYZE
pgtst_db=>
Output of pg_stats after stats update:
pgtst_db=> select * from pg_stats where tablename = 'pgtst_tbl1' and attname = 'id';
  schemaname  | tablename  | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_fre
qs |

                            histogram_bounds

                                                                    | correlation | most_common_elems | most_common_elem_f
reqs | elem_count_histogram
--------------+------------+---------+-----------+-----------+-----------+------------+------------------+----------------
---+----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------+-------------+-------------------+-------------------
-----+----------------------
 pgtst_schema | pgtst_tbl1 | id      | f         |         0 |         4 |         -1 |                  |
   | {962,89122,182629,281109,388193,483705,595095,688657,784427,882124,985113,1095191,1201347,1296235,1406058,1505579,160
8926,1712622,1811258,1913458,2012009,2109742,2195749,2293766,2387321,2503078,2609918,2695100,2796928,2894805,2991428,30905
24,3184159,3294107,3390983,3488821,3588802,3694456,3796281,3893544,3994781,4077203,4169803,4272344,4377049,4479760,4576868
,4679781,4787542,4878849,4980333,5076423,5173340,5276335,5379567,5471970,5569825,5672016,5772583,5870065,5971384,6075868,6
162299,6268973,6367768,6475449,6568573,6656837,6747744,6847117,6950795,7052269,7162410,7256444,7353972,7451009,7539941,763
4880,7748513,7854942,7951443,8061007,8170763,8269423,8368302,8478166,8576951,8678119,8794915,8896174,9000840,9101029,92002
29,9306729,9405118,9506494,9603294,9705481,9806997,9898911,9999988} |           1 |                   |
     |
(1 row)
pgtst_db=>

See only the histogram bounds have changed.

To check the volume of changes happened in a table since last analyze or vaccum along with last analyze or vaccum timestamp:

pgtst_db=> select 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_count,analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='pgtst_schema' order by 2;
  schemaname  |  relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tu
p_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_count | analyze_cou
nt | autoanalyze_count
--------------+------------+----------+--------------+----------+---------------+-----------+-----------+-----------+-----
----------+------------+------------+---------------------+--------------------+-------------+----------------------------
-----+----------------------------------+----------------------------------+--------------+------------------+------------
---+-------------------
 pgtst_schema | PGTST_TBL1 |        1 |            0 |        0 |             0 |         0 |         0 |         0 |
        0 |          0 |          0 |                   0 |                  0 |             |
     |                                  |                                  |            0 |                0 |
 0 |                 0
 pgtst_schema | pgtst_tbl1 |        5 |          213 |        0 |             0 |  10000000 |         0 |         0 |
        0 |    9998310 |          0 |                   0 |                  0 |             | 2022-10-23 21:48:25.73182+0
5:30 | 2022-10-23 22:35:50.786447+05:30 | 2022-10-23 21:48:29.010948+05:30 |            0 |                1 |
 1 |                 1
(2 rows)
pgtst_db=>

pg_statistics, is one other postgres view for checking table stats update status.

Thanks