4GB limitation on these embedded/express DBs good enough? what's next if limitation is reached?

Question

I'm wondering how long a (theoretically) desktop-app can consume the full 4GB limitation of these express/embedded database products (SQL-Server Express, Oracle Express, SQLite3, etc) provided that big blobs will be stored in filesystem.

Also what would be your strategy when it hits the 4GB?

• Archive the old DB
• Copy 1-3 months of data to the new DB (consider this as cache strategy?)
• Start using the new DB from this point onward (How do you access the old data?)

I understand that the answer might varies depending on how much data you stored in the table/column. But please describe based on your experience (what kind of desktop-app, write/read heavy, how long will it reach according to your guess).

Answer 1

My strategy for "what happens when it hits 4GB" would usually be "install a full-fledged database".

4 GB is an awful lot of text; consider it nearly infinite for a single-developer application.

Assuming blobs to the filesystem, limit users' images to 100kb per image, one profile image per user, and no more than one quarter of your database to those images; 1 GB. That's still ten thousand users.

4GB is usually a lot, unless you're doing something unusual.

Answer 2

There is really no way to answer this question, other than to advise upgrading to a "real" database. That being said, if a single-user database is reaching 4GB without holding large blobs, then you're doing something out of the ordinary.

---

Edit

One thing that many people neglect to consider is the RAM and CPU restriction on the Express edition for SQL Server. While 2008 and prior have database size limits of 4GB (10GB for 2008 R2, as pointed out in the comments for this question), you're far more likely to be negatively impacted by the 1GB RAM and single CPU limitations, especially with data sets that large.

---

Archiving is (almost) never a simple solution, since that usually involves either breaking existing relationships or duplicating data. For example, consider I have a database of Customers and Orders.

                Order
Customer        ----------
---------       OrderID
CustomerID <--- CustomerID
...             ...

The natural choice here is to create a foreign hey between the two CustomerID columns, making it non-nullable in the Order table. But what happens when I want to archive the orders? Either I have to break the relationship in the archive database (allowing it to link to a CustomerID that doesn't exist in the archive database) or I have to duplicate the data (archive the linked Customer record, while still keeping it around in the live database). Neither option is particularly desirable from a maintenance perspective.

Answer 3

"what’s next if limitation is reached?"

this number : 18446744073709551615. And in case you didn't get it, that's 16 exabyte (minus one).

Answer 4

If the limit is reached then use a DBMS that doesn't have that limitation. Also, always remember that there is no such thing as free software.