pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedTOAST not working
9+ messages / 6 participants
[nested] [flat]
* TOAST not working
@ 2006-06-10 13:03 list_man <listman@elkenserver.net>
2006-06-10 16:28 ` Re: TOAST not working Tom Lane <tgl@sss.pgh.pa.us>
2006-06-10 21:10 ` Re: TOAST not working Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 9+ messages in thread
From: list_man @ 2006-06-10 13:03 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi,
I wonder if anyone can help.
I have a VERY wide table and rows. There are over 800 columns of type:
numeric(11,2)
I can create the table no problem, but when I go to fill out a full row
with data, I get the message about reaching the 8k limit.
Can someone tell me if I have to 'enable' TOAST on columns to have it
kick in. According to my research, numeric data types are toastable.
I'd appreciate if anyone can give me lit1e.
Thanks
PS. I'm running Postgres v8.0x
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
@ 2006-06-10 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-10 17:15 ` Re: TOAST not working list_man <listman@elkenserver.net>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2006-06-10 16:28 UTC (permalink / raw)
To: listman@elkenserver.net; +Cc: pgsql-general@postgresql.org
list_man <listman@elkenserver.net> writes:
> I have a VERY wide table and rows. There are over 800 columns of type:
> numeric(11,2)
Perhaps you should reconsider your data design. Maybe some of those
columns would more logically form an array?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 16:28 ` Re: TOAST not working Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-10 17:15 ` list_man <listman@elkenserver.net>
0 siblings, 0 replies; 9+ messages in thread
From: list_man @ 2006-06-10 17:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org
Thanks for the suggestion Tom... I'm looking into it.
To continue however and educate me on datatypes & TOAST, should a row of
10k+, in this case consisting of the datatype listed below, store OK,
when TOAST kicks in?
thanks... Angus
On Sat, 2006-06-10 at 12:28 -0400, Tom Lane wrote:
> list_man <listman@elkenserver.net> writes:
> > I have a VERY wide table and rows. There are over 800 columns of type:
> > numeric(11,2)
>
> Perhaps you should reconsider your data design. Maybe some of those
> columns would more logically form an array?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
@ 2006-06-10 21:10 ` Christopher Browne <cbbrowne@acm.org>
2006-06-11 02:12 ` Re: [GENERAL] TOAST not working Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 02:43 ` Re: TOAST not working Angus Berry <angus.berry@elkenserve.com>
1 sibling, 2 replies; 9+ messages in thread
From: Christopher Browne @ 2006-06-10 21:10 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Centuries ago, Nostradamus foresaw when listman@elkenserver.net (list_man) would write:
> Can someone tell me if I have to 'enable' TOAST on columns to have it
> kick in. According to my research, numeric data types are toastable.
TOAST is only used on individual columns that exceed 8K in size.
The only way you'll be TOASTing numeric columns is if they are
individually defined to occupy > 8192 bytes.
Those are *BIG* numeric values; perhaps you're misunderstanding the
intent of TOAST...
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/slony.html
What do little birdies see when they get knocked unconscious?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [GENERAL] TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 21:10 ` Re: TOAST not working Christopher Browne <cbbrowne@acm.org>
@ 2006-06-11 02:12 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 02:58 ` Re: [GENERAL] TOAST not working Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Jim C. Nasby @ 2006-06-11 02:12 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-general@postgresql.org; pgsql-docs
On Sat, Jun 10, 2006 at 05:10:06PM -0400, Christopher Browne wrote:
> Centuries ago, Nostradamus foresaw when listman@elkenserver.net (list_man) would write:
> > Can someone tell me if I have to 'enable' TOAST on columns to have it
> > kick in. According to my research, numeric data types are toastable.
>
> TOAST is only used on individual columns that exceed 8K in size.
>
> The only way you'll be TOASTing numeric columns is if they are
> individually defined to occupy > 8192 bytes.
Actually, it's BLCKSZ/4. From
http://www.postgresql.org/docs/8.1/interactive/storage-toast.html:
"The TOAST code is triggered only when a row value to be stored in a
table is wider than BLCKSZ/4 bytes (normally 2Kb)."
BTW, 'row value' seems a bit prone to confusion (could be interpreted as
the row itself). It'd probably be better to say 'field'. Barring
objections, I'll submit a patch.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [GENERAL] TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 21:10 ` Re: TOAST not working Christopher Browne <cbbrowne@acm.org>
2006-06-11 02:12 ` Re: [GENERAL] TOAST not working Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-11 02:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-11 03:15 ` Re: [GENERAL] TOAST not working Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2006-06-11 02:58 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-general@postgresql.org; pgsql-docs
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Actually, it's BLCKSZ/4. From
> http://www.postgresql.org/docs/8.1/interactive/storage-toast.html:
> "The TOAST code is triggered only when a row value to be stored in a
> table is wider than BLCKSZ/4 bytes (normally 2Kb)."
> BTW, 'row value' seems a bit prone to confusion (could be interpreted as
> the row itself). It'd probably be better to say 'field'.
No, because that would be wrong; the statement is correct as written.
The toaster tries to do something about rows that are wider than
BLCKSZ/4 (well, actually MaxTupleSize/4 ... try grepping the source
for TOAST_TUPLE_THRESHOLD).
The OP's problem is that he's got too dang many fields. A TOAST pointer
is 20 bytes wide (on most machines, at least) so even if we toast every
single field out-of-line, we can't support more than about 400 toastable
fields in a row. The FAQ says
Maximum number of columns in a table? 250-1600 depending on column
types
but the 1600 figure is for datatypes like int4 that only take 4 bytes
anyway.
The OP was trying to store numeric(11,2) fields. If I'm counting on my
fingers correctly, such a value would occupy 16 bytes natively, which
means that pushing it out-of-line would be a dead loss anyway. But he's
still not going to get more than 512 of them into an 8K page.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [GENERAL] TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 21:10 ` Re: TOAST not working Christopher Browne <cbbrowne@acm.org>
2006-06-11 02:12 ` Re: [GENERAL] TOAST not working Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 02:58 ` Re: [GENERAL] TOAST not working Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-11 03:15 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jim C. Nasby @ 2006-06-11 03:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-docs
Dropping -general
On Sat, Jun 10, 2006 at 10:58:49PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > Actually, it's BLCKSZ/4. From
> > http://www.postgresql.org/docs/8.1/interactive/storage-toast.html:
>
> > "The TOAST code is triggered only when a row value to be stored in a
> > table is wider than BLCKSZ/4 bytes (normally 2Kb)."
>
> > BTW, 'row value' seems a bit prone to confusion (could be interpreted as
> > the row itself). It'd probably be better to say 'field'.
>
> No, because that would be wrong; the statement is correct as written.
> The toaster tries to do something about rows that are wider than
> BLCKSZ/4 (well, actually MaxTupleSize/4 ... try grepping the source
> for TOAST_TUPLE_THRESHOLD).
I'll argue that the docs are still confusing though, since 'row value'
could be interpreted as 'field', especially since the first paragraph
contains "In release 7.1 and later, this limit is overcome by allowing
large field values to be compressed and/or broken up into multiple
physical rows."
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 21:10 ` Re: TOAST not working Christopher Browne <cbbrowne@acm.org>
@ 2006-06-11 02:43 ` Angus Berry <angus.berry@elkenserve.com>
2006-06-12 10:07 ` Re: TOAST not working Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 1 reply; 9+ messages in thread
From: Angus Berry @ 2006-06-11 02:43 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Ahhh... thank you. This clarifies TOAST for me. I see how TOAST applies
to individual columns that exceed the 8k page size.
Postgres specs state it's possible to have 2GB rows and up to 1,600
columns. Can you tell me what data type would get to fill this spec. I
wouldn't normally push to this limit, but I am expecting to have to
defend Postgres specs. to a 3rd party.
Questions like this also help me get to grips with Postgres internals.
thanks... Angus
On Sat, 2006-06-10 at 17:10 -0400, Christopher Browne wrote:
> Centuries ago, Nostradamus foresaw when listman@elkenserver.net (list_man) would write:
> > Can someone tell me if I have to 'enable' TOAST on columns to have it
> > kick in. According to my research, numeric data types are toastable.
>
> TOAST is only used on individual columns that exceed 8K in size.
>
> The only way you'll be TOASTing numeric columns is if they are
> individually defined to occupy > 8192 bytes.
>
> Those are *BIG* numeric values; perhaps you're misunderstanding the
> intent of TOAST...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: TOAST not working
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 21:10 ` Re: TOAST not working Christopher Browne <cbbrowne@acm.org>
2006-06-11 02:43 ` Re: TOAST not working Angus Berry <angus.berry@elkenserve.com>
@ 2006-06-12 10:07 ` Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 9+ messages in thread
From: Martijn van Oosterhout @ 2006-06-12 10:07 UTC (permalink / raw)
To: Angus Berry <angus.berry@elkenserve.com>; +Cc: pgsql-general@postgresql.org
On Sat, Jun 10, 2006 at 10:43:02PM -0400, Angus Berry wrote:
> Ahhh... thank you. This clarifies TOAST for me. I see how TOAST applies
> to individual columns that exceed the 8k page size.
>
> Postgres specs state it's possible to have 2GB rows and up to 1,600
> columns. Can you tell me what data type would get to fill this spec. I
> wouldn't normally push to this limit, but I am expecting to have to
> defend Postgres specs. to a 3rd party.
Any variable length datatype might be able to go to 2GB. Things like
text, char, varchar and bytea are the obvious ones. Arrays too IIRC.
However, as pointed out, even a toasted field takes about 20 bytes,
which means you're limited to maybe 400 toasted fields. If you use
integers you can get to 1600.
Normally however, in cases where you need to store a lot of columns,
what you really want is an array. You could easily store an array with
a few million numerics in a single field...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-06-12 10:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-10 13:03 TOAST not working list_man <listman@elkenserver.net>
2006-06-10 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-10 17:15 ` list_man <listman@elkenserver.net>
2006-06-10 21:10 ` Christopher Browne <cbbrowne@acm.org>
2006-06-11 02:12 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 02:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-11 03:15 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 02:43 ` Angus Berry <angus.berry@elkenserve.com>
2006-06-12 10:07 ` Martijn van Oosterhout <kleptog@svana.org>
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