On 17 Jun 2021, at 1:08, Tom Lane wrote:
> Martin Mueller <martinmueller@northwestern.edu> writes:
>
>> Are there performance issues with the choice of 'text' vs. varchar and some character limit? For instance, if I
havea table with ten million records and text fields that may range in length from 15 to 150, can I expect a measurable
improvementin response time for using varchar(150) or will text do just or nearly as well.
>
> There is no situation where varchar outperforms text in Postgres.
> If you need to apply a length constraint for application semantic
> reasons, do so ... otherwise, text is the native type. It's
> useful to think of varchar as being a domain over text, though
> for various reasons it's not implemented quite that way.
>
This reminds of my days converting from MySQL to PostgreSQL. MySQL, along with other databases, seemed to have a strong
preferencefor setting a length on character strings. And all this from before the advent of UTF encoding which has made
theconcept of string ‘length’ very messy.
Database guru and SQL author Joe Celko asserts in his ’SQL for Smarties’ that if he finds a text field without a length
limithe will input the Heart Sutra (presumably in ASCII :) to demonstrate the design error. (Of course he is ignoring
thepotential for this input to help the database achieve inner consistency. :) . But taking Joe’s central point there
doseem to be grounds for restricting user input text fields to a reasonable length according to the business need… if
onlyto limit the damage of a cat sitting on the keyboard.
My approach is to define such fields as ‘text’ and set a constraint using char_length(). This allows PG to do the
businesswith the text in native form, and only imposes the cost of any length check when the field is updated… best of
bothworlds.
Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong.
— H. L. Mencken, 1920