Обсуждение: Embedded text column versus referenced text

I am in the design phase of a new db so I cannot test queries using
explain/analyze yet, but regarding performance, is there any
difference in doing this:

CREATE TABLE something (name text, age smallint, ...other columns...,
comment text);

compared to this:

CREATE TABLE comments (id serial primary key, comment text);
CREATE TABLE something (name text, age smallint, ...other columns...,
comment integer REFERENCES comments(id));

?

The comments field will be used here and there but I expect it will
most often be NULL.


--
- Rikard

On Wed, Aug 4, 2010 at 11:05 AM, Rikard Bosnjakovic
<rikard.bosnjakovic@gmail.com> wrote:
> The comments field will be used here and there but I expect it will
> most often be NULL.

If it is most often NULL it won't really take up much space in the
table.  However, if when it does have content the contents are large,
and are not relevant to the bulk of queries about the "something",
then having it separate is probably a good idea, especially if you are
using an ORM that will want to pull that data in every time you load
the object.  It really depends on how much you use the column and how
you use it.

On 04/08/10 23:05, Rikard Bosnjakovic wrote:
> I am in the design phase of a new db so I cannot test queries using
> explain/analyze yet, but regarding performance, is there any
> difference in doing this:
>
> CREATE TABLE something (name text, age smallint, ...other columns...,
> comment text);
>
> compared to this:
>
> CREATE TABLE comments (id serial primary key, comment text);
> CREATE TABLE something (name text, age smallint, ...other columns...,
> comment integer REFERENCES comments(id));
>
> ?
>
> The comments field will be used here and there but I expect it will
> most often be NULL.

PostgreSQL will store any non-null comments fields out-of-line in
compressed form automatically, using the TOAST mechanism. You can
control how and when it does this, but usually you should let PostgreSQL
decide since it'll do a very good job.

See:
 http://www.postgresql.org/docs/current/interactive/storage-toast.html

I'd avoid separating out the comments. Just leave the comments field out
of your field-list in select statements when you don't need the comments
to avoid the cost of fetching and detoasting the comments, transferring
them over the network, etc.

If you're using some kind of ORM system, you'll need to set the comments
field to lazy-loaded or, if the system doesn't support lazy-loading
fields, you will have to separate it out. Hopefully though you won't be
going through the pain and suffering of using an ORM system.

--
Craig Ringer