Changes between Version 17 and Version 18 of Performance


Ignore:
Timestamp:
Nov 4, 2013, 1:55:39 PM (11 years ago)
Author:
miracee
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • Performance

    v17 v18  
    110110}}}
    111111
     112== PostgreSQL storage ==
     113
     114First you need to get the object identifier for your database:
     115{{{
     116SELECT oid, datname FROM pg_database;
     117}}}
     118
     119* When you look into the PostgreSQL data directory you will find the global directory.
     120* When you look into the global directory you will find one directory for each database named by the database oid.
     121* When you look into the database directory you will find one file for each table and index.
     122* If the table or index exceed 1 GB it will be split into more files and you will find one file for each GB.
     123* Table and index files are named by filenodes.
     124* Table and index filenodes can be different their object identifiers.
     125
     126PostgreSQL use 8 kB pages for storing data in tables and indexes.
     127
     128More informations about the size of your tables you can get by looking into pg_class.
     129
     130Consider, that for getting correct data, ANALYZE should have run before.
     131
     132{{{
     133SELECT relkind, relfilenode, oid, relname, reltuples, relpages, pg_size_pretty(relpages * 1024 * 8) as size FROM pg_class WHERE …;
     134}}}
     135
     136* relkind i means index; r means relation which is table here.
     137* reltuple tells you how much tuple / rows exist in the table or index.
     138* relpages tells you how much pages are allocated for the table or index.
     139
     140* 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.
     141* In PostgreSQL version >= 9.2 you need to cast relpages to NUMERIC because the function changed to pg_size_pretty(NUMERIC).
     142
     143
    112144== Size of RASBASE tables ==
    113145{{{