# Forge SQL - Auto Increment recommendation **URL:** \ **Category:** Forge\ **Tags:** sql, forge\ **Created:** [May 22, 2025, 1:56am UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233 "2025-05-22T01:56:47Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![PeterMcNeil2](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@PeterMcNeil2](https://community.developer.atlassian.com/u/PeterMcNeil2)\ **Post date:** [May 22, 2025, 1:56am UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233/1 "2025-05-22T01:56:47Z")
The doco for Forge SQL recommends: > Avoid using `AUTO INCREMENT` fields in your tables, as Forge SQL is based on a distributed SQL database and sequences are not guaranteed to be globally unique. The statement that “sequences are not guaranteed to be globally unique” is somewhat incorrect. Auto Increment will be unique, especially if you use a unique index, they will not be consecutively numbered and may well have gaps, you may even exhaust the pool of available numbers due to cache dumps when the nodes are restarted. Also Auto Increment values are not sequences. The Auto increment values are not globally monotonic (i.e. increase in value across all nodes) See [https://docs.pingcap.com/tidbcloud/auto-increment/#auto\_increment](https://docs.pingcap.com/tidbcloud/auto-increment/#auto_increment) for more info. **Question** So regarding that, what is that recommendation referring to? It seems to me Auto\_Increment is globally unique, can you point to where it is not? Follow up Question: If we should not use Auto Increment, what are the recommended alternatives for say the unique ID field? Cheers, Peter. ---
**Author:** ![PeterMcNeil2](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@PeterMcNeil2](https://community.developer.atlassian.com/u/PeterMcNeil2)\ **Post date:** [May 22, 2025, 2:08am UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233/2 "2025-05-22T02:08:08Z")
BTW I’m guessing the suggested alternative is using a sequence. ---
**Author:** ![AnatolyShuvalov](https://avatars.discourse-cdn.com/v4/letter/a/bc8723/32.png) [@AnatolyShuvalov](https://community.developer.atlassian.com/u/AnatolyShuvalov)\ **Post date:** [May 22, 2025, 5:22am UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233/3 "2025-05-22T05:22:39Z")
Hi Peter, thanks for raising this up - you are absolutely right, while using `AUTO_INCREMENT` in Forge SQL is not recommended, the actual reason behind it is not correctly articulated and lacks alternatives, we will address it. The actual reason to avoid `AUTO_INCREMENT` is that it can cause a hot spot issue in TiDB when used excessively on large datasets - [Troubleshoot Hotspot Issues](https://docs.pingcap.com/tidb/stable/troubleshoot-hot-spot-issues/) Two recommended alternatives are: - Use `AUTO_RANDOM` instead of `AUTO_INCREMENT` [Troubleshoot Hotspot Issues](https://docs.pingcap.com/tidb/stable/troubleshoot-hot-spot-issues/#handle-auto-increment-primary-key-hotspot-tables-using-auto_random) - Use UUIDs stored as `BINARY(16)` type [UUID Best Practices](https://docs.pingcap.com/tidb/stable/uuid/#overview-of-uuids) Note that it is not recommended to set the `swap_flag` with TiDB to avoid hotspots too. ---
**Author:** ![PeterMcNeil2](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@PeterMcNeil2](https://community.developer.atlassian.com/u/PeterMcNeil2)\ **Post date:** [May 23, 2025, 5:20am UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233/4 "2025-05-23T05:20:11Z")
Interesting I just changed an app to use AUTO\_RANDOM in place of AUTO\_INCREMENT and, well, it’s generated the exact same id numbers on the primary key id field! This only happens in forgeSQL when I run the same statements on TiDB it works as expected. Also the migrationRunner is failing to run all the migration steps because it times out, the next time it runs the rest of the migration script. ```auto cause: Error: Network request can not be made: Forge proxy authorization expired 1 seconds ago. It's likely caused by code from a previous function invocation still running. Please ensure that all promises in your code are properly awaited. at get token [as token] (/tmp/forge-dist-34596-OvOGdHOCLOBA/ __forge_wrapper__.cjs:2:1144351) at r.getProxyToken (/tmp/forge-dist-34596-OvOGdHOCLOBA/ __forge_wrapper__.cjs:2:892943) at /tmp/forge-dist-34596-OvOGdHOCLOBA/ __forge_wrapper__.cjs:2:1139686 at F (/tmp/forge-dist-34596-OvOGdHOCLOBA/ __forge_wrapper__.cjs:2:1140368) at N (/tmp/forge-dist-34596-OvOGdHOCLOBA/ __forge_wrapper__.cjs:2:1141583) at (webpack://jira-global-page-custom-ui/node_modules/node-fetch/lib/index.mjs:1472:1) at new Promise () at fetch (webpack://jira-global-page-custom-ui/node_modules/node-fetch/lib/index.mjs:1441:1) at (webpack://jira-global-page-custom-ui/node_modules/@forge/sql/node_modules/@forge/api/out/api/fetch.js:27:1) at SqlClient.sendRequest (webpack://jira-global-page-custom-ui/node_modules/@forge/sql/out/sql.js:14:1) } ``` ![Screenshot from 2025-05-23 15-10-27](https://global.discourse-cdn.com/atlassiandeveloper/original/3X/9/2/926bdd7b0fdbcc6e526c7ae45ca1e830b0d37269.png) ![Screenshot from 2025-05-23 15-11-12](https://global.discourse-cdn.com/atlassiandeveloper/original/3X/e/0/e036df9165b02bba3807adaa9be03cdb9aea79a6.png) Interestingly in TiDB they are unique, but not really that random! ![Screenshot from 2025-05-23 15-15-18](https://global.discourse-cdn.com/atlassiandeveloper/original/3X/d/f/df4b1b5c648b67229e8fdf1a3e1dc2b9ea47977d.png) The code for the table looks like this: ```auto create table entity_type ( id bigint primary key auto_random, entity_name varchar(100) null, ... ``` and this doco says that should work 🙂[https://docs.pingcap.com/tidbcloud/auto-random/](https://docs.pingcap.com/tidbcloud/auto-random/) I might just go back to a sequence or Auto\_Increment. ---
**Author:** ![AshleyBartlett](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/ashleybartlett/32/44895_2.png) [@AshleyBartlett](https://community.developer.atlassian.com/u/AshleyBartlett)\ **Post date:** [June 29, 2025, 11:20pm UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233/5 "2025-06-29T23:20:19Z")
The fact it’s not showing a random value is interesting, and I wonder what the schema definition of that column was at the time of testing? The `AUTO_RANDOM` column property has some similar implementation details to `AUTO_INCREMENT` for primary key values [https://docs.pingcap.com/tidbcloud/auto-random/#implicit-allocation-rules-of-ids](https://docs.pingcap.com/tidbcloud/auto-random/#implicit-allocation-rules-of-ids) > TiDB implicitly allocates values to AUTO\_RANDOM columns similarly to AUTO\_INCREMENT columns. Meaning, this behaviour is not necessarily unexpected, especially if altered from AUTO\_INCREMENT ---
**Author:** ![PeterMcNeil2](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@PeterMcNeil2](https://community.developer.atlassian.com/u/PeterMcNeil2)\ **Post date:** [June 30, 2025, 1:46am UTC](https://community.developer.atlassian.com/t/forge-sql-auto-increment-recommendation/92233/6 "2025-06-30T01:46:31Z")
I recreated all the tables using AUTO\_RANDOM in that case.