PostgreSQL: Performance difference between VARCHAR and VARCHAR(n)

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:

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.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of