MIME-Version: 1.0
In-Reply-To: <20160704170458.GA425951@alvherre.pgsql>
References: 
 <CAN4RuQtCYxQg0u_NUncLAyLE-Yd9Yv1Ont5R2skJ53Xygvc4Xg@mail.gmail.com>
 <20160704170458.GA425951@alvherre.pgsql>
From: Kouber Saparev <kouber@gmail.com>
Date: Mon, 4 Jul 2016 20:10:09 +0300
Message-ID: 
 <CAN4RuQt5GZEbkHdPmm=j-z2uE3m3U-USWFNhVRiqYAKv5BZqhg@mail.gmail.com>
Subject: Re: DELETE takes too much memory
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a1134f8ac9ee3ad0536d26805
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1134f8ac9ee3ad0536d26805
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

No. There are AFTER triggers on other tables that write to this one though.
It is an audits table, so I omitted all the foreign keys on purpose.

2016-07-04 20:04 GMT+03:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> Kouber Saparev wrote:
> > I tried to DELETE about 7 million rows at once, and the query went up t=
o
> > 15% of the RAM (120 GB in total), which pushed some indexes out and the
> > server load went up to 250, so I had to kill the query.
> >
> > The involved table does not have neither foreign keys referring to othe=
r
> > tables, nor other tables refer to it. The size of the table itself is 1=
9
> GB
> > (15% of 120 GB). So why the DELETE tried to put the entire table in
> memory,
> > or what did it do to take so much memory?
>
> Are there triggers in the table?  Deferred triggers in particular can
> use memory.
>
> --
> =C3=81lvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

--001a1134f8ac9ee3ad0536d26805
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">No. There are AFTER triggers on other tables that write to=
 this one though. It is an audits table, so I omitted all the foreign keys =
on purpose.</div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">=
2016-07-04 20:04 GMT+03:00 Alvaro Herrera <span dir=3D"ltr">&lt;<a href=3D"=
mailto:alvherre@2ndquadrant.com" target=3D"_blank">alvherre@2ndquadrant.com=
</a>&gt;</span>:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0=
 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class=3D"">Kouber =
Saparev wrote:<br>
&gt; I tried to DELETE about 7 million rows at once, and the query went up =
to<br>
&gt; 15% of the RAM (120 GB in total), which pushed some indexes out and th=
e<br>
&gt; server load went up to 250, so I had to kill the query.<br>
&gt;<br>
&gt; The involved table does not have neither foreign keys referring to oth=
er<br>
&gt; tables, nor other tables refer to it. The size of the table itself is =
19 GB<br>
&gt; (15% of 120 GB). So why the DELETE tried to put the entire table in me=
mory,<br>
&gt; or what did it do to take so much memory?<br>
<br>
</span>Are there triggers in the table?=C2=A0 Deferred triggers in particul=
ar can<br>
use memory.<br>
<span class=3D"HOEnZb"><font color=3D"#888888"><br>
--<br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
<a href=3D"http://www.2ndQuadrant.com/" rel=3D"noreferrer" target=3D"_blank=
">http://www.2ndQuadrant.com/</a><br>
PostgreSQL Development, 24x7 Support, Remote DBA, Training &amp; Services<b=
r>
</font></span></blockquote></div><br></div>

--001a1134f8ac9ee3ad0536d26805--