On Wed, Oct 26, 2016 at 10:17 AM, Sebastian Webber <[email protected]>
wrote:
> Curioso do teu problema, eu fiz um teste aqui, veja:
>
> sebastian=# create table foo (id serial primary key, nome text);
> CREATE TABLE
> sebastian=# insert into foo (nome) select 'Nome ' ||
> generate_series(1,1000000);
> INSERT 0 1000000
> sebastian=# \dt+
> List of relations
> Schema | Name | Type | Owner | Size | Description
> --------+------+-------+-----------+-------+-------------
> public | foo | table | sebastian | 42 MB |
> (1 row)
>
>
>
> Até aí, tudo bem, mas quando computo o tamanho do banco, acontece algo
> diferente:
>
> sebastian=# select pg_size_pretty(pg_database_size(current_database()));
> pg_size_pretty
> ----------------
> 70 MB
> (1 row)
>
>
> Então, eu dou uma ajustada na query que computa os tamanho dos objetos[1]
> e identifico o espaço alocado:
>
> sebastian=# \x
>
> Expanded display is on.
>
> sebastian=# SELECT *, pg_size_pretty(total_bytes) AS total
>
> sebastian-# , pg_size_pretty(index_bytes) AS INDEX
>
> sebastian-# , pg_size_pretty(toast_bytes) AS toast
>
> sebastian-# , pg_size_pretty(table_bytes) AS TABLE
>
> sebastian-# FROM (
>
> sebastian(# SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0)
> AS table_bytes FROM (
>
> sebastian(# SELECT c.oid,nspname AS table_schema, relname AS
> TABLE_NAME
>
> sebastian(# , c.reltuples AS row_estimate
>
> sebastian(# , pg_total_relation_size(c.oid) AS total_bytes
>
> sebastian(# , pg_indexes_size(c.oid) AS index_bytes
>
> sebastian(# , pg_total_relation_size(reltoastrelid) AS
> toast_bytes
>
> sebastian(# FROM pg_class c
>
> sebastian(# LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
>
> sebastian(# WHERE relkind = 'r'
>
> sebastian(# AND (n.nspname <> ALL (ARRAY['pg_catalog'::name,
> 'information_schema'::name])) AND n.nspname !~ '^pg_toast'::text
>
> sebastian(# ) a
>
> sebastian(# ) a;
>
> -[ RECORD 1 ]+-----------
>
> oid | 16746
>
> table_schema | public
>
> table_name | foo
>
> row_estimate | 1e+06
>
> total_bytes | 66813952
>
> index_bytes | 22487040
>
> toast_bytes | 8192
>
> table_bytes | 44318720
>
> total | 64 MB
>
> index | 21 MB
>
> toast | 8192 bytes
>
> table | 42 MB
>
>
-[ RECORD 1 ]+-----------
oid | 18735
table_schema | public
table_name | teste
row_estimate | 3.47e+07
total_bytes | 1257799680
index_bytes | 0
toast_bytes |
table_bytes | 1257799680
total | 1200 MB
index | 0 bytes
toast |
table | 1200 MB
>
> Por fim, os ~7mb que faltam são do espaço alocado no template 1, veja:
>
> sebastian=# \l+
> List of databases
> -[ RECORD 1 ]-----+-------------------------------------------
> Name | postgres
> Owner | sebastian
> Encoding | UTF8
> Collate | en_US.UTF-8
> Ctype | en_US.UTF-8
> Access privileges |
> Size | 71 MB
> Tablespace | pg_default
> Description | default administrative connection database
> -[ RECORD 2 ]-----+-------------------------------------------
> Name | sebastian
> Owner | sebastian
> Encoding | UTF8
> Collate | en_US.UTF-8
> Ctype | en_US.UTF-8
> Access privileges |
> Size | 70 MB
> Tablespace | pg_default
> Description |
> -[ RECORD 3 ]-----+-------------------------------------------
> Name | template0
> Owner | sebastian
> Encoding | UTF8
> Collate | en_US.UTF-8
> Ctype | en_US.UTF-8
> Access privileges | =c/sebastian +
> | sebastian=CTc/sebastian
> Size | 6945 kB
> Tablespace | pg_default
> Description | unmodifiable empty database
> -[ RECORD 4 ]-----+-------------------------------------------
> Name | template1
> Owner | sebastian
> Encoding | UTF8
> Collate | en_US.UTF-8
> Ctype | en_US.UTF-8
> Access privileges | =c/sebastian +
> | sebastian=CTc/sebastian
> Size | 6945 kB
> Tablespace | pg_default
> Description | default template for new databases
>
>
>
>
teste2=# \l+
List of databases
-[ RECORD 1 ]-----+-------------------------------------------
Name | postgres
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges |
Size | 5804 kB
Tablespace | pg_default
Description | default administrative connection database
-[ RECORD 2 ]-----+-------------------------------------------
Name | template0
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges | =c/postgres
| postgres=CTc/postgres
Size | 6697 kB
Tablespace | pg_default
Description | unmodifiable empty database
-[ RECORD 3 ]-----+-------------------------------------------
Name | template1
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges | =c/postgres
| postgres=CTc/postgres
Size | 6705 kB
Tablespace | pg_default
Description | default template for new databases
-[ RECORD 4 ]-----+-------------------------------------------
Name | teste2
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges |
Size | 2410 MB
Tablespace | teste2
Description |
Repita os passos ai no seu cluster e me avise, ok?
>
> [1] https://wiki.postgresql.org/wiki/Disk_Usage
>
> --
> Sebastian Webber
> http://swebber.me
>
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
