From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mario de Frutos Dieguez <mariodefrutos@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: row is too big: size 8168, maximum size 8160
Date: Wed, 11 Jul 2018 12:17:38 -0400
Message-ID: <28785.1531325858@sss.pgh.pa.us> (raw)
In-Reply-To: <CADc-R5hOfvaRO7GCyGA3-ZuBLuc9MnirBqayoa+9Wn-YwPG+6w@mail.gmail.com>
References: <CAFYwGJ0Sphm9m3noXMgYf=1iDCn1=V2+7+VCD-nRg+C21Ee-gA@mail.gmail.com>
<19464.1531320028@sss.pgh.pa.us>
<CADc-R5hOfvaRO7GCyGA3-ZuBLuc9MnirBqayoa+9Wn-YwPG+6w@mail.gmail.com>
Mario de Frutos Dieguez <mariodefrutos@gmail.com> writes:
> Thank you for your quick answer. We have to think if we can rearrange it.
> The funny thing is that we have the same number of columns for other
> tables which are the same except for stored geometries and those
> stores the information without any problem.
> That's why I thought maybe is a corruption thing or something
No, it's just too many columns. If we suppose that the average width of
your numeric columns is 12 bytes, then you need 790*12 = 9480 bytes,
which doesn't fit on an 8K page even without any accounting for row
header and page header overhead. TOAST can't help by pushing values
out-of-line, because a TOAST pointer is 18 bytes so it'd actually make
things worse. (TOAST works well for individual large fields, but not
at all for this case.)
You can get away with 790 columns if they're 4 or 8 bytes apiece, or if
many of them are NULL, but evidently these particular numeric values
average more than that.
In practice, though, there are a lot of other inefficiencies with
tables that have so many columns. So I counsel looking into arrays
or some other approach.
regards, tom lane
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: tgl@sss.pgh.pa.us, mariodefrutos@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: row is too big: size 8168, maximum size 8160
In-Reply-To: <28785.1531325858@sss.pgh.pa.us>
* 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