I adopted a Rails app (Rails 3.2 and Postgres 9.4) that has a few Rails strings and we have gone past the 255 limit. This app had previously used MySQL rather than Postgres as backing store. My understanding is that postgres handles strings and text the same. Is this correct? Are there any limitations that we should be aware of before migrating all our Rails strings to texts?
Issues of performance are a bit of a concern but not the dominant concern.
From the fine manual:
Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While
character(n)has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in factcharacter(n)is usually the slowest of the three because of its additional storage costs. In most situationstextorcharacter varyingshould be used instead.
The three types they're talking about are char(n), varchar(n), and text. The tip is essentially saying that:
char(n) is the slowest due to blank padding and having to check the length constraint.varchar(n) is usually in the middle because the length constraint needs to be checked.text (AKA varchar with no n) is usually the fastest because there's no extra overhead.Apart from the blank padding for char(n) and length checking for char(n) and varchar(n), they're all handled the same behind the scenes.
With ActiveRecord, t.string is a varchar and t.text is text. If you don't have any hard length constraints on your strings then just use t.text with PostgreSQL.
varchar (with no length) and text the same thing in postgresql?
t.string used to be varchar(255) but is now varchar whereas t.text is and has always been text. So yes, at the PostgreSQL level, text and varchar (with no n) are the same (as noted in bullet point three) but t.string and t.text weren't always the same thing at the ActiveRecord level.