Postgresql List and Order Tables by Size

postgresql list and order tables by size

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2

This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

List of all object size functions in the manual.

list table sizes for all tables in a postgresql database

Combine your query using a CTE and join it with the one posted on this answer, as @a_horse_with_no_name suggested, e.g.

WITH j AS (
  SELECT table_schema, table_name, count(*) AS count_columns
  FROM information_schema.columns
  GROUP BY table_schema, table_name
) 
SELECT 
  nspname AS schemaname,relname,reltuples,count_columns
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
JOIN j ON j.table_name = relname AND j.table_schema = nspname 
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

How to list all indexes of a table with their corresponding size in PostgreSQL?

Use pg_indexes.

select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';

get all table's name and its disk size in PostgreSQL

its better to use pg_size_pretty - Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units

for example

select pg_size_pretty(pg_relation_size('public.audit_14'))

Result : 1624 kB

and the answer for your question is,

select 
    schemaname,
    relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname ||'.'||relname)) as table_size 
from 
(
  select 
      schemaname, 
      relname 
  from pg_stat_user_tables order by relname 
) t

How do you find the disk size of a Postgres / PostgreSQL table and its indexes

Try the Database Object Size Functions. An example:

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));

For all tables, something along the lines of:

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

Edit: Here's the query submitted by @phord, for convenience:

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

I've modified it slightly to use pg_table_size() to include metadata and make the sizes add up.

How do you find the row count for all your tables in Postgres

There's three ways to get this sort of count, each with their own tradeoffs.

If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can only be relative to some transaction. You're getting a count of what that transaction sees at the point in time when it executes. You could automate this to run against every table in the database, but you probably don't need that level of accuracy or want to wait that long.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

The second approach notes that the statistics collector tracks roughly how many rows are "live" (not deleted or obsoleted by later updates) at any time. This value can be off by a bit under heavy activity, but is generally a good estimate:

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

That can also show you how many rows are dead, which is itself an interesting number to monitor.

The third way is to note that the system ANALYZE command, which is executed by the autovacuum process regularly as of PostgreSQL 8.3 to update table statistics, also computes a row estimate. You can grab that one like this:

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

Which of these queries is better to use is hard to say. Normally I make that decision based on whether there's more useful information I also want to use inside of pg_class or inside of pg_stat_user_tables. For basic counting purposes just to see how big things are in general, either should be accurate enough.

list of schema with sizes (relative and absolute) in a PostgreSQL database

Try this:

SELECT schema_name, 
       sum(table_size),
       (sum(table_size) / database_size) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size,
         sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size

Edit: just noticed the workaround with summing up all tables to get the database size is not necessary:

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

---

Related Topics