From: Tom Lane <tgl@sss.pgh.pa.us>
To: Natalie Wenz <nataliewenz@ebureau.com>
cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg_toast oid limits
In-reply-to: <A88C1E63-DB62-43C6-B8EC-E78D07574690@ebureau.com>
References: <A88C1E63-DB62-43C6-B8EC-E78D07574690@ebureau.com>
Comments: In-reply-to Natalie Wenz <nataliewenz@ebureau.com>
	message dated "Wed, 26 Oct 2016 13:58:29 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <24093.1477509777.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 26 Oct 2016 15:22:57 -0400
Message-ID: <24095.1477509777@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Natalie Wenz <nataliewenz@ebureau.com> writes:
> I am seeing some performance issues that I'm trying to track down on a la=
rge database. One of the things I'm beginning to suspect is a particularly =
large table with many columns, about 200 of which (type text) contain large=
 chunks of data. Now, for a given row, maybe 10-30 of those columns contain=
 data, so not all 200 for each row, but the data can still be pretty sizabl=
e. There are currently around 750 million records in this table (and is abo=
ut 22TB in size). I was trying to learn more about toast, and I see some re=
ferences in the wiki and the hackers list to performance issues when you ap=
proach the 4 billion oids for a single table (which, I gather, are used whe=
n the data is toasted). Given my rudimentary understanding of how the whole=
 toast thing works, I was wondering if there is a way to see how many oids =
are used for a table, or another way to know if we're running into toast li=
mits for a single table.

You could do

select reltoastrelid::regclass from pg_class where relname =3D 'problem_tab=
le';

which will give you something like

      reltoastrelid=20=20=20=20=20=20
-------------------------
 pg_toast.pg_toast_78004

and then poke into the contents of that table.  Probably

select count(distinct chunk_id) from pg_toast.pg_toast_78004;

would answer your immediate question.

			regards, tom lane


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin