No, it is not really unlimited. In fact, it depends on the database type. Many databases have two different string types, (var)char and text. A database stores it data in so called 'pages'. One page can contain data of many rows, and normally the data of one table row is stored together. However, a page has a limit and also a row has a maximum of data. This also depends on the database type. Most of the databases have defined a limit of the char type. Data of type char is stored on the page the row is stored. Data of type text is stored on another place. The row only contains a pointer to the place the real text is stored.
When you always use the unlimited property, to retrieve an object the database must retrieve the data from different locations. The row is not stored on only one page. Conclusion: when it is not needed, do not use unlimited strings. The more unlimited strings you have, the more time it costs to retrieve your data. Use it only when it is sure the text will contain a lot of characters.
What is a lot of characters? When will a database store data in text fields and when in varchar fields? When you choose unlimited, the data will always be stored in text fields. But when you choose a limit of 80,000 characters, the data is also stored in 'text' fields. So above a specific value, it does not matter if you specify 'unlimited' or set the limit, because always the 'text' type will be used.
The limit for PostgreSQL is not clear. Strings are compressed and then stored. It depends on the data when data is stored in another page and when not. The limit for SQL Server is 4000 characters, for Oracle 2000 characters. Below this limit, the length of the string does not really matter.
PostgreSQL has a maximum 'unlimited' size of 1 GB, SQL Server 2^31-1 bytes and Oracle almost 4 GB.
Please note that Mendix uses unicode. Therefore in many databases we use the type nvarchar and ntext. To store 1 character, 2 bytes are used!