This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If possible, you should avoid providing a length modifier to VARCHAR.
I found that many times we don’t require to add any restriction on VARCHAR length.
The main performance difference between VARCHAR and VARCHAR(n) is, VARCHAR don’t need to check any length rule, and VARCHAR(n) requires 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.
If you are doing maintenance then?:
Imagine that, your table has a VARCHAR(10) column, and later you require to change the size to VARCHAR(30) and table size is around 10 GB.
For this requirement, you need ALTER command which may take a long time to execute because of the vast size of the table.
In this case, I suggest to use VARCHAR or TEXT and perform the limit validation from Front-end or write one CHECK Constraint on the column.
CHECK Constraint like:
|
1 |
ALTER TABLE MyTable ADD CONSTRAINT con_chk CHECK (length(col) < 30); |
You can modify or drop such a CHECK constraint without disturbing to table definition. It performs faster because it doesn’t require any rewrites on table pages.
