Automatic DB indexes when Postgresql used as DB backend

sarsonj · August 31, 2021, 12:01pm

Hi,

we are using Postgresql as DB backend. When I reviewed our DB performance, I realised, that there are almost no DB indexes. Even when relations were defined in Parse (for example relation to _User), the index was not created.

The only indexes that are created are on internal tables - like in _User itself.

What is the best practice to handle indexes in Parse? Do I have to create them manually using SQL? At least indexes in relations could be create automatically…

Thanks,

Jindrich

davimacedo · August 31, 2021, 4:28pm

Yes. Currently you need to create them manually (or use some tool for that). But I agree with you. It would be awesome to have auto-generated indexes in the project.

cbaker6 · August 31, 2021, 10:52pm

You can do this after initializing your your Parse Server by accessing your adapter. This example works on both Postgres and Mongo:

github.com/parse-community/parse-server

Allow to create unique indexes

opened 07:07PM - 21 Mar 21 UTC

sadortun

type:feature bounty:$100

### New Feature / Enhancement Checklist  - [x] I am not disclosing a [vulnerability](https://github.com/parse-community/parse-server/blob/master/SECURITY.md). - [x] I am not just asking a [question](https://github.com/parse-community/.github/blob/master/SUPPORT.md). - [x] I have searched through [existing issues](https://github.com/parse-community/parse-server/issues?q=is%3Aissue). ### Current Limitation `parse-server` is currently lacking a way to update DB schema. #7091 should address most of this issue, but unfortunatly, it does not seems like there is a way to add `uniqueIndex` despite being able to do so with the `mongo client` and `postgre` ```js db.collection.createIndex( <key and index type specification>, { unique: true } ) ``` ### Feature / Enhancement Description Add a `isUniqueIndex` parameter ```js SchemaController.createIndex(className, index , isUniqueIndex) {} ``` Or even better, add an `options` parameter. The downside of this would be that itwould probably cause problems in inter-compatibility between mongo and postgre ```js createIndex(className, index , options) ``` ### Example Use Case ```js schema.createIndex("_User" , {username:1} , true) // or schema.createIndex("_User" , {username:1} , {unique: true} ) ``` ### Alternatives / Workarounds We need to bypass the Parse Schema logic and implement it with manual migrations ... its a real pain ! ### 3rd Party References