Managing unique identifiers correctly is crucial for performance, data integrity, and scalability in relational applications. When building on top of PostgreSQL via Supabase, configuring auto-incrementing IDs correctly saves you from debugging painful race conditions or migration errors down the road.
Supabase relies on standard PostgreSQL mechanics for sequential row identification. Instead of relying on manual inputs or application-level UUID generation for every single table, you can configure PostgreSQL identity columns or serial types to handle unique sequencing automatically behind the scenes.
Understanding Primary Key Options in Supabase
When creating a table in the Supabase dashboard or via the SQL editor, you must decide how your primary keys will increment. PostgreSQL offers three primary approaches for numeric identifiers: SERIAL, BIGSERIAL, and the modern SQL standard IDENTITY columns.
The legacy SERIAL pseudo-type creates an integer column and automatically generates an associated sequence object. While functional, it has quirks regarding permission management and default sequence ownership. Modern PostgreSQL and Supabase workflows prefer GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY.
create table profiles (
id bigint generated by default as identity primary key,
username text not null,
created_at timestamptz default now()
);
Using GENERATED BY DEFAULT AS IDENTITY allows Supabase to automatically generate the sequence value on insert, but it also permits you to explicitly supply an ID if you're importing a data dump or migrating legacy rows. If you use GENERATED ALWAYS, PostgreSQL rejects explicit ID inserts unless you specify OVERRIDING SYSTEM VALUE.
Setting Up Auto-Increment Keys in the Supabase Dashboard
While raw SQL offers maximum control, you can also configure auto-incrementing primary keys directly through the Supabase table editor GUI.
- Navigate to your project dashboard and open the Table Editor.
- Click Create a new table and enter your table name.
- Add your first column, naming it
id. - Set the data type to
int8(for bigint) orint4(for standard integer). - Open the advanced settings or default value section and select identity generation, ensuring it acts as the primary key.
Choosing int8 (bigint) over int4 is essential for production applications. Standard 32-bit integers max out at roughly 2.14 billion rows, which high-traffic applications can exhaust surprisingly fast.
UUIDs vs. Auto-Incrementing Integers
A common debate in the Supabase ecosystem is whether to use sequential auto-incrementing integers or UUIDs (specifically UUIDv4 or UUIDv7) for primary keys. Supabase defaults many auth and storage tables to UUIDs for distributed safety, but integers still hold distinct advantages.
Sequential integers consume less storage space (8 bytes for bigint versus 16 bytes for UUID) and cluster index inserts more efficiently on disk because new rows append sequentially. However, sequential integers expose record counts and predictable patterns, making them a poor choice for public-facing resource URLs where enumeration attacks are a concern.
- Use auto-increment integers for: Internal application logs, high-frequency transactional data, and simple relational tables where URL predictability doesn't matter.
- Use UUIDs for: Distributed microservices, multi-tenant databases merging offline data, or public user-facing profile endpoints.
For more details on PostgreSQL data types, review the official PostgreSQL Documentation.
Fixing Common Auto-Increment Desynchronization Errors
One of the most frequent issues developers face when seeding data or migrating databases into Supabase is a sequence desynchronization error, which throws duplicate key constraint violations during new inserts.
This happens when you insert rows with explicit ID values (for example, inserting IDs 1 through 100), but the underlying PostgreSQL sequence counter remains stuck at 1. When Supabase attempts the next automatic insert, it tries to use ID 1, causing a collision.
You can fix this desynchronization instantly by altering the sequence manually using SQL:
select setval(pg_get_serial_sequence('profiles', 'id'), coalesce(max(id), 1)) from profiles;
This command queries the maximum existing ID in your table, handles empty table edge cases using coalesce, and advances the internal sequence pointer past your highest existing record.
Frequently Asked Questions
Can I switch an existing UUID primary key to an auto-increment integer?
Doing this safely requires creating a new column with the integer identity type, generating mapping logic, updating foreign key constraints across dependent tables, and dropping the old UUID column. It is significantly easier to decide on your primary key strategy before launching to production.
What happens to auto-increment IDs if a row is deleted?
Auto-increment sequences never reuse numbers when rows are deleted. If you delete row #5, the next inserted row receives #6. Gaps in sequential IDs are completely normal and harmless in relational databases.
How do I handle auto-increment keys with Supabase Row Level Security?
Primary key generation happens at the database engine level before Row Level Security (RLS) policies evaluate insert permissions. Ensure your table has appropriate RLS INSERT policies enabled so authenticated clients can trigger sequence generation successfully.