From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <cjames@emolecules.com>
cc: pgsql-admin@postgresql.org
Subject: Re: Extreme slowdown querying system tables
In-reply-to: 
 <CAFwQ8reGR-px-ujcye_eOfqcvC=T6R=TVTkn01d7-qzgqCjf_A@mail.gmail.com>
References: 
 <CAFwQ8reGR-px-ujcye_eOfqcvC=T6R=TVTkn01d7-qzgqCjf_A@mail.gmail.com>
Comments: In-reply-to Craig James <cjames@emolecules.com>
	message dated "Fri, 02 Feb 2018 10:41:01 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2695.1517615556.1@sss.pgh.pa.us>
Date: Fri, 02 Feb 2018 18:52:36 -0500
Message-ID: <2696.1517615556@sss.pgh.pa.us>
Precedence: bulk

Craig James <cjames@emolecules.com> writes:
> A Chinese attacker out to steal massive amounts of our curated data
> attacked our website/database in a way that created a new user account on
> every access, which includes a couple tables per user. Bottom line: about
> 3.5 million tables were created.

Ugh.

> After we tightened our new-user signup
> procedure, all of the bogus accounts were deleted. Now the entire system
> (about 400 schemas) contains about 300,000 tables total, which is roughly
> where it's been for several years.
> However, the system tables haven't recovered; see the highlighted time.

Yeah, you'll have a whole lot of bloat in the catalogs.  You'll probably
have to do a VACUUM FULL of at least pg_class and pg_attribute to get
back to normal.  Unfortunately, there's no way to do that without an
exclusive lock, which will be tantamount to an outage.  Hope you can
schedule some downtime.

> I'm reluctant to do a VACUUM FULL and REINDEX, for fear that an exclusive
> lock will halt the system (or that you shouldn't do this to system tables).

It's safe, and you would not need to REINDEX because the VACUUM would
rebuild the table's indexes anyway.  But no way around the lock :-(

			regards, tom lane