SELECT schemaname,
       tablename,
       dead_tuple_count(stats) AS dead_tuples,
       pg_size_pretty(dead_tuple_len(stats)) AS dead_space,
       pg_size_pretty(free_space(stats)) AS free_space,
       last_autovacuum,
       last_autoanalyze
FROM
  (SELECT pgt.schemaname AS schemaname,
          pgt.tablename AS tablename,
          pgstattuple(pgt.schemaname || '.' || pgt.tablename) 
          AS stats,
          uts.last_autovacuum AS last_autovacuum,
          uts.last_autoanalyze AS last_autoanalyze
   FROM pg_tables AS pgt
   LEFT JOIN pg_stat_user_tables 
        AS uts 
        ON pgt.schemaname = uts.schemaname
   AND pgt.tablename = uts.relname
   WHERE pgt.schemaname NOT IN ('repack','pg_catalog')) AS r
ORDER BY dead_tuples DESC;

SELECT schemaname,
       indexname,
       tablename,
       dead_tuple_count(stats) AS dead_tuples,
       pg_size_pretty(dead_tuple_len(stats)) AS dead_space,
       pg_size_pretty(free_space(stats)) AS free_space
FROM
  (SELECT pgt.schemaname AS schemaname,
          pgt.indexname AS indexname,
          pgt.tablename AS tablename,
          pgstattuple(pgt.schemaname || '.' || pgt.indexname) AS stats
   FROM pg_indexes AS pgt
   WHERE pgt.schemaname NOT IN ('repack',
                                'pg_catalog')
     AND pgt.indexname NOT IN ('some',
                               'important',
                               'triggers')) AS r
ORDER BY dead_tuples DESC;

create table books(k serial primary key, doc jsonb not null);

insert into books(doc) values
  ('
    { "ISBN"    : 4582546494267,
      "noscript"   : "Macbeth",
      "author"  :
        {"given_name": "William",
         "family_name": "Shakespeare"},
      "year"    : 1623
    }
  ');

alter table books
add constraint books_doc_is_object
check(
  jsonb_typeof(doc) is not null and
  jsonb_typeof(doc) = 'object'
);

alter table books
add constraint books_doc_isbn_ok
check(
  doc->>'ISBN' is not null              and
  jsonb_typeof(doc->'ISBN') = 'number'  and
  (doc->>'ISBN')::bigint > 0            and
  length(doc->>'ISBN') = 13
);

SELECT DISTINCT
    a.amname
FROM
    pg_type t
    JOIN pg_opclass o ON o.opcintype = t.oid
    JOIN pg_am a ON a.oid = o.opcmethod
WHERE
    t.typname = 'int4';
 
 amname 
--------
 btree
 hash
 brin
(3 rows)