PS: A quick proposal after our talk in Amsterdam meetup @alexey-milovidov
Some of the technical details are beyond my expertise, so I had to ask help from GPT to write this proposal

Introduction

The utility of an AUTO_INCREMENT feature, which generates unique integer values for specific columns, is evident in numerous database systems. The addition of this capability to ClickHouse can enhance data management, facilitate optimized querying, and provide a consistent method for row identification.

Design Considerations

1. Implementation Model:

   • Given ClickHouse's distributed architecture, adopting a distributed unique ID generation system, akin to Twitter's Snowflake, is recommended. This ensures unique IDs across nodes, a crucial aspect for distributed databases like ClickHouse.

2. DataType:

   • The column with AUTO_INCREMENT should have the Int64 data type, which is ClickHouse's equivalent of BIGINT in other databases.

3. Uniqueness and Sequencing:

   • While AUTO_INCREMENT will ensure the uniqueness of values, it doesn't need to guarantee:
     • Consecutive or sequential ordering.
     • Starting from a specific value.
     • Distinction from manually set values.

4. Constraints:

   • Unique Identifier: While ClickHouse possesses the concept of a primary key, it's primarily for optimizing data retrieval rather than ensuring uniqueness. The AUTO_INCREMENT feature would emphasize providing a unique identifier for rows, rather than acting as a traditional primary key.
   • Each table can have only one AUTO_INCREMENT column.
   • The AUTO_INCREMENT column should be NOT NULL and should not have a default value.

5. Value Assignment:

   • When a new row is inserted without a value for the AUTO_INCREMENT column, ClickHouse should auto-assign a value.
   • Explicitly set values during INSERT or UPDATE should be accepted, but the system must ensure that future auto-generated values avoid collisions with these.

6. Management Across Nodes:

   • Given ClickHouse's distributed environment, each node might need to manage a specific range of AUTO_INCREMENT values, thus preventing collisions. This would require further technical examination to align with ClickHouse's architecture.

7. Alternative for Custom Sequencing:

   • If the default AUTO_INCREMENT behavior isn't suitable for specific use cases, we could consider offering users a custom sequencing mechanism.

Benefits of AUTO_INCREMENT in ClickHouse

1. Unique Row Identification: Provides a consistent method for uniquely identifying rows, improving data manageability.
2. Optimized Querying: The presence of a unique identifier can facilitate more efficient data retrieval operations.
3. Consistent Data Management: A standardized way to handle row identification can streamline operations and reduce complexities.

Challenges & Considerations

1. Managing Explicit Values: Ensuring no collisions when users manually set a value in the AUTO_INCREMENT column.
2. Distributed System Nuances: Given ClickHouse's distributed nature, managing AUTO_INCREMENT values across nodes is challenging and would require a tailored approach.

Conclusion

Incorporating the AUTO_INCREMENT feature into ClickHouse can bring about enhanced usability and performance for various data management scenarios. While there are specific nuances and challenges to address, a thoughtfully designed implementation can make this a valuable addition to ClickHouse's suite of features.