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
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
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
