feature request: database index for user.identities

[wangsijie]

What problem did you meet?

On submit a social sign in or register interaction, Logto core uses findUserByIdentity to find a user with a social identity.

However, there is no index for the jsonb column user.identities, this could cause poor query performance if the user table has a large size.

Describe what you'd like Logto to have

Indexes for this column for better query performace.

Resources

• findUserByIdentity: https://github.com/logto-io/logto/blob/master/packages/core/src/queries/user.ts#L44-L51
• Example data: { "google": { "userId": "xxxx" } }
• GIN indexes: https://www.postgresql.org/docs/12/gin-intro.html

[anurag-rai]

Hi @wangsijie, I could add the index for this column. How do I create a new alteration file, and how can I test the migration locally (specifically the down migration)?

[wangsijie]

1. Create a new alteration file

Follow the existing files as templates: https://github.com/logto-io/logto/tree/master/packages/schemas/alterations

Create a file manually with the format next-${timestamp}-${name}.ts

2. Local test

First, init the database in the old schemas(in master branch). Then switch to the changed branch, build the schema package, then apply alteractions by running pnpm cli db alter deploy next, you can refer to this doc. And now you can take a look at the database and check if the new index is applied.

If everything goes fine, revert the database by runing pnpm cli db alt r v$(echo ${{ steps.version.outputs.current }} | cut -d@ -f3) and then check it again.

[iamujjwalrawat]

kindly assign it to me im interested

[wangsijie]

@iamujjwalrawat done.

[charIeszhao]

@iamujjwalrawat Hi, any updates there? Don't worry, I asked because the Hacktoberfest event is over, but rest assured you can still working on it and help improve Logto to become a better product. Let us know if you hit any blocker and we'll be willing to assist you. Thanks