TransWikia.com

Table size and row count

Database Administrators Asked on December 31, 2020

My database is in TB with different schemas. I would like to search in each schema following information:

  • Schema name
  • Table name
  • Table size
  • Rows count
  • Table Created date

Where table name contains: *.bak or dropped_* or *_drop

One Answer

The tables appear as entries in pg_catalog.pg_class that have relkind='r' (see the documentation).

  • table name: pg_class.relname

  • schema name: pg_namespace.nspname. Join pg_class.relnamespace to pg_namespace.oid to follow the association between the table and its schema.

  • table size: see pg_relation_size(pg_class.oid)

  • row count: pg_class.reltuples gives an estimate. For an exact number, run select count(*) from the table.

  • created date: the creation date is not recorded by postgres.

Some of these are also available through views in information_schema if you prefer. The structures of the views in this schema are dictated by the SQL standard, whereas the pg_catalog tables are postgres-specific.

Example of query

Starting from the query provided as a comment:

  select nspname as schema, relname as tablename, 
  reltuples as rowcounts
  from pg_class c JOIN pg_catalog.pg_namespace n
   ON n.oid = c.relnamespace where relkind='r'
   and relname like '%_drop%'
   order by nspname, reltuples desc

remarks:

  • The underscore character is a wildcard for the LIKE operator (it matches any character), so it needs to be quoted to be interpreted literally. By default, the quote character is backslash.

  • pg_relation_size() as a function call can be introduced directly in the select list.

Amended query:

  select
    nspname as schema,
    relname as tablename, 
    reltuples as rowcounts,
    pg_relation_size(c.oid) AS tablesize
  from pg_class c JOIN pg_catalog.pg_namespace n
   ON n.oid = c.relnamespace where relkind='r'
   and relname like '%_drop%'
   order by nspname, reltuples desc

Correct answer by Daniel Vérité on December 31, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP