Posted by Kyle Hankinson August 14, 2020
Need to find out how many rows, or how much data your PostgreSQL tables are consuming? Possibly, looking to find out what the largest tables in the database are? There are a couple of options available depending on your needs.
Finding out how many rows are in a single table is the easiest task. Simply run the query:
SELECT COUNT(1) FROM <TABLE>
You will get a single row result which contains the number of rows:from the specified table.
+----------+ | count(1) | +----------+ | 7 | +----------+
Counting rows from multiple tables at once is a bit more difficult. You can get an estimate by using the following query:
SELECT schemaname as table_schema, relname as table_name, n_live_tup as row_count FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
A more accurate way to get exact row counts courtesy of stackoverflow would be:
select table_schema, table_name, (xpath('/row/cnt/text()', xml_count))::text::int as row_count from ( select table_name, table_schema, query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count from information_schema.tables where table_schema = 'public' --<< change here for the schema you want ) t order by 3 DESC
Using this second method will take longer and be more likely to slow-down other database operations, however the count will be more accurate.
Sometimes row count might not be exactly what you are looking for. If you need to find out how large a table is in bytes, the
pg_relation_size method will come to your help. Finding the size for all tables would be:
SELECT schemaname, tablename, pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) FROM pg_tables ORDER BY 1, 2
Which will give the schema, table and pretty printed size for each table.