Changes between Version 17 and Version 18 of Performance

Timestamp:

Nov 4, 2013, 1:55:39 PM (11 years ago)

Author:

miracee

Comment:

—

Performance

@@ -110 +110 @@
 }}}
 
+== PostgreSQL storage ==
+
+First you need to get the object identifier for your database:
+{{{
+SELECT oid, datname FROM pg_database;
+}}}
+
+* When you look into the PostgreSQL data directory you will find the global directory. 
+* When you look into the global directory you will find one directory for each database named by the database oid.
+* When you look into the database directory you will find one file for each table and index. 
+* If the table or index exceed 1 GB it will be split into more files and you will find one file for each GB.
+* Table and index files are named by filenodes. 
+* Table and index filenodes can be different their object identifiers.
+
+PostgreSQL use 8 kB pages for storing data in tables and indexes.
+
+More informations about the size of your tables you can get by looking into pg_class.
+
+Consider, that for getting correct data, ANALYZE should have run before.
+
+{{{
+SELECT relkind, relfilenode, oid, relname, reltuples, relpages, pg_size_pretty(relpages * 1024 * 8) as size FROM pg_class WHERE …;
+}}}
+
+* relkind i means index; r means relation which is table here.
+* reltuple tells you how much tuple / rows exist in the table or index.
+* relpages tells you how much pages are allocated for the table or index.
+
+* In PostgreSQL version < 9.2 pg_size_pretty(INTEGER) will overflow when size is more then round about 2 GB because it exceed 4 byte INTEGER.
+* In PostgreSQL version >= 9.2 you need to cast relpages to NUMERIC because the function changed to pg_size_pretty(NUMERIC).
+
+
 == Size of RASBASE tables ==
 {{{