PostgreSQL Big Text Column Performance

postgresql rails-postgresql
Thomas Tremble picture Thomas Tremble · Sep 5, 2011 · Viewed 10.9k times · Source

I am storing sent emails in a RDBMS including the to address, from address, and email body.

The body can really be any arbitrary amount of text, and I won't ever care to search on it.

Are there any performance issues I should worry about when having a potentially large column that isn't used too often in one of my most frequently accessed tables (Emails) ?

(This project is written in Rails)

Answer

SingleNegationElimination picture SingleNegationElimination · Sep 5, 2011

postgresql stores large objects in a secondary area. You can read about it here: TOAST. The main concern will be keeping the large object out of the select list of queries that return many rows, so that you avoid visiting the secondary storage area.

If and when you do decide to add search functionality to the body text, you will need to use a full text strategy, which is well supported in Postgres, but is somewhat non intuitive. The topic receives a full chapter of treatment in the manual.

Related questions

Can't find the 'libpq-fe.h header when trying to install pg gem

I am using the Ruby on Rails 3.1 pre version. I like to use PostgreSQL, but the problem is installing the pg gem. It gives me the following error: $ gem install pg Building native extensions. This could take a while... ERROR: …

Read More
How to solve privileges issues when restore PostgreSQL Database

I have dumped a clean, no owner backup for Postgres Database with the command pg_dump sample_database -O -c -U Later, when I restore the database with psql -d sample_database -U app_name However, I encountered several errors …

Read More
PostgreSQL date() with timezone

I'm having an issue selecting dates properly from Postgres - they are being stored in UTC, but not converting with the Date() function properly. Converting the timestamp to a date gives me the wrong date if it's past 4pm PST. 2012…

Read More