Home > mailing lists

Thread: PostgreSQL 9.6 Temporary files

PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 22:31:20

Dear Friends,

I am newbie to postgresql.

I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.

I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).
I already run vacuum full and there is few dead tuples.

Best regards,

Jimmy AUGUSTINE

Re: PostgreSQL 9.6 Temporary files

From

Andreas Kretschmer

Date:

19 March 2018, 22:44:23

On 19 March 2018 17:31:20 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>Dear Friends,
>
>I am newbie to postgresql.
>I have 162 GB on my database but when I check size of all tables, I
>approximately obtain 80 GB.


Indexes?


>I also see that I have 68GB of temporary files however I only found

Where can you see that?


>2.4MB
>at postgres/data/base/pgsql_tmp.
>
>Could you tell me what are those temporary files and where are they at?
>Can
>I delete some of them?


No, never delete files in datadir!

>
>All values come from pgAdmin 4 and checked by my own SQL
>queries(postgresql-9.6).
>I already run vacuum full and there is few dead tuples.

A few dead tuples arn't a real problem.


>
>Best regards,
>Jimmy AUGUSTINE


--
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

19 March 2018, 22:45:51

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
> Dear Friends,
> 
> I am newbie to postgresql.
> I have 162 GB on my database but when I check size of all tables, I 
> approximately obtain 80 GB.
> I also see that I have 68GB of temporary files however I only found 
> 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

> 
> Could you tell me what are those temporary files and where are they at? 
> Can I delete some of them?
> 
> All values come from pgAdmin 4 and checked by my own SQL 
> queries(postgresql-9.6).

Can you show actual queries used?

> I already run vacuum full and there is few dead tuples.
> 
> Best regards,
> Jimmy AUGUSTINE


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Melvin Davidson

Date:

19 March 2018, 23:01:37

On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.

Note that total_size is the size of the table and all it's indexes.

SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
        END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;

--

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:02:14

Hi Andreas thanks for your response,

2018-03-19 17:44 GMT+01:00 Andreas Kretschmer <andreas@a-kretschmer.de>:

On 19 March 2018 17:31:20 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>Dear Friends,
>
>I am newbie to postgresql.
>I have 162 GB on my database but when I check size of all tables, I
>approximately obtain 80 GB.

Indexes?

Indexes are included into 80 GB that I mentioned.

>I also see that I have 68GB of temporary files however I only found

Where can you see that?

I used pgAdmin 4 and I see statistics on my global database.

>2.4MB
>at postgres/data/base/pgsql_tmp.
>
>Could you tell me what are those temporary files and where are they at?
>Can
>I delete some of them?

No, never delete files in datadir!

>
>All values come from pgAdmin 4 and checked by my own SQL
>queries(postgresql-9.6).
>I already run vacuum full and there is few dead tuples.

A few dead tuples arn't a real problem.

>
>Best regards,
>Jimmy AUGUSTINE

--
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:04:57

2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

19 March 2018, 23:09:12

On 03/19/2018 10:04 AM, Jimmy Augustine wrote:
> 
> 
> 2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
> 
>         Dear Friends,
> 
>         I am newbie to postgresql.
>         I have 162 GB on my database but when I check size of all
>         tables, I approximately obtain 80 GB.
>         I also see that I have 68GB of temporary files however I only
>         found 2.4MB at postgres/data/base/pgsql_tmp.
> 
> 
>     Exactly how did you determine this?
> 
> I used this command and sum result for all database :
> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
> 
> And this for complete database :
> SELECT pg_size_pretty(pg_database_size('Database Name'));
> 

So where did the 68GB number for temporary files come from?



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:12:36

2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/19/2018 10:04 AM, Jimmy Augustine wrote:

2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all
tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only
found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

19 March 2018, 23:15:47

On 03/19/2018 10:12 AM, Jimmy Augustine wrote:

> 
>              On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
> 
>                  Dear Friends,
> 
>                  I am newbie to postgresql.
>                  I have 162 GB on my database but when I check size of all
>                  tables, I approximately obtain 80 GB.
>                  I also see that I have 68GB of temporary files however
>         I only
>                  found 2.4MB at postgres/data/base/pgsql_tmp.
> 
> 
>              Exactly how did you determine this?
> 
>         I used this command and sum result for all database :
>         SELECT pg_size_pretty(pg_total_relation_size('table_name'));
> 
>         And this for complete database :
>         SELECT pg_size_pretty(pg_database_size('Database Name'));
> 
> 
>     So where did the 68GB number for temporary files come from?
> 
> I don't measure this value by my own. I was disappointed by the gap 
> between the two queries, so I checked pgAdmin 4 and I saw this value.

In what section of pgAdmin4?

Or do you know what query it used?

> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Melvin Davidson

Date:

19 March 2018, 23:17:40

On Mon, Mar 19, 2018 at 1:12 PM, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:

2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:04 AM, Jimmy Augustine wrote:

2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all
tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only
found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com

>I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

I think your problem is that SELECT pg_size_pretty(pg_total_relation_size('table_name')); only looks at the current database

but SELECT pg_size_pretty(pg_database_size('Database Name')); looks at ALL databases.

Try this query instead to show individual database sizes.

SELECT oid,
       datname,
       pg_size_pretty(pg_database_size(datname))as size_pretty,
       pg_database_size(datname) as size,
       (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
          FROM pg_database) AS total,
       ((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname))
                                       FROM pg_database) ) * 100)::numeric(6,3) AS pct
FROM pg_database
ORDER BY datname;

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:17:46

Hi,

I used this command and I found the same value in total_size column.

2018-03-19 18:01 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:

On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.
Note that total_size is the size of the table and all it's indexes.

SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
        END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;

--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From

Melvin Davidson

Date:

19 March 2018, 23:20:48

On Mon, Mar 19, 2018 at 1:17 PM, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:

Hi,

I used this command and I found the same value in total_size column.

2018-03-19 18:01 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:

On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.
Note that total_size is the size of the table and all it's indexes.

SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
        END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;

--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

>I used this command and I found the same value in total_size column.

Please be specific. Exactly WHAT is the SQL query?

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:21:42

2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/19/2018 10:12 AM, Jimmy Augustine wrote:

   On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

   Dear Friends,

   I am newbie to postgresql.
   I have 162 GB on my database but when I check size of all
   tables, I approximately obtain 80 GB.
   I also see that I have 68GB of temporary files however
I only
   found 2.4MB at postgres/data/base/pgsql_tmp.

   Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

In what section of pgAdmin4?

In section "Statistics" when I click on my database.

Or do you know what query it used?

I have found this but not sure

SELECT temp_files AS "Temporary files"    , temp_bytes AS "Size of temporary files"
FROM   pg_stat_database db;

-- Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Andreas Kretschmer

Date:

19 March 2018, 23:25:11

On 19 March 2018 18:21:42 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> On 03/19/2018 10:12 AM, Jimmy Augustine wrote:
>>
>>
>>>              On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
>>>
>>>                  Dear Friends,
>>>
>>>                  I am newbie to postgresql.
>>>                  I have 162 GB on my database but when I check size
>of all
>>>                  tables, I approximately obtain 80 GB.
>>>                  I also see that I have 68GB of temporary files
>however
>>>         I only
>>>                  found 2.4MB at postgres/data/base/pgsql_tmp.
>>>
>>>
>>>              Exactly how did you determine this?
>>>
>>>         I used this command and sum result for all database :
>>>         SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>>         And this for complete database :
>>>         SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>>>     So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>>> between the two queries, so I checked pgAdmin 4 and I saw this
>value.
>>>
>>
>> In what section of pgAdmin4?
>>
>In section "Statistics" when I click on my database.
>
>Or do you know what query it used?
>>
>I have found this but not sure
>
>SELECT temp_files AS "Temporary files"
>   , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;


That's aggregated. Not current values.

Andreas


--
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

19 March 2018, 23:25:51

On 03/19/2018 10:17 AM, Melvin Davidson wrote:
> 
> 

> *
> *I think your problem is that SELECT 
> pg_size_pretty(pg_total_relation_size('table_name')); only looks at the 
> current database


> *
> *but SELECT pg_size_pretty(pg_database_size('Database Name')); looks at 
> ALL databases.

Not according to here:

https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT
> 
> *
> *Try this query instead to show individual database sizes.
> 
> SELECT oid,
>         datname,
>         pg_size_pretty(pg_database_size(datname))as size_pretty,
>         pg_database_size(datname) as size,
>         (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
>            FROM pg_database)  AS total,
>         ((pg_database_size(datname) / (SELECT SUM( 
> pg_database_size(datname))
>                                         FROM pg_database) ) * 
> 100)::numeric(6,3) AS pct
>    FROM pg_database
>    ORDER BY datname;
> *
> 
> -- 
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:27:42

I tried this query and my database size is equal to 162GB.

2018-03-19 18:17 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:

On Mon, Mar 19, 2018 at 1:12 PM, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:

2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:04 AM, Jimmy Augustine wrote:

2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all
tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only
found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com

>I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

I think your problem is that SELECT pg_size_pretty(pg_total_relation_size('table_name')); only looks at the current database
but SELECT pg_size_pretty(pg_database_size('Database Name')); looks at ALL databases.

Try this query instead to show individual database sizes.

SELECT oid,
       datname,
       pg_size_pretty(pg_database_size(datname))as size_pretty,
       pg_database_size(datname) as size,
       (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
          FROM pg_database) AS total,
       ((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname))
                                       FROM pg_database) ) * 100)::numeric(6,3) AS pct
FROM pg_database
ORDER BY datname;

--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

19 March 2018, 23:29:35

2018-03-19 18:25 GMT+01:00 Andreas Kretschmer <andreas@a-kretschmer.de>:

On 19 March 2018 18:21:42 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> On 03/19/2018 10:12 AM, Jimmy Augustine wrote:
>>
>>
>>> On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
>>>
>>> Dear Friends,
>>>
>>> I am newbie to postgresql.
>>> I have 162 GB on my database but when I check size
>of all
>>> tables, I approximately obtain 80 GB.
>>> I also see that I have 68GB of temporary files
>however
>>> I only
>>> found 2.4MB at postgres/data/base/pgsql_tmp.
>>>
>>>
>>> Exactly how did you determine this?
>>>
>>> I used this command and sum result for all database :
>>> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>> And this for complete database :
>>> SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>>> So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>>> between the two queries, so I checked pgAdmin 4 and I saw this
>value.
>>>
>>
>> In what section of pgAdmin4?
>>
>In section "Statistics" when I click on my database.
>
>Or do you know what query it used?
>>
>I have found this but not sure
>
>SELECT temp_files AS "Temporary files"
> , temp_bytes AS "Size of temporary files"FROM pg_stat_database db;

That's aggregated. Not current values.

Ah did you know some documentation about that ?

Andreas

--
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

19 March 2018, 23:35:37

On 03/19/2018 10:29 AM, Jimmy Augustine wrote:
> 
> 

> 
> 
>     That's aggregated. Not current values.
> 
> Ah did you know some documentation about that ?

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

> 
>     Andreas
> 
> 
>     --
>     2ndQuadrant - The PostgreSQL Support Company
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

20 March 2018, 00:32:15

On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
> I tried this query and my database size is equal to 162GB.
> 

Well you can always look in $DATA directly. The database will be under 
$DATA/base/<db oid>.

You can find the <db oid> like this:

select oid, datname from  pg_database where datname='<db name>';

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

20 March 2018, 16:16:29

Thanks all for your response,

$du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:

SELECT stats.relname          AS table,      pg_size_pretty(pg_relation_size(statsio.relid))          AS table_size,      pg_size_pretty(pg_total_relation_size(statsio.relid)           - pg_relation_size(statsio.relid))          AS related_objects_size,      pg_size_pretty(pg_total_relation_size(statsio.relid))          AS total_table_size,      stats.n_live_tup          AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS statsUSING (relname)WHERE stats.schemaname = current_schemaUNION ALL
SELECT 'TOTAL'          AS table,      pg_size_pretty(sum(pg_relation_size(statsio.relid)))          AS table_size,      pg_size_pretty(sum(pg_total_relation_size(statsio.relid)           - pg_relation_size(statsio.relid)))          AS related_objects_size,      pg_size_pretty(sum(pg_total_relation_size(statsio.relid)))          AS total_table_size,      sum(stats.n_live_tup)          AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS statsUSING (relname)WHERE stats.schemaname = current_schemaORDER BY live_rows ASC;

I obtain 80GB in total_table_size (half of my database), where are missing data at?

2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
I tried this query and my database size is equal to 162GB.

Well you can always look in $DATA directly. The database will be under $DATA/base/<db oid>.

You can find the <db oid> like this:

select oid, datname from pg_database where datname='<db name>';

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Adrian Klaver

Date:

20 March 2018, 20:00:55

On 03/20/2018 03:16 AM, Jimmy Augustine wrote:
> Thanks all for your response,
> 
> $du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:
> 
> |SELECT stats.relname AS table, 
> pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, 
> pg_size_pretty(pg_total_relation_size(statsio.relid) - 
> pg_relation_size(statsio.relid)) AS related_objects_size, 
> pg_size_pretty(pg_total_relation_size(statsio.relid)) AS 
> total_table_size, stats.n_live_tup AS live_rows FROM 
> pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS 
> stats USING (relname) WHERE stats.schemaname = current_schemaUNION ALL 
> SELECT 'TOTAL' AS table, 
> pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, 
> pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - 
> pg_relation_size(statsio.relid))) AS related_objects_size, 
> pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS 
> total_table_size, sum(stats.n_live_tup) AS live_rows FROM 
> pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS 
> stats USING (relname) WHERE stats.schemaname = current_schemaORDER BY 
> live_rows ASC;
> 
> |
> 
> |I obtain 80GB in total_table_size (half of my database), where are 
> missing data at?

First of all you are using pg_statio_user_tables which does not count 
system tables.

Second pretty sure the use of current_schema is limiting the results to 
only one schema in the database.

> |
> 
> 
> 2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
> 
>         I tried this query and my database size is equal to 162GB.
> 
> 
>     Well you can always look in $DATA directly. The database will be
>     under $DATA/base/<db oid>.
> 
>     You can find the <db oid> like this:
> 
>     select oid, datname from  pg_database where datname='<db name>';
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From

Jimmy Augustine

Date:

20 March 2018, 20:28:30

2018-03-20 15:00 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/20/2018 03:16 AM, Jimmy Augustine wrote:
Thanks all for your response,

$du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:

|SELECT stats.relname AS table, pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, pg_size_pretty(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid)) AS related_objects_size, pg_size_pretty(pg_total_relation_size(statsio.relid)) AS total_table_size, stats.n_live_tup AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schemaUNION ALL SELECT 'TOTAL' AS table, pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid))) AS related_objects_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS total_table_size, sum(stats.n_live_tup) AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schemaORDER BY live_rows ASC;

|

|I obtain 80GB in total_table_size (half of my database), where are missing data at?

First of all you are using pg_statio_user_tables which does not count system tables.

Second pretty sure the use of current_schema is limiting the results to only one schema in the database.

AHHH Thanks you I found missing data they are stored into pg_largeobject.

|

2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

On 03/19/2018 10:27 AM, Jimmy Augustine wrote:

I tried this query and my database size is equal to 162GB.

Well you can always look in $DATA directly. The database will be
under $DATA/base/<db oid>.

You can find the <db oid> like this:

select oid, datname from pg_database where datname='<db name>';

-- Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com