11 ms·
PostgreSQL: Don't use CHAR or VARCHAR
- nness 13y agoUh, shouldn't you use the most appropriate type available to describe your data, since that will simplify the process if you ever need to migrate to a different DBMS?
- deleted 13y ago[deleted]
- bunderbunder 13y agoNowadays the most appropriate type available to describe your data is a non-standard type that's specific to the RDBMS you're using, often as not. With SQL databases, you can generally only pick one of the following: 1. Any kind of expectation of hassle-free migration to a different RDBMS. 2. Benefiting from much of anything that's happened in any dialect of SQL since about the time Windows 3.1 hit the market. Personally, I generally prefer #2, because #1 is kind of a myth anyway. For example, PosgtgreSQL's VARCHAR type has different semantics from Oracle's: One supports Unicode and the other doesn't.
- Pxtl 13y agoMicrosoft follows Oracle's approach and uses NVARCHAR to describe their infernal 2-byte format that might be UCS2-wrongendian or it might be UTF-16 depending on their mood and the tool you're using at the moment. Naked VARCHAR means you have to pick an 8-bit character-set like a savage.
- bunderbunder 13y agoYa, or for Oracle you might be better off using VARCHAR2, which uses UTF-8. That way you aren't mired in savagery, but don't have to pay the performance hit of storing 2 bytes per character for text that's mostly in western European languages. Whereas SQL Server users are stuck choosing between doubling up on I/O and suffering codepages. Meanwhile in PostgreSQL you just use regular VARCHAR and pick utf8 as your character set like a proper subgenius. Fun fact: In earlier versions of Portal, it was database portability that GlaDOS promised to give you after the experiment.
- dragonwriter 13y agoA few observations: 1. While the linked blog post is new today, its mostly a link back to a different 2010 blog post. 2. The linked blogged post and the 2010 blog post basically discuss performance considerations that have been documented clearly in the PostgreSQL documentation for character data types since version 8.3 (and less completely for several versions before that) regarding the performance considerations ( CHAR(X) worse than VARCHAR(X) worse than VARCHAR and TEXT.) 3. The linked blog post says "don't use CHAR or VARCHAR", but really, it should be "don't use CHAR(x) or VARCHAR(x)". VARCHAR is pretty much identical to TEXT.
- Jonanin 13y agoRegarding 2: even if it has been documented for a while, I don't think it's very widely known. I still see a lot of models.CharField(max_length=x) in Django code, where x is usually an arbitrary value only chosen because CharField requires it.
- tcdent 13y agoProbably a result of supporting multiple backends. AFAIR, MySQL requires a length to be passed on VARCHAR.
- falcolas 13y agoYes, because varchar is implemented as a fixed-length list of characters. Text fields are implemented as blobs, and as such can grow large enough to have to be stored off-page (with all of the associated performance hits associated with that).
- rosser 13y agoFTFA: If you want to change the max length to be larger, postgres will have to rewrite the table, which can take a long time and requires an exclusive table lock for the entirety of the operation. As of (IIRC) 9.2, this is no longer true. If you alter a varchar column to be narrower than it currently is, you'll rewrite the table. If you make it wider, or convert from varchar(n) to text, you won't. EDIT: And if you're willing to hack the system catalogs instead of using an ALTER TABLE, you can even get around the rewrite to narrow a varchar(n) column. See: http://stackoverflow.com/a/7732880 http://stackoverflow.com/a/7732880
- susi22 13y agoI have to disagree. You should always put limits on everything, as said nicely here [1]: "Put a limit on everything. Everything that can happen repeatedly put a high limit on it and raise or lower the limit as needed. Block users if the limit is passed. This protects the service. Example is uploading files of logos for subreddits. Users figured out they could upload really big files and harm the system. Don’t accept huge text blobs either. Someone will figure out how to send you 5GB of text." Sure, you should ideally do this in your application code. But if there is multiple interfaces (such as a REST api etc) to your database then you have to remember to put them in place everywhere. I don't see a good reason to make a username field to be TEXT instead of a generous VARCHAR(300). If somebody wants to choose a longer username than that, he's probably malicious. It protects you with zero cost and allows you to make some user input sanitation mistakes (we're all humans) in your application code. [1] http://highscalability.com/blog/2013/8/26/reddit-lessons-learned-from-mistakes-made-scaling-to-1-billi.html http://highscalability.com/blog/2013/8/26/reddit-lessons-lea...
- zzzeek 13y agouse VARCHAR because constraints are a good thing, and use CHAR if you are storing strings of a fixed length, because semantics are a good thing. The point about padding being wasteful for variable-length data in CHAR is moot because CHAR is for storing fixed-size strings like state codes. As the PG docs say, there is virtually no performance difference at all between all three, so stick with standard practices.
- VladRussian2 13y ago> As the PG docs say, there is virtually no performance difference at all between all three Btw, there is a clear [though infrequent] case where using CHAR(X) vs. VARCHAR(X) may cause huge performance penalty with iron platter HDD. CHAR(X) will always contribute X to the row length (even if data value is Y < X, so VARCHAR(X) with such value would contribute Y) thus making it more probable to have your row TOAST-ed ( i.e. have parts of row stored separately in separate table) thus causing a noticeable (when only few rows are accessed) extra disk head lookup on non-cached data. Similar to C/LOB in-row limit exceeding on other databases.
- zzzeek 13y agolike I said, don't use CHAR for non-fixed-length data. Use it for short fixed-length strings.
- jeltz 13y ago> and use CHAR if you are storing strings of a fixed length, because semantics are a good thing The CHAR type is not what most people think it is. It is a blank padded string, not a fixed length one. Inserting 'a' into a CHAR(2) results in 'a ' being stored and retrieved from the database. You should always used VARCHAR or TEXT in PostgreSQL and never CHAR (at least I cannot think of a case when you would want it). CHAR is there for SQL standard compliance. Instead use one of these: field VARCHAR(2) CHECK (length(field) = 2) field VARCHAR CHECK (length(field) = 2) field TEXT CHECK (length(field) = 2) The rules for working with blank padded strings is in my personal experience a hell with non-obvious results when casting, sorting, comparing and other operations. EDIT: I can leave you with this little example. $ SELECT length('ab '::char(4) || 'cd '::char(4)); length -------- 4 (1 row)
- mariusz79 13y agoWhat if the performance changes? What if you decide to migrate to a different db at a later time? What if your software determines field types and sizes in the gui based on the database schema? IMHO always use the right field for the job..
- kiallmacinnes 13y ago> What if the performance changes? What if you decide to migrate to a different db at a later time? Couldn't agree more. Unless you're at the point where preventing the DB from performing a length check on data it receives is going to provide a tangible benefit this article is awful advice.
- sitharus 13y agoNothing is preventing you from adding your own check constraints, it's just moving from specifying the data storage as having a length to explicitly specifying the constraint.
- kiallmacinnes 13y agoI think you missed the entire point of the GP's message. Tomorrow, postgres could release an update that massively improves the performance of CHAR compared to TEXT. You can take advantage of that by using the correct (I.e. semantic) field. Add to that, project requirements change - yea, it happens. What if you need to port to mysql, mssql, oracle etc? Using the correct field types will make that easier, using a premature optimisation combined with a check constraint (blowing away any gains of that premature optimisation) makes that harder
- bratsche 13y agoIf you're thinking of migrating to another database later, you're basically ruling out using half of postgresql's more compelling features up front. I kind of don't understand this line of thinking.
- 13y ago
- DrJokepu 13y agoCouldn’t agree more. Database constraints should be thought of as the last line of defence against madness rather than as means to validate input. Database constraints are not really suitable to defend against attackers. Constraints might stops users from creating extremely large records but they won't stop users from creating an extremely large number of records etc. You need to sanitise your input thoroughly in the application layer.
- Kequc 13y agoThere is also a maintenance cost by putting restraints on the database as almost assuredly your application will have similar constraints. Therefore if those constraints change the workload is not just doubled because you have to update the application in two places. But perhaps tripled or more as the developer tries to find any other locations where the same logic might have been put as well. If the logic is in two places it might very well be in three or more.
- deleted 13y ago[deleted]
- epo 13y agoSilly example, who decides what is 'an extremely large number of records'? Whoever has a view about this should monitor and police the limit. If you are saying that you can't protect against every eventuality so you may as well guard against none, then that is asinine. Applications should enforce correct application behaviour regardless of user behaviour. Databases should maintain database integrity regardless of application bahaviour. If this requires duplicated effort then so be it.
- DrJokepu 13y agoI think it would be difficult to defend an argument claiming that constraints on data size help maintaining data integrity.
- rsynnott 13y agoClearly, this is an evil plot to make peoples' schemas break entertainingly in case they ever try to move to MySQL :)
- effn 13y agoThere is nothing evil in preventing people from migrating to MySQL.
- steven2012 13y agoThat's interesting. So can you put an index on a TEXT column in PG? From what I know, you can't do that in SQL Server/Oracle, you can only use full text searching (I think).
- Jonanin 13y agoYes, indexes behave the same on TEXT columns as they would on CHAR or VARCHAR ones.
- jeltz 13y agoNote however that normal b-tree indexes cannot index column values larger than 8191 bytes. The 8191 byte limit is applied after possible compression of the string.
- masklinn 13y ago> So can you put an index on a TEXT column in PG? Yes, but the default index (btree) will generate an error if you try to insert data above 8k. The hash index will work. Not that it makes much sense to use an index (rather than FTS) on such an amount of data.
- hobs 13y agoDoes pg have the concept of a clustered index? If so, for frequent inserts/updates it could actually matter.
- effn 13y agoIt has a index clustering operation but this is a one-time operation that will not affect further inserts/updates. But it shouldn't matter, the implicit constraint on a VARCHAR(n) does not affect indexing.
- hobs 13y agoInteresting, I was just thinking about a variable width item on the disk expanding or contracting, and if it had to be arranged in a serial physical order it would (necessarily) be forced to move the other bits if it wasn't inserted at the end.
- dancecodes 13y agoIm sorry but just examine pg_type (http://www.postgresql.org/docs/8.4/static/catalog-pg-type.html http://www.postgresql.org/docs/8.4/static/catalog-pg-type.ht...) - there all character columns in text? Sometime for performance (short keys) useful use fixed length i.e. CHAR(N)
- etler 13y agoSo is there actually any benefit to using text over varchar when the constraint is actually 0 to X, or instead of char when your input actually needs to be exactly X characters? It seems the real point of the article is make sure that these are really the constraints you want.
- poissonpie 13y agoI'm of the opinion that your data structure should model your data. If your piece of data is best represented by char or varchar, then use it. I'm not super familiar with Postgres, but among other things, modelling your data correctly helps when another developer has to step in and maintain your app. They can easily get a sense of how the presentation layer should look if you've done so.
- hmottestad 13y agoFrom my database course I learnt that nothing is slow in a database until you can't fit your join operation in memory. Having to do a a join operation on varchar(300) is predictable. Having to do one on text is unpredictable and can slow your system to a crawl if the operation needs to be done on disk rather than in memory. FWIK the article did not talk about joins at all. I would love if someone has a good article comparing what happens when you do a join on varchar vs text. EDIT: One question remains, how is the "text" stored when doing a join. For varchar you just make an array, because all values are the same length. With "text" do you first find the longest text and use that as the default array element size? This may not seem important with in memory operations, but seeking on disk is considerably faster when all your elements have the same size.
- icebraining 13y agoFor varchar you just make an array, because all values are the same length. No, they're not, that's why it's a VARCHAR and not just a CHAR. A varchar(n) field may have any length between 1 and n.
- jeltz 13y agoThe memory usage of a join has many more variables than the size of the joined field (in some cases it might not matter at all as long as that field is not included in the result set) so I would not say that joining on a short string is that much more predictable than joining on a possibly long string. What matters the most is what the query actually does, what the data looks like, and what indexes you have. Joins are usually implemented in one of these three ways, and the planner can select which one to use depending on the query and table statistics: 1. Hash join: Build a hash table of all the joined fields of set 1 and then traverse the rows of set 2 looking them up in the hash table. 2. Merge join: Sort both sets of rows and merge them. With the right indexes you may not even need to sort anything, just traverse in index order. 3. Nested loop join: For every tow in set 1 find matching entries in set 2.
- DharmaPolice 13y agoAs someone who regularly has to write SQL in 7 different dialects I much prefer VARCHAR to TEXT as the former is handled much more consistently across platforms. A TEXT column in MS SQL Server is an entirely different proposition to a VARCHAR column. VARCHAR on the other hand is treated with relative similarity between most of the systems I regularly use. Additionally, one of the key benefits of more explicit datatypes is documentation. Knowing that a column is 30 characters wide is useful information to have at hand (without having to check check constraints) and often reflects a business rule. Where joins have to be performed on character columns it also helps to know if both sides of the join are both (say) CHAR(8). If every text column is TEXT then that's much less clear. Check constraints help but you don't always know if they've been applied to all current data (some platforms allow for constraints to ignore current data). If I know a column is VARCHAR(50) then I am 100% certain that there will be no value longer than 50 in it.
- znowi 13y agoI wish more articles had tl;drs like this one. Very helpful. Often you have to skim through preludes and side stories with jokes to piece the gist together.
- kevrone 13y agoI've used PostgresSQL quite successfully for the past few years at rather large scales and I can tell you, using TEXT everywhere is sooooooooo much easier on everyone involved. Especially on large teams (hundreds of developers) where migrations are a big deal. And especially when the business teams are essentially dictating the use cases. Those people change their minds ALL THE TIME ("Yeah, I know we agreed that we only need about 20 characters for the description here, but we now think 25 will really make the whole thing pop, ya know?"). And as far as the argument for keeping schemas in strict SQL so that some future database switch can be made more smoothly...I mean c'mon. Even if you actually do that (who does that?) you're going to have more annoying things to do than replacing some TEXT columns to VARCHAR.