I have a large text corpus that I am storing in Postgres. A table articles has all of the metadata for each article, about 19 million rows. It's primary key is articles.doc_id. Another table (texts) stores the texts themselves, with a text column for the document text, and a column texts.doc_id which is a primary key that is also a foreign key to articles.doc_id.

Is this the best way to store a large amount of text, or would I better off just having a another column on the articles table with the text?

  • 1
    How big are the texts? If they are big enough to almost always be TOASTed, then I can't much point in splitting them into a separate table manually just for TOAST to split them out again. – jjanes May 22 at 22:34
  • having the large texts in the same table makes lazy use of select * painful, but other than that I can't see a down side. – Jasen May 23 at 0:28
  • @jjanes The texts are almost always a few hundred words long; in many cases thousands. – Lincoln Mullen May 23 at 2:59
  • A few hundred words is not very long. The problem there is that they might not be long enough to trigger TOAST (and so get them stored out-of-main), but still long enough to annoyingly bloat your table. This is exactly the condition that might be solved by splitting the table. You can create the table with just the text values and the key value, and then inspect the size of the table proper versus the size of the associated toast table. If the table proper is small, then you know that splitting it yourself didn't do much. – jjanes May 23 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.