[SOLVED] String Storage Concerns

Heyo! I have a question about how to size my string lengths in Appwrite Cloud DB. And then a thought I had on a way to possibly approach this. This is mainly to better understand how to optimize my document attributes to minimize costs for storage and transfer when Appwrite is released.

My Understanding So Far

• String attributes are varchar internally and need to be sized
• MariaDB is used with InnoDB

Thus, storing any amount of data -- even a single character -- in a varchar(16000) attribute will consume the full 16kb of the storage allocated

So Does That Mean...

A NULL value will store only 2 bytes? Meaning if I have a nullable varchar(16000), it will either consume 2 bytes of storage or 16kb of storage?

Why I'm Asking
I have a column that is going to store a user's document content. The content can be freeform text...like this post that I'm making right now. It means that it is not only incredibly variable in length, but it could take up a significant amount of space.

Is This a Problem?
Assuming everything I've mentioned so far is accurate, is this an issue? Since there isn't much information on the pricing structure for Appwrite...is this going to be an issue? If the cost structure somehow works around the storage on disk or something, then please let me know and you can ignore my optimization idea.

What Do you Think of This Optimization?
What if I partitioned this field into multiple nullable fields?

Instead of: content varchar(16000) nullable

I have something like: content_one varchar(1000) nullable ;content_two varchar(2000) nullable; content_three varchar(4000) nullable; etc and combined these columns together when rendering the content.

In theory, if I have a lot of users and a majority of them are creating very little content but there are a few power users who are creating a lot of content, I could save on costs.

Thanks for reading! Open to any feedback.

TLDR: Seems like micro-optim. I would not be too worried.

String attributes are varchar internally and need to be sized
Correct, until some size. There are some breaking points when Appwrite switched to MEDIUMTEXT, TEXT? .. Refference: https://github.com/utopia-php/database/blob/main/src/Database/Adapter/MariaDB.php#L1070-L1082

varchar(16000) attribute will consume the full 16kb of the storage
This should not be the case. It would be case for char, but varchar is (somehow) doing it smarter. your 1 character will probably take like 4-5 characters, but still much less than maximum defined. Refference: https://stackoverflow.com/questions/1885630/whats-the-difference-between-varchar-and-char
Here is our math behind varchar length calculation: https://github.com/utopia-php/database/blob/main/src/Database/Adapter/SQL.php#L328-L330

A NULL value will store only 2 bytes?
Not exactly sure if 2 bytes, but yes, it would be really small piece of information

I have a column that is going to store a user's document content.
My recommendation would be to set size to at least 16384, to make sure TEXT type is used in SQL. Again, not exactly sure how it's handled, but I am sure MariaDB is smart about this.

Is This a Problem?
I dont think so. I have used this in multiple demo apps and didn't notice any issues.

If the cost structure somehow works around the storage
Regarding Appwrite Cloud, pricing for databases will most likely be around amount of documents. No promises yet, but that's the plan.

What if I partitioned this field into multiple nullable fields?
It would work, yes. I don't think it would make a huge difference. Tho please don't let my experience discourage you from gaining yours. I would love to read article from you proving that it indeed makes a difference and how much of performance/storage difference it makes.

Thank you! Very thoughtful response.

[SOLVED] String Storage Concerns

Ah darn well it looks like the UI doesn't let me set strings that large even though it looks like it's configurable internally.

Captured from the network tab:

Should be fine but just an observation.

can you try with a size bigger than 16383?

That works 😄