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