8 good optimization tricks for Firestore and SQLite

Optimization – who is this post for?

Firestore, SQLite, what is it?

First a brief intro. In my mobile games I am using SQLite to store in a simple, relational database the data needed to run my app. This includes, user accounts, game state / history, transactions and other things I need to save for the app to work properly.
Firestore on the other hand, is the NoSQL data storage technology from Google Firebase (which is a set of cloud services). Firestore is similar to how MongoDB or other NoSQL databases work and is perfect for non-structured data.

Why this choice? First, I basically grew up using SQL so saving data in a relational way feels natural to me. Second, Firestore because of simplicity to read/write data and integrate it with different programming languages (in my case, Flutter).

What to use when

SQLite – use it only for historical, configuration and finalized data. For example, imagine having a short game in progress. It is usually a bad idea to keep writing to the local SQL database every single event and action that has happened. It is much cleaner to save the final state of the game once the game finishes (unless the game takes very long and saving a state is important). Still, there are other ways, but using the SQL database to save things too early when stuff keeps changing (getting deleted, modified, etc.) can give you a headache, especially when fighting with relations and foreign key constraints. My advice – don’t use SQLite as your primary source of truth.

Firestore – use it as your source of truth. Especially, if you have an online game or sharing the state between devices. I found this work particularly well with Firebase functions (for updating/inserting data to Firestore) and with Riverpod (a library for Flutter to synchronize the state from the Firestore). If you use both SQLite and Firestore, then as said above, wait with saving to SQL until the game / state is finished, otherwise you will encounter a lot of foreign key constraints.

Memory – of course, you have the current state of your application (as long as it is running). The issue starts when you want to persist the ongoing state somewhere, especially in case when the user kills the app for whatever reason and reopens it. Then the state is gone

Golden solution?

• Memory – for ongoing states of your application
• SQLite – for historical data and configurations (free)
• Firestore – for cloud backup, online capabilities (bears cost)
• SQLite (again) – for snapshots of your states, but when loaded in the memory, treat it as a new set of data. Do not rely later on updates/deletes and so on. Once ready to finalize the session, simply overwrite the tables with new data instead of doing tedious updates and deletes (although the old state should be deleted if possible)

Cost of Firestore

The current prices as of 2026-03-05:

Operation	Price
Reads	$0.06 per 100,000 reads
Writes	$0.18 per 100,000 writes
Deletes	$0.02 per 100,000 deletes

But before you start paying the above amounts, there is a generous free tier (as of 2026-03-05):

Operation	Free limit
Reads	50,000 / day
Writes	20,000 / day
Deletes	20,000 / day
Storage	1 GB
Network engres	10 GB / month

And if we look at some real-life examples:

Type of app	Reads per month	Writes per month	Estimated cost
Small	500,000	100,000	~$0.48
Medium	5,000,000	1,000,000	~$4.80
Large	50,000,000	10,000,000	~$48.00

There are some traps involved to these pricings that I learned quite soon. So to ensure proper optimization of your setup, watch out for:

• Reads do not relate to a query being sent to Firestore, 1 read = 1 document. Which means, if your query return 10 documents = 10 reads. It is very important to try to prepare your architecture in such a way to reduce the number of documents you need to return.
• Snapshot listening (useful for online games that require immediate reaction in the app in case something changes) costs: 1 read to setup the listener, then 1 read for every document change. So here it’s especially important to not listen to an entire large collection with plenty of sub-collections and everything, but keep the data as isolated as possible so that you only listen to small changes.

The cloud costs tend to be very low in the beginning (which is great for development and experimentation), but once the app scales, the costs can kill you.

Firestore optimization techniques

This is critical to perform optimization at the very beginning because it’s very tempting to just ship an app that’s working great and has all the online capabilities. In the beginning the costs will be so low, you won’t care too much. But as soon as you reach 1 mln users, then suddenly you might realize the costs of infrastructure outweigh the revenue. Therefore, optimize your online infrastructure in the very beginning. Some tips here:

1. Only update what is really needed, do not store your entire data model in online databases – if most of the logic sits in the app, the online database should only contain information about the state or other small chunks of data that are relevant to be shared. Don’t go pouring your entire SQL structure into Firestore.
2. Use data denormalization to reduce reads – when you have a list of players for your game stored in Firestore and then you have some game state document, do not just store the players again in some sub-collection, store only their ID references or even just their names if that’s what you need in the end. Reading a document with an array is much better than reading document with sub-collections (which require additional reads by the way, Firestore will not return you the entire structure).
3. Be cautious with listeners – they are very handy and nice, but they increase the cost heavily. Every little change triggers a read to all the listening devices. So make sure that you are listening to small documents that contain only the most relevant information for the devices. For example, if you have some collection of GameRounds, don’t listen to the entire GameRounds collection, just go for the currentRound only.
4. Reduce the number of “refresh traps” – it can be tempting to give the user an option to refresh the state from the online database. But imagine if some more creative type of users starts spamming this button every day for whatever reason (maybe after reading this article, who knows?). Every refresh costs at least one read, so if you allow this – either limit how often the refresh can be done, or allow the refresh only in particular situations (like the UI seems to be stuck).
5. Don’t turn a phone into a backend – if you need to prepare some complex updates and inserts in Firestore, it shouldn’t be the device that is calling Firestore all the time with updates and inserts. First of all, this is unreliable and unstable and second of all it depends on the device availability and connectivity (loss of data?) and third of all you need to ensure proper security to make sure the device can update only the right data. The best way is to leave the heavy lifting to backend, which can be for example Firebase Functions. In my example of GameRoom app, when players vote for result, they are actually calling a Firebase Function to add the vote. Then the function checks if all votes were cast, calculates the average and then updates the necessary documents and the result document which is being watched by the devices. That way we only invoke the function once and then read one document instead of updating a lot of places.
6. Set up a data cleaner – if your online database contains documents that get expired or archived, make sure to have some Firebase function running that will delete them after some time. This reduces the space limitation given by Firebase. In my case, I am deleting finished Game Rooms after 24h.
7. Set up database rules for unwanted updates – it’s actually quite important to prevent random users from being able to update things they shouldn’t be updating (on purpose, or randomly). In my case, I am checking if the calling user ID matches the user ID of document he is trying to update. Everything else – I deny, I let Firebase functions handle the rest. Example rule definition:

rules_version = '2';
service cloud.firestore {
  match /databases/{database}/documents {

    // Game rooms
    match /gameRooms/{roomId} {
      allow read: if request.auth != null;
      allow update: if request.auth != null
        && request.auth.uid == resource.data.hostFirebaseAuthId;
    }
    
    // Deny everything else
    match /{document=**} {
      allow read, write: if false;
    }
  }
}

8. Don’t turn your NoSQL into SQL database – that’s a more generic advice, not related to Firestore in particular. In general, if you recreate a relational database in NoSQL database – this means you are not using your NoSQL database properly. As stated in previous points, keep in Firestore only what you need to keep, make use of data denormalization to store only summaries, avoid using too many nested documents if possible to optimize number of reads done.

SQLite optimization techniques

Let’s not forget SQLite. Even though it’s a local database so working with it will be much faster, still it is worth noting some tricks.

1. Enable WAL mode for concurrent reads and adapt cache size – Switch the journal to Write-Ahead Logging to improve write throughput and allow readers during writes, which is ideal for mobile and desktop apps with mixed workloads. In addition, increase the page cache size to keep hot data in memory and reduce disk reads, and consider adjusting page size if you control database creation and typical row size. Example:

return openDatabase(
  path,
  version: 1,
  onConfigure: (db) async {
    await db.execute('PRAGMA foreign_keys = ON');
    await db.execute('PRAGMA journal_mode = WAL');
    await db.execute('PRAGMA cache_size = -4000');
  },
);

2. Use explicit transactions for multiple writes – Wrap multiple INSERT/UPDATE/DELETE operations in a single transaction to avoid per-statement sync overhead and get 2–20x higher write throughput. My example implementation in Flutter:

    try {
      final db = await SqlDatabaseHelper.instance.database;
      // sqflite automatically rolls back the transaction if an exception is thrown
      await db.transaction((txn) async {
        await _gameRoomRepository.upsertGameRoom(gameRoomToSave, db: txn);
        await _playerGameRoomRepository.upsertPlayers(
          gameRoomToSave.players!,
          db: txn,
        );
        // and so on with other db actions using the txn
      }
    } catch (e, st) {
      LogService.instance.logException(
        error: e.toString(),
        stack: st,
        context:
            'GameSessionOnlineDbSaver.saveCompleteGameRoomToDatabase could not save the entire session to local database. '
            'The transaction was rolled back automatically.',
      );
      // Rethrow so the caller can handle the failure (e.g. show an error to the user)
      rethrow;
    }

And from the perspective of the repository class, use batch commits for bulk inserts:

await db.transaction((txn) async {
  final batch = txn.batch();
  for (final item in items) {
    batch.insert('todos', item.toMap(),
        conflictAlgorithm: ConflictAlgorithm.replace);
  }
  await batch.commit(noResult: true); // faster for bulk inserts
});

3. Prepare your DB creation and migration script – It’s easy to design a database from scratch where everything is clean and beautiful. But problems start when you are in production and suddenly decided to modify your DB structure. First of all, it’s a good practice to keep your DB creation scripts versioned so that you know exactly in which version you had which state of the database. So for example, in my project I have added assets like this:
   • assets/sql
     • migrations
       • v1_to_v2.sql
       • v2_to_v3.sql
     • definition
       • create_db_v1.sql
       • create_db_v2.sql
       • create_db_v3.sql
     • demo_data
       • demo_data_v1.sql
       • demo_data_v2.sql
       • demo_data_v3.sql

Then the migration script would be triggered in the openDatabase code:

openDatabase(
  path,
  version: 3,
  onCreate: _onCreate,
  onUpgrade: _onUpgrade
);

Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    LogService.instance.info('Upgrading DB: $oldVersion → $newVersion');
    try {
      await db.transaction((txn) async {
        // If you had earlier migrations, keep them here as guards:
        if (oldVersion < 2) {
          await _runSqlAsset(txn, 'assets/sql/migrations/v1_to_v2.sql');
        }
        if (oldVersion < 3) {
          await _runSqlAsset(txn, 'assets/sql/migrations/v2_to_v3.sql');
        }
      });
    } catch (e, st) {
      LogService.instance.logException(
        error: e.toString(),
        stack: st,
        context: '_onUpgrade',
      );
      rethrow; // Surface the error so you notice during testing
    }
  }

4. Centralize CRUD operations – instead of rewriting in every single repository class the same create, read, update and delete operations, just isolate these actions to one central class. That way it will be easier to detect errors. Each repository class will have a reference to that central class. Example:

class SqlCrudOperations {
  // Singleton
  static final SqlCrudOperations _instance = SqlCrudOperations._internal();
  factory SqlCrudOperations() => _instance;
  SqlCrudOperations._internal();

  Future<int> createRecord({
    required DatabaseExecutor db,
    required String table,
    required Map<String, dynamic> objectInSqlFormat,
  }) async {
    try {
      int result = await db.insert(
        table,
        objectInSqlFormat,
        conflictAlgorithm: ConflictAlgorithm.rollback,
      );

      LogService.instance.info(
        "SqlCrudOperations: createRecord: $result record(s) inserted into $table.",
      );
      return result;
    } catch (e, st) {
      LogService.instance.logException(
        error: e.toString(),
        stack: st,
        context: 'createRecord for table $table',
      );
      return -1; // Indicate failure
    }
  }

  Future<List<Map<String, Object?>>> getAllRecords({
    required DatabaseExecutor db,
    required String table,
  }) async {
    try {
      final result = await db.query(table);

      if (result.isNotEmpty) {
        return result;
      } else {
        return List.empty();
      }
    } catch (e, st) {
      LogService.instance.logException(
        error: e.toString(),
        stack: st,
        context: 'getAllRecords for table $table',
      );
      return List.empty(); // Indicate failure
    }
  }
  // And more, following the same pattern
}

From that point, a repository class will have a simple:

class SqlGameSessionResultRepository
    implements AbstractGameSessionResultRepository {
  final SqlCrudOperations _sqlCrudOperations = ServiceLocator()
      .getService<SqlCrudOperations>();

  final String tableName = 'game_session_result';
  @override
  Future<void> createGameSessionResult(
    GameSessionResult gameSessionResult, {
    DatabaseExecutor? db,
  }) async {
    final executor = db ?? await SqlDatabaseHelper.instance.database;
    await _sqlCrudOperations.createRecord(
      db: executor,
      table: tableName,
      objectInSqlFormat: gameSessionResult.toSql(),
    );
  }
}

5. Avoid running DB commands from build() – it’s generally a bad practice to instantiate your DB or make queries from the build() method. This should be done in the initState or via the Riverpod listeners (if online sync is needed).
6. Index the columns your Flutter screens actually query – When a list or search screen in Flutter feels slow, it’s often because SQLite is scanning the whole table instead of using an index. Look at the WHERE, ORDER BY and JOIN columns you use in db.query or rawQuery, and create simple or composite indexes for them in your migration scripts. Example:

CREATE TABLE user_parameter (
  id TEXT PRIMARY KEY,
  user_account_id TEXT NOT NULL,
  parameter_name TEXT NOT NULL,
  parameter_value TEXT,
  last_updated_on INTEGER NOT NULL,
  FOREIGN KEY (user_account_id) REFERENCES user_account(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX idx_user_parameter_user ON user_parameter(user_account_id);
CREATE UNIQUE INDEX uq_user_parameter_user_key ON user_parameter(user_account_id, parameter_name);

7. Avoid saving to DB all the time – it’s tempting to keep your local DB always up to date, but sometimes excessive DB updates can harm the performance. Make use of the memory and save progress in a cumulative way, unless you really have the need to keep every single action saved in the DB. In my Event Calculator app, the state of a transaction is saved only after the whole thing is confirmed and not during editing every single field.
8. Keep one DB instance and let it live – Opening and closing the SQLite database all the time is expensive, especially in Flutter where every call crosses the platform channel. I keep a single openDatabase call (for example in a singleton SqlDatabaseHelper or a repository layer), reuse that instance across the whole app, and let the OS close it when the app is killed instead of manually opening/closing on every screen.
   
   In practice that means: you don’t open the DB in every widget, you do it once on startup (or lazily the first time it’s needed), inject it into your repositories, and only expose async methods (Future/Stream) to the UI so that all queries and writes are done off the build method and can be awaited or watched safely. This small change removes a lot of hidden latency and weird “database is locked” errors you can get when multiple parts of the app try to open their own connection at the same time.

Extra tip based on lessons learned

Set up a good logging mechanism and Crashlytics (e.g. in Firebase) – This might be obvious, but I cannot stress this enough. You will see how difficult it is sometimes to troubleshoot errors, especially in multi-threaded apps with SQLite, Firestore, etc.

In the above code examples you may see a LogService that I implemented just for this reason. On top of that, all the crashes and exceptions I try to save in the Firebase Crashlytics to know exactly what went wrong where. The same logging mechanism I use in my typescript functions for Firebase Functions.

Thank you for reading my post, let’s stay in touch!