You are referring to pg 7.4. In modern versions, they take up 1byte+length if you have <126 bytes. Also note that the reason strings are much slower is often that collation-sensitive comparison is hugely expensive - not that the string takes more space. But the end result is the same, of course.
– Magnus HaganderFeb 27 '10 at 12:32

@Magnus - thanks for the heads-up. Feel free to edit my answer as I see you have enough rep points.
– Robert MunteanuFeb 27 '10 at 21:48

"not that the string takes more space"... strings of characters above minimal sizes take up a heck of a lot more space than even high-precision numbers, because a number (singular) has a fixed unit, strings are always aggregate types. 8 bytes for a 64-bit number 4 bytes per-character in a string, including either a length byte or struct; or another terminator character for incredibly naive implementations...
– MrMeseesMay 13 '16 at 4:59

@RobertMunteanu Hey Robert, apologies I know this is an old post but can I kindly check...on the following: in order to query integers, i have to link each string column to another table (relationship). however, that means more joining operations are required for each query. How do i determine if this trade-off is worth it? Thank you!
– AiRiFiEdMar 6 '17 at 8:45

It will be a bit faster using an int instead of a varchar. More important for speed is to have an index on the field that the query can use to find the records.

There is another reason to use an int, and that is to normalise the database. Instead of having the text 'Mercedes-Benz' stored thousands of times in the table, you should store it's id and have the brand name stored once in a separate table.

Could you explain more? Do you mean instead of Mercedes-Benz to store thousands of times id 1. For example table car_brands, columns Brands and Id. Row Mercedes-Benz and 1. And in main table column Brands and value 1. And when SELECT, then at first get Id from table car_brands and then SELECT Something FROM main_table WHERE Brands = (SELECT Id FROM car_brands WHERE Brands = Mercedes-Benz). Or some other approach?
– AndrisJan 12 '15 at 4:56

3

@user2118559: Yes, that is how you would store it. To get the data you would generally use a join rather than a subquery: select something from main_table c inner join car_brands b on b.Id = c.Brands where b.Brands = 'Mercedes-Benz'.
– GuffaJan 12 '15 at 9:12

Why the downvote? If you don't explain what it is that you think is wrong, it can't improve the answer.
– GuffaMar 29 '16 at 21:43

Breaking down to the actual performance of string comparison versus non-floats, in this case any size unsigned and signed does not matter. Size is actually the true difference in performance. Be it 1byte+(up to 126bytes) versus 1,2,4 or 8 byte comparison... obviously non-float are smaller than strings and floats, and thus more CPU friendly in assembly.

String to string comparison in all languages is slower than something that can be compared in 1 instruction by the CPU. Even comparing 8 byte (64bit) on a 32bit CPU is still faster than a VARCHAR(2) or larger. * Again, look at the produced assembly (even by hand) it takes more instructions to compare char by char than 1 to 8 byte CPU numeric.

Now, how much faster? depends also upon the volume of data. If you are simply comparing 5 to 'audi' - and that is all your DB has, the resulting difference is so minimal you would never see it. Depending upon CPU, implementation (client/server, web/script, etc) you probably will not see it until you hit few hundred comparisons on the DB server (maybe even a couple thousand comparisons before it is noticeable).

To void the incorrect dispute about hash comparisons. Most hashing algorithms themselves are slow, so you do not benefit from things like CRC64 and smaller. For over 12 years I developed search algorithms for multi-county search engines and 7 years for the credit bureaus. Anything you can keep in numeric the faster... for example phone numbers, zip codes, even currency * 1000 (storage) currency div 1000 (retrieval) is faster than DECIMAL for comparisons.

Index or not, int is a lot faster (the longer the varchar, the slower it gets).

Another reason: index on varchar field will be much larger than on int. For larger tables it may mean hundreds of megabytes (and thousands of pages). That makes the performance much worse as reading the index alone requires many disk reads.

For example of 5 millions records of "audi", wouldn't the index only hold only one copy of string of "audi" and 5 millions integers of primary_key? Would the size difference really be that large, be it vchar or integer?
– lulalalaMar 10 '17 at 3:03

Performance wise, ENUM should perform more or less the same as int in the search field, but as varchar in the target list (because it has to transfer the whole string to the client for matched rows, not just the int)
– Magnus HaganderFeb 27 '10 at 12:33

Somewhat relative.
Yes, INTs will be faster, but the question is if it is noticeable in your situation.
Are the VARCHARs just some small words, or longer texts? and how many rows are in the table? If there are just a few rows it will most likely be entirely buffered in memory (when requested often), in that case you wont notice much difference. Then of course there is indexing, which gets more important when the table grows. Using SSD's might be faster then HD's with optimized queries. Also good disk-controllers sometimes speed up queries >10x . This might leave room for just using VARCHARs which makes reading and writing queries easier (no need to write complex joins) and speed up development.
Purists however will disagree and always normalize everything.