From: Joshua D. Drake <jd@commandprompt.com>
To: Natalie Wenz <nataliewenz@ebureau.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg_toast oid limits
Date: Wed, 26 Oct 2016 12:05:33 -0700
Message-ID: <cb232f2c-6b52-8256-d50e-86aaa530c883@commandprompt.com> (raw)
In-Reply-To: <A88C1E63-DB62-43C6-B8EC-E78D07574690@ebureau.com>
References: <A88C1E63-DB62-43C6-B8EC-E78D07574690@ebureau.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 10/26/2016 11:58 AM, Natalie Wenz wrote:
> Hi all,
>
> I am seeing some performance issues that I'm trying to track down on a large 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 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 issues when you approach the 4 billion oids for a single table (which, I gather, are used when 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 limits for a single table.
>
> What I was reading, for reference:
> https://wiki.postgresql.org/wiki/TOAST
> http://osdir.com/ml/postgresql-pgsql-hackers/2015-01/msg01901.html
>
> Also, we are running postgres 9.5.4.
oids are used per relation not per row.
Your problem is likely more to do with the number of rows + the width of
the table. I suggest finding a way to partition this table.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jd@commandprompt.com, nataliewenz@ebureau.com
Subject: Re: pg_toast oid limits
In-Reply-To: <cb232f2c-6b52-8256-d50e-86aaa530c883@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox