Natalie Wenz <nataliewenz@ebureau.com> writes:
> I am seeing some performance issues that I'm trying to track down on a large database. One of the things I'm
beginningto suspect is a particularly large table with many columns, about 200 of which (type text) contain large
chunksof data. Now, for a given row, maybe 10-30 of those columns contain data, so not all 200 for each row, but the
datacan still be pretty sizable. There are currently around 750 million records in this table (and is about 22TB in
size).I was trying to learn more about toast, and I see some references in the wiki and the hackers list to performance
issueswhen you approach the 4 billion oids for a single table (which, I gather, are used when the data is toasted).
Givenmy rudimentary understanding of how the whole toast thing works, I was wondering if there is a way to see how many
oidsare used for a table, or another way to know if we're running into toast limits for a single table.
You could do
select reltoastrelid::regclass from pg_class where relname = 'problem_table';
which will give you something like
reltoastrelid
-------------------------
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