PostgreSQL Big Text Column Performance

Question

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)

SingleNegationElimination · Accepted Answer · 2011-09-05 16:56:17Z

14

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.

answered Sep 5, 2011 at 16:56

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cerin Over a year ago

That link doesn't mention it, but about full text search, you'll want to setup a GIN index on the text column. With that, full text search is insanely fast. I don't know why more people don't mention this PostgreSQL feature.

mu is too short · Accepted Answer · 2011-09-05 18:04:08Z

10

No, you don't need to worry about that.

Technically there is no difference in storage between a e.g. varchar(5) and a text column.

Quote from the manual

there is no performance difference among these three types, apart from increased storage space when using the blank-padded type

The three types mentioned there are char, varchar and text. Where char is the "blank-padded type".

edited Sep 5, 2011 at 18:04

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Sep 5, 2011 at 16:55

user330315

2 Comments

SingleNegationElimination Over a year ago

That's not really true; a varchar(5) is constrained to 9 bytes of storage, but a text (or plain varchar, without a size) can exceed the page size, which puts the actual data out of the page altogether.

user330315 Over a year ago

@TokenMacGuy: no, there is no difference in storage between those two. With the only exception that a varchar(5) will never be "toasted" as it does not exceed the threshold for compressing the value. 5 characters stored in a varchar(5) column are no different to 5 characters stored in a text column.

Jcyrss · Accepted Answer · 2025-02-09 04:27:54Z

Even Postgres has TOAST feature, for the projects that performance is very import consideration (like large number of users), I still recommand maybe don't do that.

There should be some other systems sitting in front of database server. Like reverse proxy, application server (Rails in your cases). That will make large data transferring between those systems, create impact on overall system performance.

We could put those data on other systems, like cloud Object Storage/CDN services, and save links and first n chars of them in database.

In the front end, first show user first n chars and a link of click to view full info. If user click it, retrive content from other system.

Of cause, it will bring extra design/implementation complexity. But that is tradeoff we should think about.

Collectives™ on Stack Overflow

PostgreSQL Big Text Column Performance

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related