Disk Usage

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Notes about TOAST size computations)
(add section to find large databases in a cluster)
Line 2: Line 2:
 
== Finding the size of various object in your database ==
 
== Finding the size of various object in your database ==
 
* [http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html table size, database size]
 
* [http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html table size, database size]
 +
 +
== Finding the largest databases in your cluster ==
 +
 +
 +
<source lang="sql">
 +
SELECT d.datname as Name,  pg_catalog.pg_get_userbyid(d.datdba) as Owner,
 +
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
 +
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
 +
        ELSE 'No Access'
 +
    END as Size
 +
FROM pg_catalog.pg_database d
 +
    order by
 +
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
 +
        THEN pg_catalog.pg_database_size(d.datname)
 +
        ELSE NULL
 +
    END desc nulls first
 +
    LIMIT 20
 +
</source>
  
 
== Finding the size of your biggest relations ==
 
== Finding the size of your biggest relations ==
Line 9: Line 27:
 
Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts.  Tables which have both regular and [http://www.postgresql.org/docs/current/static/storage-toast.html TOAST] pieces will be broken out into separate components; an example showing you might include those into the main total is available in the [http://www.postgresql.org/docs/current/static/disk-usage.html documentation], and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:
 
Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts.  Tables which have both regular and [http://www.postgresql.org/docs/current/static/storage-toast.html TOAST] pieces will be broken out into separate components; an example showing you might include those into the main total is available in the [http://www.postgresql.org/docs/current/static/disk-usage.html documentation], and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:
  
Note that all of the queries on this page only show you the totals for the database you're currently connected to.
+
Note that all of the queries below this point on this page only show you the totals for the database you're currently connected to.
  
 
<source lang="sql">
 
<source lang="sql">

Revision as of 17:12, 7 November 2012

Finding the size of various object in your database

Finding the largest databases in your cluster

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS Size
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC nulls first
    LIMIT 20

Finding the size of your biggest relations

Performance Snippets

Disk usage

Works with PostgreSQL

>=8.1

Written in

SQL

Depends on

Nothing


Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. Tables which have both regular and TOAST pieces will be broken out into separate components; an example showing you might include those into the main total is available in the documentation, and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:

Note that all of the queries below this point on this page only show you the totals for the database you're currently connected to.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

Example output (from a database created with pgbench, scale=25):

        relation        |    size    
------------------------+------------
 public.accounts        | 326 MB
 public.accounts_pkey   | 44 MB
 public.history         | 592 kB
 public.tellers_pkey    | 16 kB
 public.branches_pkey   | 16 kB
 public.tellers         | 16 kB
 public.branches        | 8192 bytes

Finding the total size of your biggest tables

This version of the query uses pg_total_relation_size, which sums total disk space used by the table including indexes and toasted data rather than breaking out the individual pieces:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Sizes before 8.1

The pg_relation_size functions were introduced in PostgreSQL 8.1. In earlier versions, the following query can be used instead, returning the size in megabytes:

SELECT 
  relname, (relpages * 8) / 1024 AS size_mb
  FROM pg_class ORDER BY relpages DESC LIMIT 20;

You'll need to account for TOAST yourself here. Bear in mind also that relpages is only up-to-date as of the last VACUUM or ANALYZE on the particular table.

Sizes in 8.4 and later

In 8.4, pg_relation_size was changed to use the regclass type, which means that pg_relation_size(data_type_name) no longer works.

Easy access to these queries

~/.psqlrc tricks: table sizes shows how to make it easy to run queries like this in psql

Personal tools