Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3g4S-00545E-IM for pgsql-admin@arkaria.postgresql.org; Thu, 16 Nov 2023 17:22:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r3g4R-007z1C-9U for pgsql-admin@arkaria.postgresql.org; Thu, 16 Nov 2023 17:22:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3g4Q-007z14-Un for pgsql-admin@lists.postgresql.org; Thu, 16 Nov 2023 17:22:26 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3g4O-006lsm-Bf for pgsql-admin@lists.postgresql.org; Thu, 16 Nov 2023 17:22:26 +0000 Content-Type: multipart/alternative; boundary="------------plEd0OrgnfBlCurCHI0bjaYC" Message-ID: <810a0a70-0fbc-400c-bc67-f9d627f103be@cloud.gatewaynet.com> Date: Thu, 16 Nov 2023 19:22:20 +0200 MIME-Version: 1.0 Subject: Re: Index file is growing when I drop a table. Why? Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------plEd0OrgnfBlCurCHI0bjaYC Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 16/11/23 18:32, ο/η Ron Johnson έγραψε: > Pg 15.4 > > I'm dropping the large table "bar" in a test database, and it's taking > a /long/ time.  Thus, I thought I'd watch it deleting files. > > To my surprise, though, the files that store the index are growing.  > What's up with that? What does pg_locks show ? SELECT blocked_locks.pid AS blocked_pid,    blocked_activity.usename AS blocked_user,    blocking_locks.pid AS blocking_pid,    blocking_activity.usename AS blocking_user,    blocked_activity.query AS blocked_statement,    blocking_activity.query AS blocked_by   FROM pg_locks blocked_locks     JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid     JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND NOT blocking_locks.database IS DISTINCT FROM blocked_locks.database AND NOT blocking_locks.relation IS DISTINCT FROM blocked_locks.relation AND NOT blocking_locks.page IS DISTINCT FROM blocked_locks.page AND NOT blocking_loc ks.tuple IS DISTINCT FROM blocked_locks.tuple AND NOT blocking_locks.virtualxid IS DISTINCT FROM blocked_locks.virtualxid AND NOT blocking_locks.transactioni d IS DISTINCT FROM blocked_locks.transactionid AND NOT blocking_locks.classid IS DISTINCT FROM blocked_locks.classid AND NOT blocking_locks.objid IS DISTINCT FROM blocked_locks.objid AND NOT blocking_locks.objsubid IS DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid <> blocked_locks.pid     JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid  WHERE NOT blocked_locks.granted; > > foo=# select * from pg_class where oid = 41533; > -[ RECORD 1 ]-------+---------- > oid                 | 41533 > relname             | i_bar_su1 > relnamespace        | 2200 > > $ pwd > /var/lib/pgsql/15/data/base/16482 > $ la -aFl 41533* > -rw------- 1 postgres postgres 1073741824 2023-11-16 11:15:06 41533 > -rw------- 1 postgres postgres  189792256 2023-11-16 11:24:15 41533.1 > > Five minutes later, 41533.1 is 4.3MB larger. > > $ dir 41533* > -rw------- 1 postgres postgres 1073741824 2023-11-16 11:15:06 41533 > -rw------- 1 postgres postgres  194101248 2023-11-16 11:29:44 41533.1 > -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt --------------plEd0OrgnfBlCurCHI0bjaYC Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 16/11/23 18:32, ο/η Ron Johnson έγραψε:
Pg 15.4

I'm dropping the large table "bar" in a test database, and it's taking a long time.  Thus, I thought I'd watch it deleting files.

To my surprise, though, the files that store the index are growing.  What's up with that?

What does pg_locks show ?

SELECT blocked_locks.pid AS blocked_pid,
   blocked_activity.usename AS blocked_user,
   blocking_locks.pid AS blocking_pid,
   blocking_activity.usename AS blocking_user,
   blocked_activity.query AS blocked_statement,
   blocking_activity.query AS blocked_by
  FROM pg_locks blocked_locks
    JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND NOT blocking_locks.database IS DISTINCT FROM blocked_locks.database
AND NOT blocking_locks.relation IS DISTINCT FROM blocked_locks.relation AND NOT blocking_locks.page IS DISTINCT FROM blocked_locks.page AND NOT blocking_loc
ks.tuple IS DISTINCT FROM blocked_locks.tuple AND NOT blocking_locks.virtualxid IS DISTINCT FROM blocked_locks.virtualxid AND NOT blocking_locks.transactioni
d IS DISTINCT FROM blocked_locks.transactionid AND NOT blocking_locks.classid IS DISTINCT FROM blocked_locks.classid AND NOT blocking_locks.objid IS DISTINCT
FROM blocked_locks.objid AND NOT blocking_locks.objsubid IS DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid <> blocked_locks.pid
    JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
 WHERE NOT blocked_locks.granted;


foo=# select * from pg_class where oid = 41533;
-[ RECORD 1 ]-------+----------
oid                 | 41533
relname             | i_bar_su1
relnamespace        | 2200

$ pwd
/var/lib/pgsql/15/data/base/16482
$ la -aFl 41533*
-rw------- 1 postgres postgres 1073741824 2023-11-16 11:15:06 41533
-rw------- 1 postgres postgres  189792256 2023-11-16 11:24:15 41533.1

Five minutes later, 41533.1 is 4.3MB larger.

$ dir 41533* 
-rw------- 1 postgres postgres 1073741824 2023-11-16 11:15:06 41533
-rw------- 1 postgres postgres  194101248 2023-11-16 11:29:44 41533.1

-- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt
--------------plEd0OrgnfBlCurCHI0bjaYC--