Re: DELETE takes too much memory

I was using the pg_activity monitoring tool, which I find quite awesome.

https://github.com/julmon/pg_activity

There are 3 btree indexes, here's the definition of the table itself:

Table "audits.audits"
Column | Type |
Modifiers
-------------------+-----------------------------+-----------------------------------------------------------------------
id | bigint | not null default
nextval('audits.audits_id_seq'::regclass)
auditable_type_id | oid | not null
auditable_id | integer |
operation | audits.operation | not null
old_data | jsonb |
new_data | jsonb |
user_id | integer | default
(NULLIF(session.get_var('user_id'::text), ''::text))::integer
ip | inet | default
(NULLIF(session.get_var('ip'::text), ''::text))::inet
service_name | character varying(100) | default
NULLIF(session.get_var('service'::text), ''::text)
service_action | text | default
NULLIF(session.get_var('action'::text), ''::text)
created_at | timestamp without time zone | not null default
clock_timestamp()
Indexes:
"audits_pkey" PRIMARY KEY, btree (id)
"index_audits_on_auditable_type_id_and_auditable_id" btree
(auditable_type_id, auditable_id)
"index_audits_on_created_at" btree (created_at)

2016-07-06 19:12 GMT+03:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev <kouber(at)gmail(dot)com> wrote:
> > I tried to DELETE about 7 million rows at once, and the query went up to
> 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 other
> > tables, nor other tables refer to it. The size of the table itself is 19
> 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?
> >
> > I am using 9.4.5.
>
> How did you measure memory usage exactly? In particular, memory
> consumption from the pid attached to the query or generalized to the
> server? Is this linux and if so what memory metric did you use? What
> kinds of indexes are on this table (in particular, gin/gist?)?
>
> merlin
>