Subdirectory containing WAL (Write Ahead Log) files Subdirectory containing state files for prepared transactions Subdirectory containing symbolic links to tablespaces Subdirectory containing subtransaction status data Subdirectory containing temporary files for the statistics subsystem Subdirectory containing permanent files for the statistics subsystem Subdirectory containing exported snapshots Subdirectory containing information about committed serializable transactions Subdirectory containing replication slot data Subdirectory containing LISTEN/NOTIFY status data Subdirectory containing multitransaction status data (used for shared row locks) Subdirectory containing status data for logical decoding Subdirectory containing files used by the dynamic shared memory subsystem Subdirectory containing transaction commit timestamp data Subdirectory containing cluster-wide tables, such as pg_database Subdirectory containing per-database subdirectoriesįile recording the log file(s) currently written to by the logging collector In this tutorial, you have learned various handy functions to get the size of a database, a table, indexes, a tablespace, and a value.A file containing the major version number of PostgreSQL To find how much space that needs to store a specific value, you use the pg_column_size() function, for examples: select pg_column_size( 5:: smallint) The statement returns the following output: pg_size_prettyĬode language: SQL (Structured Query Language) ( sql ) PostgreSQL value size The following statement returns the size of the pg_default tablespace: SELECT The pg_tablespace_size() function accepts a tablespace name and returns the size in bytes. To get the size of a tablespace, you use the pg_tablespace_size() function. Pg_size_pretty (pg_indexes_size( 'actor')) Ĭode language: SQL (Structured Query Language) ( sql ) PostgreSQL tablespace size The pg_indexes_size() function accepts the OID or table name as the argument and returns the total disk space used by all indexes attached of that table.įor example, to get the total size of all indexes attached to the film table, you use the following statement: SELECT To get total size of all indexes attached to a table, you use the pg_indexes_size() function. Pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database Code language: SQL (Structured Query Language) ( sql ) datname | size To get the size of each database in the current database server, you use the following statement: SELECT The statement returns the following result: pg_size_pretty For example, the following statement returns the size of the dvdrental database: SELECT To get the size of the whole database, you use the pg_database_size() function. Here is the output: relation | total_sizeĬode language: SQL (Structured Query Language) ( sql ) PostgreSQL database size relkind 'i' AND nspname !~ '^pg_toast' ORDER BY You can use the pg_total_relation_size() function to find the size of biggest tables including indexes.įor example, the following query returns top 5 biggest tables in the dvdrental database: SELECT The following shows the output: pg_size_pretty For example, to get the total size of the actor table, you use the following statement: SELECT To get the total size of a table, you use the pg_total_relation_size() function. The pg_relation_size() function returns the size of the table only, not included indexes or additional objects. The following is the output in kB pg_size_pretty Pg_size_pretty (pg_relation_size( 'actor')) The pg_size_pretty() function takes the result of another function and format it using bytes, kB, MB, GB or TB as appropriate. To make the result more human readable, you use the pg_size_pretty()function. The pg_relation_size() function returns the size of a specific table in bytes: pg_relation_sizeĬode language: SQL (Structured Query Language) ( sql ) For example, you can get the size of the actor table in the dvdrental sample database as follows: select pg_relation_size( 'actor') Code language: SQL (Structured Query Language) ( sql ) To get the size of a specific table, you use the pg_relation_size() function. Summary: in this tutorial, you will learn how to get the size of the databases, tables, indexes, tablespace using some handy functions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |