Re: blobs

The reason I am asking why you think it is a bottleneck is because many MySQL users see 'reading' a table as a bottleneck, and this is usually because they are using MyISAM tables.  Under MyISAM tables readers block readers, writers block readers, readers block writers.  This is NOT the case with PostgreSQL, readers never block other readers, writers never block readers, and readers never block writers.  PostgreSQL does this using multi-versioning, similar to how Oracle works.

Okay, I would love to see your test case.  My statement is based on the suggestion that you were embedding your data in the same row as a bytea column.  Here is a test case that I ran:

NOTE: Fill factor is set to 10% to exaggerate a wide row, this also proves the point that EMPTY space in a table affects performance.

drop table foo;
create table foo (x integer not null primary key, y text not null) with (fillfactor = 10);
insert into foo (x, y) select generate_series(1,100000), repeat('1234567890', 10240);
checkpoint;
analyze foo;

select relpages,reltuples,pg_size_pretty(pg_relation_size(relname)) from pg_class where relname = 'foo';
 relpages | reltuples | pg_size_pretty
----------+-----------+----------------
   100000 |    100000 | 781 MB
(1 row)


-- fetch just the integer column
explain analyze select x from foo;
                                                   QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..101000.00 rows=100000 width=4) (actual time=10.389..19288.848 rows=100000 loops=1)
 Total runtime: 19700.804 ms
(2 rows)

-- fetch just the text column
explain analyze select y from foo;
                                                   QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..101000.00 rows=100000 width=32) (actual time=9.234..19863.485 rows=100000 loops=1)
 Total runtime: 20290.618 ms
(2 rows)

-- fetch both the integer and text column
explain analyze select * from foo;
                                 
------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..101000.00 rows=100000 width=36) (actual time=40.478..20470.648 rows=100000 loops=1)
 Total runtime: 20904.611 ms
(2 rows)


-- Runtime is the same for all three cases against the "wide" table.  Disproves the theory that selecting or not selecting a "column" has something to do with performance.  I would think logically it does when you are talking about sorting and merging data sets, not when we are talking about physical reads.
-- Now let's seperate the "large" text column from the rest of the data.

drop table bar;
create table bar (x integer not null primary key);
insert into bar (x) select generate_series(1,100000);
checkpoint;
analyze bar;

select relpages,reltuples,pg_size_pretty(pg_relation_size(relname)) from pg_class where relname = 'bar';
 relpages | reltuples | pg_size_pretty
----------+-----------+----------------
      441 |    100000 | 3528 kB
(1 row)


-- fetch from the "skinny" table
explain analyze select * from bar;
                                                 QUERY PLAN                                                 
-------------------------------------------------------------------------------------------------------------
 Seq Scan on bar  (cost=0.00..1441.00 rows=100000 width=4) (actual time=19.552..416.575 rows=100000 loops=1)
 Total runtime: 790.901 ms
(2 rows)

-- Notice how must faster it is, seems to suggest that a table with a narrow column with is faster.

I think this case proves the point that a "wide" blob if not accessed frequently shouldn't be included in the core table.  Naturally this makes no sense if every single time you are going to fetch the blob and use it with every single fetch against the table.

I think you are referring to large objects, which are completely different then your original statement.  Your original statement seemed to ask about including binary data in the table versus not in the table.  My understanding of large objects is they create a file on the filesystem (probably an oid like a table does) and you store the oid of the "file" in a table.  Basically you have bytea (byte arrays) which are stored inline with the rest of the row and large objects where store a reference to another object.