Re: Extreme slowdown querying system tables

On Fri, Feb 2, 2018 at 3:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Craig James <cjames(at)emolecules(dot)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 :-(
>

Thanks. That's exactly what I needed to know. We'll schedule some down time.

Craig

>
> regards, tom lane
>

--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------