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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
