PostgreSQL 9.6 Temporary files

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

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

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 tablespaceFROM
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 DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
Command – UXCEmployment by invitation only!*

Hi Andreas thanks for your response,

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

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'));

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

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.

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

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!

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 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 tablespaceFROM
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 DavidsonMaj. Database & Exploration SpecialistUniverse
Exploration Command – UXCEmployment 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!

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;

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

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

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

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 ?

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

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

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_schema
UNION ALLSELECT '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_schema
ORDER 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>: