If possible, you can avoid to provide a length modifier to VARCHAR.
I have found that many times we actually don’t require to add any restriction on VARCHAR length.
The main performance difference between VARCHAR and VARCHAR(n) is, VARCHAR don’t require to check any length rule and VARCHAR(n) require to check length rule before any execution.
Using VARCHAR you can avoid unnecessary cycle for checking internal default validation or rule.
PostgreSQL also supports TEXT so even TEXT is a bit faster than VARCHAR.
Now I discuss one of the maintenance related point:
Imagine that, Your table has one VARCHAR(10) column and later you require to change the size up to VARCHAR(30) and table size is around 10 GB.
For this requirement you require ALTER command which may take long time to execute because of the big size of the table.
In this case I suggest this, use VARCHAR or TEXT and perform require limit validation from Front-end or write one CHECK Constraint on this column.
CHECK Constraint like:
ALTER TABLE MyTable ADD CONSTRAINT con_chk CHECK (length(col) < 30);
You can modify or drop such a constraint without disturbing to table definition. This performs faster because this doesn’t require any rewrites on table pages.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.