Mobile Local Storage, Databases & Realm Caching: 15 Core Questions

Designing mobile local databases requires understanding the trade-offs between relational tables and object graphs, managing multi-threaded write access, enabling fast query indexes, and secure storage encryption. This guide explores 15 storage questions.

Questions Quick Links

• Q1. SQLite vs. NoSQL Object Databases
• Q2. Realm thread isolation constraints
• Q3. Reactive queries & UI updates
• Q4. Manual vs Auto migrations
• Q5. Database Indexing trade-offs
• Q6. WAL (Write-Ahead Logging) benefits
• Q7. Object relationship mappings
• Q8. SQLCipher & Realm Encryptions
• Q9. SQLite batch transaction grouping
• Q10. Handling disk space boundaries
• Q11. SharedPreferences vs NSUserDefaults limits
• Q12. Raw SQL queries vs ORM engines
• Q13. Concurrent multi-thread write conflicts
• Q14. SQLite Full-Text Search (FTS5)
• Q15. Connection pool cleanup metrics

Q1. Compare SQLite, NoSQL Object Databases (Realm), and Key-Value stores.

Answer:

• SQLite: A relational SQL engine. Excellent for structured data with relationships requiring tables and queries.
• Realm (NoSQL): Object-oriented database. Saves data models directly as objects without mapping layers. Faster read speeds.
• Key-Value Stores: Used for lightweight user settings (preferences). Slow and unsuitable for large structured tables.

Q2. Explain Realm's thread isolation rules. How do you share objects across threads?

Answer:

Realm objects are live-update pointers tied to the thread they were query-loaded on. Sharing them across threads leads to thread-access crash errors. To share objects, pass their unique primary keys (or wrap them in **ThreadSafeReference** instances) and re-query them on the target thread.

Q3. How do reactive queries update UI screens dynamically?

Answer:

ORM engines (like Room/Realm) register listener callbacks on query tables. When a write transaction completes, it triggers table-change listeners, pushing updated collections through streams (e.g. Kotlin Flow, RxJava, Combine) to trigger UI redraws.

Q4. Compare Manual SQL Migrations to Auto-migrations.

Answer:

• Auto-migrations: The database engine automatically compares schemas and writes tables updates (only for basic column addition/removal).
• Manual Migrations: Required for complex structural shifts (e.g. merging tables, changing column types) using explicit raw SQL scripts to prevent data loss.

Q5. What is the database indexing trade-off?

Answer:

Indexes create sorted reference tables for specific columns, converting table-scan lookups (`O(N)`) into index-tree searches (`O(log N)`). However, indexes increase database file size and slow down write operations (insert/update/delete) because the index table must be rebuilt on every write transaction.

Q6. What is WAL (Write-Ahead Logging) mode in SQLite?

Answer:

By default, SQLite locks the entire database file during writes. In **WAL mode**, SQLite writes modifications to a separate log file (`-wal`) and reads data from both logs and database files. This allows concurrent read transactions to run during write operations, preventing UI freezes.

Q7. How do object relationships differ in SQLite vs. Realm?

Answer:

• SQLite: Uses relational key parameters (foreign keys, join tables) requiring query joins to link data.
• Realm: Uses direct reference link variables (e.g. mapping a list of objects directly inside a property), loading parent-child links instantly.

Q8. How do you encrypt local databases?

Answer:

Use SQLCipher (for SQLite engines) or pass a 64-byte key array to the Realm builder. The encryption key should be securely stored in hardware-backed storage (Keychain or KeyStore) and loaded dynamically.

Q9. How do you optimize batch record insertions in SQLite?

Answer:

Avoid executing insert loops individually, which causes SQLite to start and commit a transaction for each record. Wrap all inserts inside a single database transaction: `db.beginTransaction(); try { ... } finally { db.endTransaction(); }`. This speeds up execution times by up to 100x.

Q10. How do you handle storage limit boundaries?

Answer:

Check device storage availability before launching heavy downloads. Wrap write operations in try-catch statements, handling disk full exceptions by notifying users or purging cached temporary files.

Q11. What are the limits of SharedPreferences and NSUserDefaults?

Answer:

Both parse and cache the entire XML/plist file in system memory on startup. Storing large data tables or images in preferences blocks threads, causing UI lag and memory issues.

Q12. What is the difference between raw SQLite queries and ORM engines?

Answer:

• Raw SQLite: Gives you total control over execution. Requires parsing cursor variables manually and writing SQL syntax.
• ORMs: Abstract SQL query details. Speed up development, but can result in inefficient query configurations if not monitored.

Q13. How do databases handle concurrent multi-threaded write conflicts?

Answer:

Relational databases throw database locked exceptions if threads try to write concurrently. Enforce database access through a single synchronized database helper class, queueing transactions sequentially.

Q14. How does Full-Text Search (FTS5) work in SQLite?

Answer:

SQLite provides the **FTS5** extension module to create virtual indexing tables: `CREATE VIRTUAL TABLE my_fts USING fts5(title, body)`. This enables fast keyword matches (`MATCH` operator) across thousands of text records in milliseconds.

Q15. Why should database connections be closed when the app context is destroyed?

Answer:

Leaving database instances open when context frames destroy can leak file descriptor handles. Close connections inside activity/service lifecycle cleanup callbacks to return memory handles to the OS.