Postgresql – Is varchar(x) as fast as `text CHECK ( char_length(x) )`

check-constraintspostgresqlvarchar

In an Twitter exchange, Simon West asks to Brandur,

Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before

Brandur responds,

Excellent question!

(1) VARCHAR and TEXT are equally performant in Postgres (see the "tip" box at https://www.postgresql.org/docs/current/static/datatype-character.html …).
.

(2) If you ever want to change the length, ALTER TABLE requires an exclusive lock (see https://www.postgresql.org/docs/current/static/sql-altertable.html …). Changing CHECK is instant.
When answering a question that called into question text CHECK (char_length(email) <= 255) vs varchar(255)

Is the first claim (bolded) of the two claims made strictly true?

If interested in the second claim check out this question.

Best Answer

No, it's not strictly true. Here is what the linked docs say,

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

So essentially

  • There is no reason to use varchar over text when the column is unconstrained. This is not true in some databases, namely ones that implement schemes like VARCHAR(max).
  • When it's constrained in a column, it's slower. There is a reason to use varchar(x) over text CHECK ( length(x) <= x)

Example

You can easily see this,

\timing 1

CREATE TABLE foo (
  x varchar(255) NOT NULL
);

CREATE TABLE bar (
  x text NOT NULL 
    CHECK (char_length(x) <= 255)
);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

Note, char_length and length are the same here. They internally call textlen.

Results

test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1156.529 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1268.869 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1107.869 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1283.043 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1121.788 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1238.194 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1116.421 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1248.272 ms

In each case, the insert into bar is slower

Type-constraints rechecked

I would have though too that the checks would have carried over to tables using the same types. This kind of optimization may be possible some day, but alas no joy,

test=# CREATE TABLE foo2 ( LIKE foo INCLUDING ALL );
Time: 7.143 ms
test=# CREATE TABLE bar2 ( LIKE bar INCLUDING ALL );
CREATE TABLE
Time: 10.762 ms
test=# INSERT INTO foo2 TABLE foo;
Time: 3613.517 ms
test=# INSERT INTO bar2 TABLE bar;
Time: 4061.650 ms

Though the varchar form is still noticeably faster.

Other Notes

As it relates to emails the subject of the tweet, this post may also be useful.

Related Question