Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not.

I would like to give the question of VARCHAR size a little twist and hope to get some insight in this:

Given (multiline) free-text fields (not short stuff like names) that you want to store in an (Oracle) database, is there any point (wrt. performance or otherwise) in not maxing out the VARCHAR capacity (VARCHAR2(4000) on Oracle) but choosing a smaller value such as 1024 or 512 because that will likely be sufficient in 98% of the cases anyway.

It does impact memory usage, especially when a client program has to allocate enough memory to receive a dataset.

Bear in mind that a lot of apps (especially web apps) use UTF-8 which is a multi-byte character set. As such, you should really consider characters rather than bytes.

If I was expecting over a thousand characters, then I'd actively consider a CLOB. I'd be thinking about whether it will store plain text or some form of markup (wiki / html ?), usage with non-Euro languages. The Questions and Answers here, for example, would be CLOB, but comments can fit in a VARCHAR.

If you max out a VARCHAR, then in six months someone will want to make it bigger again, and you'd be kicking yourself for not using a CLOB.

StackExchange Database Administrators Q#4479, answer score: 11