How to backfill GSI for new entity index?

[pablote]

Imagine this situation:

1. You create a table with GSI 1 already there.
2. Add a new entity with only local pk and sk
3. Rows are inserted for the new entity
4. Come up with new access pattern and index for said entity, map it GSI 1

Only newly inserted rows will be present in the index, see the following screenshot:

I imagine I can write a script that hits DynamoDB directly and fills all the missing gsi1pk and gsi1sk, but is there a way to do this from electrodb itself? I'd much rather that'd be case.

It doesn't have to be something automatic at startup, 'cause I imagine there'd be a cost to this. But if I know I'm in the situation, it'd be great to do something like: MyEntity.byMyCondition.backfill().

[Mikbol]

I'm also looking for a better way to handle this.
This must be a common scenario during development?

Is there a better solution around than having a one-time job that fetches all entities and then updates/put them immediately?

// without handling errors, batching, unprocessed entities, etc.
const allMyEntities = // get all entities by looping queries, scan, etc.
await MyEntity.put(allMyEntities).go({ pages: "all" })

[NicoPowers]

I think having a separate repo that could resemble electrodb migration tools or some kind of cli designed to work with electrodb would good. This would need to take the different in schema versions (by checking the entity version) and then generate the commands needed to backfill version 1 with 2, etc.

[tywalch]

@iOSonntag has done a lot of work on this project, and it's worth checking out: https://github.com/iOSonntag/electrodb-migrations

[zirkelc]

It would be nice to have a built-in function which you can call on entity-level. Here's a generic but ineffecient workaround:

type BackfillIndexOptions = {
  tableName?: string;
  region?: string;
};
export const backfillIndex = async <E extends Entity<any, any, any, any>>(
  entity: E,
  options: BackfillIndexOptions = {},
): Promise<void> => {
  if (options.tableName) {
    entity.setTableName(options.tableName);
  }

  if (options.region) {
    entity.setClient(new DynamoDBClient({ region: options.region }));
  }

  const items = await entity.scan.go({ pages: 'all' });

  for (const item of items.data) {
    const result = await entity
      .put(item)
      .where(({ updatedAt }, { eq }) => eq(updatedAt, item.updatedAt))
      .go();

    console.log('Updated item', result);
  }
}

[tywalch]

Migrations end up being very specific to the details of the project, and often have unique constraints/considerations (e.g. additional operations/calls per item, rate considerations, concurrency optimizations, custom logic to handle mid-migration failure, etc).

@zirkelc your example may work best for smaller tables -- scan with pages: 'all' will pull all items into memory before writing. For larger tables, it might be better to perform a put on each individual page from the scan.

How you model your entity also has a huge impact on how you can performantly migrate the data. If I'm migrating a "users" table, I might first get "organizations" first which then allows me to get all users for each organization in parallel.

A lot of ways to skin the cat here, which is why there isn't a built in tool 😅

[anatolzak]

Backfilling indexes is no longer necessary with the new DynamoDB multi-attribute composite GSIs

https://electrodb.dev/en/modeling/indexes/#multi-attribute-indexes

[anatolzak]

For small tables, a straightforward script that scans all items and applies updates is usually simple enough.

For large tables, the situation becomes more involved. You need to account for throttling, concurrency control, table-level and account-level throughput limits, and distributed rate limiting.

For those cases, I use AWS’s DynamoDB Bulk Executor, which runs on AWS Glue and enables highly parallelized operations across large datasets. It supports large-scale updates and deletions.

For update operations, a small amount of Python logic is still required, but the setup is relatively lightweight.

It has saved me a lot of time and effort when working with large tables.

[iOSonntag]

I'm working on a tool for exactly that — though it's still an early draft.

https://github.com/iOSonntag/electrodb-migrations

Caution

Heads up: don't use the code as-is. It's a prototype, not production-ready.

My goal is to make this fully robust and properly hand-crafted, but before I invest the time, I'd appreciate feedback on the overall design. I've put considerable thought into the details and I'm about 90% happy with the current direction.

The current design involves downtime, but zero-downtime migrations should only be a small v2 update away.