From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Joao Junior <jcoj2006@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete huge Table under XFS
Date: Sun, 6 Oct 2019 22:54:52 +0200
Message-ID: <20191006205452.5vx3qqxwjbxc6jzh@development> (raw)
In-Reply-To: <CABnPa_hdHsdypn7HtXU81B9HcrVcimotnwfzE-MWwO1etWYJzA@mail.gmail.com>
References: <CABnPa_h2f5342mF6yM9pAR1=nV24hKwiBAXhmiS9zOEKmvUQAg@mail.gmail.com>
<357e7bd3-1b40-180c-14ce-7911229b5efb@a-kretschmer.de>
<CABnPa_hdHsdypn7HtXU81B9HcrVcimotnwfzE-MWwO1etWYJzA@mail.gmail.com>
On Thu, Sep 19, 2019 at 07:00:01PM +0200, Joao Junior wrote:
>A table with 800 gb means 800 files of 1 gb. When I use truncate or drop
>table, xfs that is a log based filesystem, will write lots of data in its
>log and this is the problem. The problem is not postgres, it is the way
>that xfs works with big files , or being more clear, the way that it
>handles lots of files.
>
I'm a bit skeptical about this explanation. Yes, XFS has journalling,
but only for metadata - and I have a hard time believing deleting 800
files (or a small multiple of that) would write "lots of data" into the
jornal, and noticeable performance issues. I wonder how you concluded
this is actually the problem.
That being said, TRUNCATE is unlikely to perform better than DROP,
because it also deletes all the files at once. What you might try is
dropping the indexes one by one, and then the table. That should delete
files in smaller chunks.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, jcoj2006@gmail.com
Subject: Re: Delete huge Table under XFS
In-Reply-To: <20191006205452.5vx3qqxwjbxc6jzh@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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