Skip to main content

How to check database schema size and count objects

How can I check the size of my database schemas and count the number of objects in each schema?

Answer​

Atlas provides programmatic capabilities that allow you to customize inspection output using Go templates. This lets you generate custom reports about your database schema, including counting schemas, tables, views, functions, and other database objects.

By configuring a custom format template in your atlas.hcl file, you can use atlas schema inspect to generate detailed statistics about your database structure, such as the total number of objects per schema and the overall database size.

Example Configuration​

Add a format block to your environment configuration in atlas.hcl:

atlas.hcl
env "schema-size" {
  url = "<DATABASE-URL>"
  format {
    schema {
      //language=gotemplate
      inspect = <<-EOT
        {{- /* Count number of database objects, such as extensions and schemas. */}}
        {{- $size := add (len .Realm.Objects) (len .Realm.Schemas) }}
        {{- range .Realm.Schemas }}
          {{- $schemaSize := add (len .Tables) (len .Views) (len .Funcs) (len .Procs) (len .Objects) }}
          {{- /* Add the number of triggers in each table to the schema size. */}}
          {{- range .Tables }}
            {{- $schemaSize = add $schemaSize (len .Triggers) }}
          {{- end }}
          {{- /* Print the size of the current schema. */}}
          {{- printf "Schema %q size: %d objects.\n" .Name $schemaSize -}}
          {{- $size = add $size $schemaSize }}
        {{- end }}
        {{- /* Print the total size of the database. */}}
        {{- printf "Total database size: %d objects.\n" $size -}}
      EOT
    }
  }
}

Usage​

Run the inspection command with your configured environment:

atlas schema inspect --env schema-size

Example Output​

The command will output statistics for each schema and the total database size:

Schema "manufacturing" size: 45 objects.
Schema "public" size: 120 objects.
Total database size: 167 objects.

What's Being Counted​

The template counts various database objects to calculate schema and database size:

  • Schema-level objects: Tables, views, functions, procedures, and other schema objects
  • Table-level objects: Triggers associated with each table
  • Database-level objects: Extensions and other database-level objects

Learn More​

For more information on customizing inspection output, see: