Yeah, I used to do the same thing, I had dozens of limited VARCHAR fields and sometimes I forgot to change them when my application validation logic changed, and then we'd get errors from length limits being hit. It was a very liberating day when I realized I didn't need to have the length limits in database and that it made no performance impact, I went through and removed them all promptly. Life has been better since.

Does Postgresql have index size limits? I know in SQL server one thing that can bite you using varchar(max) is that if later you want to index it, it can fail if anything is over 900 bytes as that is the index size limit.

Its a run-time error too so the index may get created no problem then later someone tries to insert a >900 bytes value and it throws.

Even if it doesn't have index size limits, I can't imagine that it doesn't have row size limits and some kind of off-row storage or multi-page storage with resulting overhead.

Looking at the doc[0], I see this:

> PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code.

So, yeah, large values are stored differently and there's an impact on performance.

Sorry, a blanket statement like "you should use varchar with no length limit" seems extremely ill advised to me. Like, sure, you don't want to use varchar(30) for last names. But varchar(255) is probably going to do you just fine.

[0]: https://www.postgresql.org/docs/current/storage-toast.html

IIRC you can still set the index, it's just that you can't do matching if the match/content is over the length and it can still do begins with matching.

That said, I still don't get why they didn't just change the behavior for TEXT/NTEXT vs adding (MAX) options.

edit: I am mistaken, when setting an index, the index max is now the insert limit (from response below).

If your referring to SQL Server you get on insert "Operation failed. The index entry of length 901 bytes for the index 'xyz' exceeds the maximum length of 900 bytes."

Thanks. It's been a long while since I've dealt with the situation.

roughly 2700 bytes, from a bit of googling.

(off the top of my head, the most likely use case for 2+kb text fields would be things you would be using pg's full text index on anyway, not the usual btree type indexes as you'd want to speed up a join or whatever.)

I agree, but sometimes its not something you think about and when every says just use varchar(max)/text then later on you want to speed it up with a btree, oops. IMO its better to set the length if it might be indexed to let everyone know ahead of time not to go over.

I am not that familiar with PG full text index types, but in SQL server the full text index doesn't really work well with exact/begins with searches like the b-tree so your kinda stuck in that situation.

With SQL server I like to stick with varchar(255) as it gives head room for unicode indexing and at one point there where some internal optimizations for less <=255 due to the legacy max being 255, not sure anymore.

Beyond 255 its much less likely to need a btree and becomes more of a full text issue.

Last time I looked it is limited to the page size (so about 4kb on the boxes I played with). If you try and put more than that into an indexed field it throws an error.

I did not know about this limitation in SQL Server, thanks!

Until a troll uploads the Bee Movie script as a first name.

Use `CHECK length(the_column) < some_maximum`. It has the benefit that you can change it without having to redefine views.

Woah that's my go-to, I had no idea I wasnt the only one doing this.

The Bee Movie is truly the lorem ipsum of tomorrow.

... or the Bee Movie as a base64 encoded string.

But every time there's a "b" in the output string, the lyrics to All Star are added in plaintext.

we call this beecryption

bcrypt

... of a scrypt

There are other advantages of setting (large) limits that are appropriate to your data model, at least if you take data from untrusted users. You'd be surprised by the kinds of things users will put into truly unlimited fields... But yeah, there's no reason to try to figure out whether 32 characters or 64 characters would be better. But a title field probably never needs to be bigger than a couple thousand characters, for instance.

That should be rejected at the API level already. It's possible to do that at the DB layer as well, but who's to say that there isn't another class of users that has higher limits.

To the first part: sure, if you're confident a single API service will be the only thing inserting into the DB, then you can skip the DB level limit. I think you may as well add it, since you've already put thought into what you want the API-level limit to be, so why not stick that same value in one more place.

To the second part: that way lies madness. It's true that you don't know all your future use cases, but it isn't a good reason to leave things unrestricted. It is much easier to increase the limit than to lower it (because you are likely to be deleting data if you lower a limit, which is a tough pill to swallow).

Totally agree, especially with larger companies there are integrations to ERP, data ware houses, partners etc. and then it's often a good idea to have communicated lengths on your domain data (title, email, ...).

In case more than one service writes to the same table, either both should be under control of the same organization, or a stored procedure layer should be introduced for the external org (there will probably be more things to check that just the limit). The phrase "untrusted users" is concerning in GP's post.

For users of older postgres deployments (<9.2) it's worth noting that changing the limit on varchar is not free, it triggers a table rewrite.

With Postgres, you can have code running on the user's machine access the database directly, either via postgrest, or even via an old-fashioned database driver. This is not a problem because Postgres has a very fine-grained rights management and supports row-level security, preventing the end user from accessing any data they are not authorized to.

Of course, you mention the stored procedure layer that is required to make this work, so you're probably already aware of this. In that case, remember that you might not need views over literally every table in the database, and even in the case where you do use a view or stored procedure, it might still be much more convenient to do the length limit check on the table rather than cluttering every stored procedure that writes to the table with the same length limit check.

Yeah but what it state here is that if you want to add a limit on field size, then add a check constraint... Much easier to deal with than changing type

You're right, that seems better.

Coming from other database systems I found this intriguing, and did some googling. Turns out there are historical reasons that PG has functionally overlapping text and varchar(n) datatypes, where text was the original still the best datatype as detailed in this thread, whereas varchar was added for ANSI compatibility, and does not enjoy the same system support (changing length is a pain compared to other engines).

PG is going to be the engine of choice for my next project, so I'm happy to learn about this quirk compared to other engines. Which doesn't mean I'm all that happy about it. I'm really in agreement with this post from 2010 (still current AFAICT) that wishes the support for varchar(n) would be better, for readability and cross-engine compatibility nothing else [0]

[0] https://www.postgresonline.com/journal/archives/154-In-Defen...

For contrast, MS SQL Server has `NVARCHAR(MAX)`. It's generally bugged me with MS-SQL that they added `NVARCHAR(MAX)` rather than just changing the behavior of `TEXT`

It's because text and ntext are extremely old LOB types, and they support SQL statements like WRITETEXT and UPDATETEXT that other statements don't. They also can't be indexed, don't support functions like LEFT(), REPLACE(), etc. Text and ntext behave wildly differently than char and varchar while varchar(max) and nvarchar(max) are almost the same. Changing that functionality would cause problems. Further, neither text nor ntext are ISO standard data types.

It's the same reason that SQL Server has datetime2 and Oracle has varchar2 (the latter of which is only now being merged into varchar). That's why float, real, and money still exist even though numeric/decimal was created. They behave differently and in ways that are not exactly backwards compatible. In order to prevent your customers from having to rewrite their application just because you updated your data types, they chose to add new data types.

This is one of my complaints with ORMs these days: they’re starting to feel a lot less sophisticated than the databases, and data, that they sit above.

The thing with an abstraction later is that it has to accommodate for the simplest component.

In this case (the no need to specify the len on your varchars) is an implementation detail of Postgres, but the ORM still has to deal with weaker components.

Also for those interested, there’s no difference between varchar and text in Postgres, I just use text fields now, since all length checking is done at the application level, and Postgres is the only DB I need to support

There are two ways to built abstractions: either by defining an interface that represents the lowest-common-denominator of functionality such that all the underlying implementations already implement it; or by defining the interface that you want to have, where some implementations can implement it directly, and others require a shim/polyfill implementation, or where the implementation “adapter” will just plain throw a NotImplementedError at your attempt to use the advanced features of the interface.

Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found.

Have you ever seen an ORM that allows you to fluently deal with errors raised by triggers? DB-side computed columns [by storing to a table but querying from a related view]? Where the ORM’s “objects” expose an OOP interface to calling DB-side stored procedures that take that row-type as an input? (Or, even more crazily, where you could define a stored procedure in an abstract fluent AST against the object, and have it registered with the database? Or how about exposing DB-side BLOB handles as native-to-your-runtime IO streams, that could be fed into any runtime function that expects such?

There’s really a lot you could do, that’s still abstract to the SQL level (rather than specific to a particular database) if you were okay with some implementations of your interface not supporting every feature.

ORMs are great for quickly standing up a database, but if you need advanced features you should probably just use raw SQL. Most ORMs even make this very simple and safe.

Simple to say in a language with dynamic typing; but part of the reason I use an ORM (when I do, which isn't always) is to be able to guarantee that there is never a mismatch between DB types, and business-layer types.

Usually this means that I have a set of proxy types in the business layer that directly represent the DB types—table row types, table composite-primary-key types, view row types, stored procedure input-tuple and output-tuple types, etc.

Most ORMs support this sort of mirrored-schema modelling between the DB and business layer, with the ability to declare things like reference relationships where the ORM will throw an error if you get back a type you weren't expecting.

But when you drop to "raw SQL" in pretty much any ORM, you lose the ability to specify the business-layer-side input and output types for your expressions, and instead have to deal with sending and receiving the ORM's AST-like abstraction (usually plain arrays of scalars to map to bindings in the SQL statement.)

ORMs that provide a LINQ-like abstraction are a bit different, in that you can use SQL fragments within the fluent syntax to do things like call a stored procedure; and in those cases, they usually allow you to pass in typed arguments, and to specify the output type of the expression (for the rest of the fluent-syntax chain to use in interpreting it.)

While LINQ-like features are pretty nice, they still don't cover everything, because ORMs pretty much always execute their expressions within a transaction, and some SQL features (DDL statements specifically) can't be executed in a transaction. There are also certain types of SQL statements that most RDBMSes refuse to prepare with bindings—e.g. you can't make "which table you're querying from" a dynamic part of the query, even if you're willing to cast the result to a static "base" row type [as in PG's table inheritance.]

> Personally, I prefer the latter. But it seems like in the ORM space the latter approach is nowhere to be found.

sqlalchemy

Avoid like the plague.

At a risk of this being too far removed from the original subject, It'd be interesting to hear your reasoning behind this statement. I've used SQLAlchemy in some projects and did not find it troublesome, but that's just my experience, and I might've not pushed it enough.

We use it on a large monolithic application. It’s too brittle. It abstracts away the benefits of the underlying database all for the ability to support a handful of databases? Meh. The default queries or mode of constructing queries is so generic that it makes no sense to use: I miss the scalpel that is crafting my own SQL queries taking into account indexes and such running and tuning queries before committing them to code.

Look I was being hyperbolic but SQLAlchemy is great for prototyping but it’s then it’s too easy to just keep building on that crutch.

I hate them. They take out all the benefits of the database and add only cognitive load. I guess the case for them could be made in the same way that why would you write Assembly instead of C but to me an ORM is to the database like visual basic is to C

With Postgres, yes. Unless there's a real semantic need for a max length, you should always just use `text`.

Even then you should use text, just with a CHECK constraint. That's easier to change later than a varchar column.

Yes. The most complicated migrations I've ever done have involved increasing the length of varchar(n) columns, because every dependent view needed to be dropped and recreated (and all of their dependents, and so on, recursively) within the migration transaction, in the right sequence.

If I had just used the text datatype in the first place (along with a check constraint), these migrations would have been so much simpler.

You had views dependent on views? Object oriented database schemes sound problematic.

Nothing to do with object orientation. We just use a lot of views, both to abstract away common complex queries, and to allow external systems to interact with the data in a controlled way.

That makes sense actually. But stored procedures could do that too?

Yes. I tend to treat procedural code as a last resort, because pure declarative SQL is so much easier to troubleshoot.

I guess I use stored procedures differently. I rely on SQL queries not for loops if I can help it. Sometimes it can’t be avoided if you’re doing updates in chunks for example.

I think is super weird that databases can have developer-hostile designs like this and still be considered "world class". If a web all framework had pitfalls like these, people would rageblog them into oblivion. I'm not trying to single out postgres because I have the impression that the same holds for all databases over some age.

I mean, most of this article reads as a list of features postgres never should've had. If PHP can deprecate magic quotes, then why can't postgres do something similar? Why do the "string data type" docs contain anything other than the "text" type, outside a section titled "deprecated, do not use"? Why isn't there a strict mode that warns me when I'm trying to use Bad Ideas From The Nineties?

Every single technology that lives long enough has legacy features like these and antipatterns. When compared to other software of similar age, this is remarkable little cruft.

Every tool that's powerful enough needs to have caveats. I agree it's a PITA, but this is orders of magnitude better than say, bash, C++, Linux, or PHP.

Strict mode sounds like an awesome idea. From time to time people release database schema linting scripts, those might help?

They can't deprecate varchar and some other ill advised field types because they are required to comply with the SQL standard.

The text thing, though, has never been a secret. Learning about it is sort of a coming of age ritual for postgres developers.

Postgres' warts are nowhere as unpredictable and problematic as magic quotes. Apart from those things, most RDBMs are incredibly stable and ironed-out workhouses and are nowhere as controversial than PHP is.

Even better, make a domain type encapsulating the constraint.

I sometimes use a limit (constraint or varchar(n)) to enforce an upper bound when the domain doesn't really demand one, but you'd rather get an error that a longer string in your database. Say, 100 characters for a login name or 1000 characters for a real person's name.

Even if somebody actually shows up with a longer name, you know that your application will have other problems in that case (e.g. the UI explodes or something), but you prevent the occasional bug or other problem from writing megabytes into the name field, and causing follow-up problems.

So, does this mean there is no longer a good reason for fixed-width text fields in any databases? Why not?

Surely if it's variable-width, that means you can't guarantee it will be on the same page? And therefore there's a performance cost?

Indeed, that is the reason character fields have traditionally had a length in RDBMS. However, many of these optimizations get broken by unicode support.

Consider a CHAR(1) field. Without unicode support this field is always 1 byte, and therefore easy to store. With unicode support, however, the value that fits in this field is actually between 1-4 bytes. However, in many cases users will only store 1 byte in this field. Storing this field as a fixed length 4-byte field will in many cases result in a 2-4X increase in size and hence waste a lot of space. Thus even with CHAR(1) it is usually better to store the strings in a variable-length manner to avoid wasting space; and CHAR(1) is the absolute best case for this type of optimisation. This gets way worse with larger CHAR lengths.

It's also worth noting that even if we could store a fixed one byte that that is likely still not the best way of storing a CHAR(1) field. A CHAR(1) field is typically used to store types or categories. Typically, a column like this would have few unique values (~2-8 values), which means we can reduce the size of these values to 1-4 bits using dictionary compression instead of a single byte, resulting in a size savings of factor 2-8X over storing a fixed one byte length.

FWIW, I am guessing it's a rare case that you can be confident that all strings you'll want to store are of a specific length, but cannot predict the set of characters they may use.

The use cases for CHAR instead of VARCHAR that I'm thinking of would be things like serial numbers. CHAR(1) seems even more specific; the only time I've ever seen that used is for some version of enumerations with mnemonic keys.

> things like serial numbers

In Postgres, if I had a truly massive number of alphabetic-textual serial numbers (such that I was worried about their impact on storage/memory size), I’d define a new TYPE, similar to the UUID type, where such serial numbers would be parsed into regular uint64s (BIGINTs) on input, and generated back to their textual serial-number representation on output.

I don't think the problem is so much about predicting the encoding that will be used. Let's say you KNOW that non-ascii characters will be used in the field, but only very occasionally. That means the field has to be 4x as wide as it would have been otherwise even if non-ascii characters will almost never be used.

If you knew the characters were ASCII, you wouldn't want unicode support in the first place. It's pure overhead.

But you also wouldn't want char(n), because as the article notes:

char(n) doesn't reject values that are too short, it just silently pads them with spaces. So there's no actual benefit over using text with a constraint that checks for the exact length.

So even if you're using pure ASCII, you're supposedly better off just setting the character encoding appropriately and using a text or non-limited varchar field.

That said, I thought the main benefit of a char field (and its padding) was that you could use it in select instances where is was useful to have specific length records. E.g. if all the other fields of the table are fixed length, every record can be fixed length if you use a char(n). I'm not sure if that's a benefit or not to modern databases anymore though.

Well, it depends.

Some RDBMS's don't store the string's length with each value for fixed-width character fields. So a fixed-length field may still be preferable if the space lost to padding amounts to less than the space lost to overhead.

That distinction doesn't apply to PostgreSQL, because it stores a length along with the value even for fixed-with columns.

The problem is that even with serial numbers things aren't as fixed. You might have a different type of serial number, zip code, etc.. A check constraint can be expanded to do the appropriate length and formatting checks for most types. A strict length restriction just does one simple thing.

Same with name and address fields. I've seen databases where they set it at an seemingly arbitrary number, because that's the maximum length their delivery service could handle (UPS etc.), but then they added another one with different constraints…

The only performance cost is a worst-case cost. If your idea of performance is something like how many rows you can read/write/process per second, then the average length matters much more than the maximum observed length or the maximum permitted length.

As far as I know - it depends on how the optimizer/planner works as well (at least in SQL Server.)

If you have a known fixed width you can be certain about your memory grants required for a specific query (width * rows estimate) - if you don't know that ahead of time you might find you need to allocate more memory than is actually required.

This can be really bad for concurrency.

Postgres uses TOAST tables, an abstraction which allows PG rows to have unbounded lengths. Essentially, this works like a filesystem that supports extended attributes in both “small=intrusive” and “large=external extent” modes. When you have unbounded-length fields in your row-type definition, you end up with

• one “regular” PG table which has a row record type defined with a fixed length (like a filesystem inode), where all the static-length fields are held in the record itself, and any unbounded-length fields have a certain amount of space reserved for them in case they fit into that small space (if they’re even smaller, this space is just wasted);

• and one TOAST table—essentially a table of extents, that the unbounded-length fields of the previous record can, instead of holding data directly, hold pointers into.

TOAST for such columns only gets used when the column’s reserved size within its record gets overflowed; which I believe, for current PG versions, is 127 bytes. So, any row-type where all the fields are bounded to a length of at most 127 (either by literally using VARCHAR(127), or CHAR(127), or by using a TEXT column with a CHECK(length(col) < 128) constraint)—will never use TOAST, thus guaranteeing your stride width and memory bounds.

And, as well, any query that doesn’t try to “unwrap” the value of those unbounded-length columns, won’t have to join against the table’s TOAST table. So you can optimize for the memory bounds of specific queries on a table (by bounding the length of certain fields, keeping them out of TOAST) while leaving other fields as unbounded-length, if no hot-path query uses them.

(There’s also an extra optimization where values that are just barely too large to fit into the 127 bytes of reserved in-record space, are transparently compressed, to see if that’ll make them fit. Thus, although you need the length-check to guarantee that you won’t be using TOAST, it may turn out that your table “gets away with” having some values a bit larger than the bound without ever using TOAST.)

Excellent answer - thank you. I did some googling, and if you have any specific resources I would love to read more (I was just reading https://www.postgresql.org/docs/11/planner-optimizer.html)

A question - if the planner knows its joining to the TOAST table, and it has some predicate to evaulate, how does it know all the widths of all the rows so it doesnt over-grant(or are memory grants not a thing?)

Generally with statistics/histograms you get a sample and a max width (or something like that) - so I am just confused how it can be precise.

Thanks!

After many days of googling, it looks like postgres doesnt have this problem because it also cant use dynamic memory grants to get better performance on a per-query basis like SQL Server does.

So while you never suffer from "under" resourced queries, you generally dont get to see what they would look like if they had more memory.

In the SQL Server world that memory grant is part of the query plan, so that's why its a question that occurred to me.

Happily, the PG planner isn't thrown off by such things, at least in my experience. It has sample rows and uses them. (I really like the postgres planner, it's the best I've used.)

I've seen it be FAR wrong about memory, though, despite being right about column widths: When you join a few tables in a big select, the planner will look at some sample rows and guess the number of rows in each step of the possible plan, and if the sample is too small, that estimate can be wrong by a LARGE factor.

Use a CHECK statement to limit the length. Changing varchar length doesn't play well with dependent views.

>Seriously ? everytime I create a django charfield I try to guess in my head what is the best length for this field to optimize for performance, but sounds like using unlimited length (textfield) field doesn't make any difference. Good news

Pretty much, except if you're using ModelForms you'll end up rendering a different element by default, and the admin (if you use it) will be different. Not really a problem with DRF without the browsable API.

You can change the element for a field in widgets in the Meta class of a form.

That's strange. In SQL Server, the size of the text columns impacts performance [0] by changing the amount of memory that is allocated to run queries.

[0] - https://sqlperformance.com/2017/06/sql-plan/performance-myth...

As the article points out, that's not normally the case in SQL Server, it's that there are certain steps like sorting that require blocks of preallocated memory.

SQL Server is probably like that because varchar(max) is relatively recent; I'd expect as their support for it improves that issue will go away.

> I try to guess in my head what is the best length for this field to optimize for performance, but sounds like using unlimited length (textfield) field doesn't make any difference.

That used to be a good idea. I guess it's fallen the way of defragging, or "parking" your hard drive.

Charfield in django is varchar so while there is no performance difference vs textfield, it does put limits on your inputs. This is most likely a good security measure. Unbounded inputs can be found and exploited.

I’m pretty sure Django doesn’t actually create a limited char field that way, it merely adds validation. For exactly this reason.

It does pass the limit down. I've just verified my schema that has a varchar(128).

One of the reasons given in the bug for it being difficult to fix is down in the depths it assumes it is a varchar() -- with parentheses.

Here's the relevant comment:

https://code.djangoproject.com/ticket/14094#comment:13

I wonder if the same holds true for SQL Server? Data truncation errors bite us in the ass all the time where I'm at.