pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex file is growing when I drop a table. Why?
3+ messages / 2 participants
[nested] [flat]
* Index file is growing when I drop a table. Why?
@ 2023-11-16 16:32 Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-16 17:22 ` Re: Index file is growing when I drop a table. Why? Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ron Johnson @ 2023-11-16 16:32 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index file is growing when I drop a table. Why?
2023-11-16 16:32 Index file is growing when I drop a table. Why? Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-11-16 17:22 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2023-11-16 20:37 ` Re: Index file is growing when I drop a table. Why? Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleas Mantzios @ 2023-11-16 17:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Στις 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index file is growing when I drop a table. Why?
2023-11-16 16:32 Index file is growing when I drop a table. Why? Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-16 17:22 ` Re: Index file is growing when I drop a table. Why? Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
@ 2023-11-16 20:37 ` Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2023-11-16 20:37 UTC (permalink / raw)
To: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Nov 16, 2023 at 12:22 PM Achilleas Mantzios <
a.mantzios@cloud.gatewaynet.com> wrote:
> Στις 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,
> [snip]
>
> I was the only user in that database. (Also, the drop finished before I
saw your email.)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-11-16 20:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-16 16:32 Index file is growing when I drop a table. Why? Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-16 17:22 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2023-11-16 20:37 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox