I was using the pg_activity monitoring tool, which I find quite awesome.
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@xxxxxxxxx>:
On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev <kouber@xxxxxxxxx> 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
