Thread: max tuple size and filesystem block size

This has been answered many times I guess:

My linux filesystem is using a 4k block size.  Does this mean that I can only
store up to 4k in a tuple?  Where exactly does that 8k limit come from?  If
Postgres is limited to 8k per tuple on my system right now, then it would use 2
blocks.

It has been said that Postgres can use up to 32k per tuple.  Is the max kb per
tuple equal to the block size of the filesystem?  That is, if I want 32k max
tuple size, I have to put Postgres on a filesystem that is using 32k blocks
(or 16k??) ?

I'm confused! :)

--
Robert B. Easter
reaster@comptechnews.com

Robert B. Easter wrote:

> I want to store the full text of papers, news items, notes, comments, etc that
> can be of any length.  I don't want to use large objects to hold every single
> thing.  I've also realized now that large objects are only truly needed if you
> have to store binary data.
>
> Large objects have all these problems:
> 1. Cumbersome to work with - functions are not supported everywhere.
> 2. Nonstandard.
> 3. Will not dump using pg_dump.  A special program must be used.
> 4. Each lo creates a file (uses inode), which can limit the amount of
>         data you can store eventually.
> 5. Slower?
>
> I'm thinking I can store large amounts of text using the VARCHAR type, which
> is standard.  I could take a news item or paper etc, and break it down into
> chunks that go into 'pages' in a VARCHAR.  With a limit of 8k on a tuple, I'd
> be limited to about VARCHAR(7168) for a page, which is ok but more would be
> better.  I'd want to leave some bytes left over for other attributes.  If I can
> use 16k or 32k tuples, then a lot of the time, a news article, etc would fit
> into just one page/tuple.  Anyhow, I'm trying to decide on a text storage
> scheme that will be flexible and uses standard database features. Once decided
> I'll be locked into it.  I wish I knew how other sites store large amounts of
> news and other text in databases.

This is a piece of the postgreSQL FAQ :

4.6) What is the maximum size for a row, table, database?

Rows are limited to 8K bytes, but this can be changed by editing include/config.h and
changing BLCKSZ. To use attributes larger than 8K,
you can also use the large object interface.

Rows do not cross 8k boundaries so a 5k row will require 8k of storage.

Table and database sizes are unlimited. There are many databases that are tens of
gigabytes, and probably some that are hundreds.

In include/config.h, this means editing the following line :
#define BLCKSZ 8192
to
#define BLCKSZ 32768

and then rebuilding postgreSQL.

About VARCHAR : the people from postgreSQL prefer the usage of the 'text' datatype. It
is a variable length
field on which you do not have to put an upper boundary.

Good luck,

Jurgen Defurne
defurnj@glo.be