Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsBuT-00077B-IL for pgsql-admin@arkaria.postgresql.org; Fri, 08 Feb 2019 19:34:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gsBuS-0000tq-Ag for pgsql-admin@arkaria.postgresql.org; Fri, 08 Feb 2019 19:34:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsBuS-0000ti-2T for pgsql-admin@lists.postgresql.org; Fri, 08 Feb 2019 19:34:00 +0000 Received: from mail-qt1-x82f.google.com ([2607:f8b0:4864:20::82f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsBuP-0008UV-O4 for pgsql-admin@lists.postgresql.org; Fri, 08 Feb 2019 19:33:59 +0000 Received: by mail-qt1-x82f.google.com with SMTP id j36so5261104qta.7 for ; Fri, 08 Feb 2019 11:33:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=aqaGukw1tf7KIcXnU2hhev0yl8VnqqxbHVFOclIbT9A=; b=fvhI77ItqS0G8Kzo4H1Z9tDSVl9OwIKAQ51i7ybPEmMuYsDNiyR2bkrUc0ndPkxbZP otFevU2lKJRo3RLIJQT6f43N0WY0IaUwaNzb7QXBn3oSm24YK5XJVFVG9KC7qyxPLu2h eq2vQNnONeRbZ7oovldBAEhWnci6sX5WhO+bLEdlY41vPZ67lvCV7BQDDpTwc/Y2ehoE UkaOoNF5zhiuTWs3exy0JJJgJ5Ro7eLruX4hYxLalQ5lL+F78Ja0rLKiSMXXaJ9OpVsM u6K2Qm9kFes0cgoxyKXI/o/97FXDYEd1wjJf6VHfo/zehxQQDVMZ6BDiV9IasXE645kW KNIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=aqaGukw1tf7KIcXnU2hhev0yl8VnqqxbHVFOclIbT9A=; b=jXe/jgQQWtKgVvUQmCGiVlUoHAuTKNMWwhFfWZ3kgF3YzULzdDsrfSiIgacb7bPXeS N9lvPIZyOMemQWQHaX/dImTuYh9ZZACc0IqPFC87wIc3LYLOTVzQAw6DN9m6q03vQV2L wb0P5etSE/mbdT2sw0vNvQoC4MGyPuLM7CNtCtH2wQf41BBMI2tBYynwv2pAmjOy6tYR SBVGcZD3AR9jv+vFksJ5uL8Y2fcj38bUO2tWYNHQaKU6dLL/77Kx2yOccYmQN4dQ+dkc UXhM2XQIqcLRMjF6gdv0dhYT0TGOc78yyr+BjFaHdVvyeBbKiy6mD/g10ImKxX+HPTxo bnng== X-Gm-Message-State: AHQUAuYbD8/5v4VR4nIHlRVV07xs7cxsdVmy2uPvgTsfMXrhreY8jfKD kfGqBS98KZHXr3oXpGeha28k3vrR X-Google-Smtp-Source: AHgI3IZnFNvQz///SQ+S62WGbakgMyB3v4wTOyQWuB2BmFizct6tJhxs8rctQARyXP6G6W26q/fv3Q== X-Received: by 2002:ac8:8e4:: with SMTP id y33mr17616634qth.311.1549654435243; Fri, 08 Feb 2019 11:33:55 -0800 (PST) Received: from ?IPv6:2601:5cd:c200:16e3:e062:debf:8651:5f8d? ([2601:5cd:c200:16e3:e062:debf:8651:5f8d]) by smtp.gmail.com with ESMTPSA id y70sm2685462qka.75.2019.02.08.11.33.54 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 08 Feb 2019 11:33:54 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-EDCBE90F-7EE5-4194-B59B-C491009E0E72 Mime-Version: 1.0 (1.0) Subject: Re: Postgres Database Disk Usage From: Carrie Berlin X-Mailer: iPhone Mail (16D40) In-Reply-To: Date: Fri, 8 Feb 2019 14:33:53 -0500 Cc: "pgsql-admin@lists.postgresql.org" Content-Transfer-Encoding: 7bit Message-Id: <240DE3B0-C76B-4A62-9F7E-E37B05A1D8EC@gmail.com> References: To: Jeremiah Bauer List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-EDCBE90F-7EE5-4194-B59B-C491009E0E72 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Do you character varying columns that defined with limit ? Search for pg_toa= st on google and find the query to determine how much pg_toast you have. Sent from my iPhone > On Feb 8, 2019, at 10:47, Jeremiah Bauer wrote: >=20 > Hello, >=20 > We have a development database that reports a larger size than the sum of i= t's relations and I was hoping someone had some insight into this issue. Th= e consumed space is reflected in the size of the data directories PostgreSQL= is maintaining on disk, so it's just not a reported size issue. >=20 > This is the version of Postgres we are using: >=20 > FVDM=3D# select version(); > version = =20 > --------------------------------------------------------------------------= --------------------------------- > PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 201= 20313 (Red Hat 4.4.7-23), 64-bit >=20 > pg_database_size reports this size: >=20 > name | owner | size =20 > ------------+----------+--------- > FVDM | postgres | 43 GB >=20 > Running a query to report the total relation size of each schema results i= n this: >=20 > schemaname | pg_size_pretty=20 > --------------------+---------------- > information_schema | 152 kB > pg_catalog | 7880 kB > public | 52 MB > mart | 439 MB > (4 rows) >=20 > The total size of the relations is less than a gigabyte, but the database i= s 43GB in size. I've checked each table and there aren't any added indexes,= so I'm very confused. >=20 > We've run vacuum full and reindex on the database with no reduction in siz= e and a vacuum analyze verbose reports no recoverable rows. I cloned the ma= chine for troubleshooting and even attempted truncating every table in the d= atabase to see if that remedied the issue. The only thing that has reclaime= d the space is dropping the database and restoring from backup. >=20 > What is using the space and how do we reclaim it? =20 >=20 > Any help or insight would be greatly appreciated, I'm at a loss as to what= is consuming this space. >=20 > -- >=20 > Jeremiah > CONFIDENTIALITY NOTICE: The information contained in this email (and any a= ttachments) is privileged and confidential and protected from disclosure. If= you are not the intended recipient of this email or the attachments, be awa= re that any disclosure, copying, distribution or use of this email or any at= tachment is strictly prohibited and you should not read the message or read o= r open any attachment. If you have received this email by mistake, please im= mediately notify the sender and delete it permanently from your system. Agri= Stats, Inc. and its subsidiaries will not be held liable to any person or e= ntity resulting from the unintended or unauthorized use of any information c= ontained in this email. --Apple-Mail-EDCBE90F-7EE5-4194-B59B-C491009E0E72 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Do you character varying columns that defined with limit ? Search for pg_toast on google and find the query to determine how much pg_toast you have.

Sent from my iPhone

On Feb 8, 2019, at 10:47, Jeremiah Bauer <jbauer@agristats.com> wrote:

Hello,

We have a development database that reports a larger size than the sum of it's relations and I was hoping someone had some insight into this issue.  The consumed space is reflected in the size of the data directories PostgreSQL is maintaining on disk, so it's just not a reported size issue.

This is the version of Postgres we are using:

FVDM=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

pg_database_size reports this size:

    name    |  owner   |  size  
------------+----------+---------
 FVDM       | postgres | 43 GB

Running a query to report the total relation size of each schema results in this:

     schemaname     | pg_size_pretty
--------------------+----------------
 information_schema | 152 kB
 pg_catalog         | 7880 kB
 public             | 52 MB
 mart               | 439 MB
(4 rows)

The total size of the relations is less than a gigabyte, but the database is 43GB in size.  I've checked each table and there aren't any added indexes, so I'm very confused.

We've run vacuum full and reindex on the database with no reduction in size and a vacuum analyze verbose reports no recoverable rows.  I cloned the machine for troubleshooting and even attempted truncating every table in the database to see if that remedied the issue.  The only thing that has reclaimed the space is dropping the database and restoring from backup.

What is using the space and how do we reclaim it?  

Any help or insight would be greatly appreciated, I'm at a loss as to what is consuming this space.

--


Jeremiah

CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.
--Apple-Mail-EDCBE90F-7EE5-4194-B59B-C491009E0E72--