I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution than when I run du on the linux machine. I have 3 tablespaces I use: dwh, ssd, and archive. And 2 more pg_default and pg_global that as I understand come with the installation. This is the output from pg_tablespace:

  spcname   | spcowner |     spclocation        | spcacl | spcoptions
 pg_default |       10 |                        |        |
 pg_global  |       10 |                        |        |
 ssd        |    16385 | /ssd/rt/data           |        |
 dwh        |    16385 | /ssd/rt/dwh            |        |
 archive    |    16385 | /archive/postgres/data |        |

When I run this query:

SELECT  tablespace,
    sum(pg_total_relation_size((schemaname || '.' || tablename)::regclass))/1024/1024/1024 as total_usage_gb
FROM pg_tables
group by 1

which suppose to output the usage per tablespace I get the following:

 tablespace |     total_usage_gb
            |   252.6884689331054688
 archive    |  2268.1800308227539063
 dwh        |     9.9236679077148438
 pg_global  | 0.00161743164062500000
(4 rows)

When I run du -sh on the folders that the tablespaces use I get the following:

1.1T    /ssd/rt/data
7.8G    /ssd/rt/dwh
1.5T    /archive/postgres/data
697M    /var/lib/pgsql/9.1/data/base
1.7M    /var/lib/pgsql/9.1/data/global

A bit more info before I ask my questions: When I run show default_tablespace I get the following:


(1 row)

When I run show temp_tablespaces I get the following


 (1 row)

I'm using postgresql 9.1

My questions are:

  1. When the tablespace is null, what happen? Because there are no row from the DB query that lists the "ssd" tablespace, there is one row with tablespace null that has 253GB. I don't think it went to pg_global or pg_default because those folders has less than 1GB occupied.

  2. Where is the 1.1TB that the du -sh shows on folder /ssd/rt/data which suppose to be tablespace "ssd"

  3. The du -sh list 1.5TB in archive folder but my query returns 2.2TB in the archive tablespace.

I'm sorry in advance if this is a trivial issue, I tried looking at it in the documentation but couldn't find much.

-- UPDATE --
I found the pg_database system_catalog. In there, there is a column that holds the default tablespace which in my case is the one named, "ssd". I still couldn't solve the discrepancy between pg_total_relation_size and du command.

  • Tablespaces are cluster-wide but pg_tables is only for the database you're connected to. Could you show all the databases with select oid, datname, pg_database_size(datname) from pg_database ? – Daniel Vérité May 14 '13 at 13:02
  • dwh database takes up 2574.439 GB of storage, all the rest are below 1 gb. – Gluz May 14 '13 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.