What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)

by

For a random table:

# select 
  pg_relation_size(20306, 'main') as main,
  pg_relation_size(20306, 'fsm') as fsm,
  pg_relation_size(20306, 'vm') as vm,
  pg_relation_size(20306, 'init') as init,
  pg_table_size(20306), 
  pg_indexes_size(20306) as indexes,
  pg_total_relation_size(20306) as total;
  main  |  fsm  |  vm  | init | pg_table_size | indexes |  total 
--------+-------+------+------+---------------+---------+--------
 253952 | 24576 | 8192 |    0 |        286720 |  196608 | 483328
(1 row)

From that, you can tell pg_table_size is the sum of all the return values of pg_relation_size. And pg_total_relation_size is the sum of pg_table_size and pg_indexes_size.

If you want to know how much space your tables are using, use pg_table_size and pg_total_relation_size to think about them — one number is table-only, and one number is table + indexes.

Check the storage file layout for some info about what fsm, vm, and init mean, and how they’re stored on disk.

More Related Contents:

  1. How to reset postgres’ primary key sequence when it falls out of sync?
  2. PostgreSQL LIKE query performance variations
  3. How to import CSV file data into a PostgreSQL table
  4. The forgotten assignment operator “=” and the commonplace “:=”
  5. Equivalent to unpivot() in PostgreSQL
  6. Passing user id to PostgreSQL triggers
  7. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  8. I forgot the password I entered during postgres installation
  9. Do nullable columns occupy additional space in PostgreSQL?
  10. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  11. How do I do large non-blocking updates in PostgreSQL?
  12. How to configure PostgreSQL to accept all incoming connections
  13. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  14. Disable PostgreSQL foreign key checks for migrations
  15. Prevent recursive trigger in PostgreSQL
  16. Spark Dataframes UPSERT to Postgres Table
  17. PostgreSQL Error: Relation already exists
  18. Using row_to_json() with nested joins
  19. Optimize Postgres query on timestamp range
  20. Use variable set by psql meta-command inside of DO block
  21. pgAdmin III Why query results are shortened?
  22. How do I handle opening/closing Db connection in a Go app?
  23. How do I alter the date format in Postgres?
  24. OpenShift: How to connect to postgresql from my PC
  25. clojure.java.jdbc lazy query
  26. Alternate output format for psql showing one column per line with column name
  27. Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
  28. Is it possible to change the natural order of columns in Postgres?
  29. ERROR: could not stat file “XX.csv”: Unknown error
  30. SELECT or INSERT a row in one command

Leave a Comment