1

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.

  • 1
    The claim in the quoted text seems to be the opposite of your worded question. – ypercubeᵀᴹ Nov 1 '17 at 16:54
  • 1
    This is something used in older Postgres version that did not optimize changing the length of a varchar column and had to rewrite the whole table. Back then changing the check constraint was less invasive then changing the data type. This is no longer the case since at least 9.0 I think, so this approach is pretty much not needed any more – a_horse_with_no_name Nov 1 '17 at 17:58
  • 1
    @a_horse_with_no_name I thought it wasn't 100% optimized. 9.1 release note say: " Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases (Noah Misch, Robert Haas) For example, converting a varchar column to text no longer requires a rewrite of the table. However, increasing the length constraint on a varchar column still requires a table rewrite." I'm not sure if this has been addressed in a later release. – ypercubeᵀᴹ Nov 1 '17 at 18:04
  • 1
    @ypercubeᵀᴹ: increasing the length of a varchar column on a table with 10 million rows only takes 17ms on my computer. I can't imagine that Postgres does a table rewrite in 17ms: i.imgur.com/LRxfwbt.png (that is with Postgres 10, but I am 100% this was the case with 9.5 already) – a_horse_with_no_name Nov 1 '17 at 18:16
  • 1
    @ypercubeᵀᴹ: this was changed in 9.2: "Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types" - so it's only for increasing the size, not reducing it. – a_horse_with_no_name Nov 1 '17 at 18:22
1

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.

  • The quoted "a few extra CPU cycles to check the length when storing into a length-constrained column" refers to the difference between VARCHAR and VARCHAR(n). Nothing to do with VARCHARvs TEXT. – ypercubeᵀᴹ Nov 1 '17 at 17:47
  • Right, it has something to do with VARCHAR(x) and text, because text is varchar. – Evan Carroll Nov 1 '17 at 17:55
  • 1
    Yeah, the difference you observe may be because the CHECK check is slightly more expensive than the VARCHAR(n) lenght check. Still, I'd take a sub millisecond diff on every INSERT over a possible table lock when I'll want to convert my VARCHAR(50) to VARCHAR(250). – ypercubeᵀᴹ Nov 1 '17 at 18:00
  • Sure, I'm not saying the compromise is right or wrong I'm just observing the "slight" difference. Personally, I don't make arbitrary constraints I never have to resize them, ever. If there is a constraint, I prefer varchar(x) because I like types. Actually, I prefer domains because I like explicit semantic types even more. – Evan Carroll Nov 1 '17 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.