Re: postgresql 9.6 data directory fs becomes full

Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> writes:

> I want to check something regarding postgresql performance during my
> app is running.
>
> My app does the next things on 20 tables in a loop :
>
> 1.truncate table.
> 2.drop constraints on table
> 3.drop indexes on table
> 4.insert into local_table select * from remote_oracle_table
> 4.1.Recently I'm getting an error in this part : SQLERRM = could not extend
> file "base/16400/124810.23": wrote only 4096 of 8192 bytes at block
> 3092001
> 5.create constraints on table
> 6.create indexes on table.
>
> This operation runs every night. Most of the tables are small 500M-2G
> but few tables are pretty big 24G-45G.
>
> My wals and my data directory are on different fs. My data directory
> fs size is 400G. During this operation the data directory fs becomes
> full. However, after this operation 100G are freed which means that
> 300G are used from the 400g of the data directory fs. Something
> regarding those sizes doesnt seems ok.
>
> When I check my database size :
>
> mydb=# SELECT
> mydb-# pg_database.datname,
> mydb-# pg_size_pretty(pg_database_size(pg_database.datname)) AS size
> mydb-# FROM pg_database;
> datname | size
> -----------+---------
> template0 | 7265 kB
> mydb | 246 GB
> postgres | 568 MB
> template1 | 7865 kB
> (4 rows)
>
> When I check all the tables in mydb database :
>
> mydb-# relname as "Table",
> mydb-# pg_size_pretty(pg_total_relation_size(relid)) As "Size",
> mydb-# pg_size_pretty(pg_total_relation_size(relid) -
> pg_relation_size(relid)) as "External Size"
> mydb-# FROM pg_catalog.pg_statio_user_tables ORDER BY
> pg_total_relation_size(relid) DESC;
> Table | Size | External Size
> -------------------+------------+---------------
> table 1| 45 GB | 13 GB
> table 2| 15 GB | 6330 MB
> table 3| 9506 MB | 3800 MB
> table 4| 7473 MB | 1838 MB
> table 5| 7267 MB | 2652 MB
> table 6| 5347 MB | 1701 MB
> table 7| 3402 MB | 1377 MB
> table 8| 3092 MB | 1318 MB
> table 9| 2145 MB | 724 MB
> table 10| 1804 MB | 381 MB
> table 11 293 MB | 83 MB
> table 12| 268 MB | 103 MB
> table 13| 225 MB | 108 MB
> table 14| 217 MB | 40 MB
> table 15| 172 MB | 47 MB
> table 16| 134 MB | 36 MB
> table 17| 102 MB | 27 MB
> table 18| 86 MB | 22 MB
> .....
>
> In the data directory the base directory`s size is 240G. I have 16G
> of ram in my machine.
>
> Waiting for help, thanks.

You didn't say but if I can assume you're doing this work in a
transaction...

You understand that space is *not* freed by the truncate until commit, right?

>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800