How bigint identity columns and UUIDs compare as Postgres primary keys: storage, index locality, insert performance, exposing IDs in URLs, generating IDs in the application, and why UUIDv7 changed the trade-off.

Every new Postgres table needs a primary key type, and the default usually depends on who wrote the last schema: bigint identity columns for people who worry about index size, UUIDs for people who worry about URLs.
Both are defensible, and the arrival of time-ordered UUIDv7 has removed the main performance argument against UUIDs.
A bigint is 8 bytes and a uuid is 16, and the difference repeats in every index containing the key and every foreign key that references it. On a table with a few foreign keys and secondary indexes, that is a measurable but rarely decisive amount of extra storage.
Locality matters more than size, since a sequence always appends to the right edge of the B-tree, so recent pages stay in memory. A random UUIDv4 sends each insert to an arbitrary leaf page, which on a large table means reading cold pages, more page splits, and more full-page images in the WAL after each checkpoint. UUIDv7, defined in RFC 9562, puts a 48-bit millisecond timestamp in the most significant bits, so new keys sort after old ones and inserts behave much like a sequence again.
Sequential IDs in URLs reveal how many orders or users you have and invite enumeration: /invoices/1041 suggests /invoices/1040 exists. Authorization should stop the access, but the leak of business volume remains. UUIDs remove that, with one caveat for v7: the timestamp in the first 48 bits tells anyone holding the ID when the row was created, which is harmless for orders and sometimes not for user accounts.
A UUID can be generated before the row exists, which is useful in three common cases:
A bigint from a sequence needs a round trip to the database first, and sequences are not shared across databases.
Some schemas use both: a bigint identity as the internal primary key that foreign keys reference, and a unique uuid column as the public ID in URLs and APIs. It keeps joins compact at the cost of an extra unique index and a lookup from public to internal ID on every request. With UUIDv7 available, a single UUID key is simpler for most new tables.
-- bigint identity, preferred over serial for new tables
CREATE TABLE invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
total_cents bigint NOT NULL
);
-- UUIDv7 generated by Postgres 18 or later
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
total_cents bigint NOT NULL
);
The identity column docs explain why GENERATED ALWAYS AS IDENTITY replaced serial: the sequence is owned by the column and permissions follow the table. On versions before 18, generate UUIDv7 in the application rather than relying on an extension.
Best fit: bigint identity for internal, high-volume tables written by one database; UUIDv7 for anything exposed, generated by clients or written from several places.
Encore is a backend framework with built-in infrastructure, and a service's database and migrations live in the same package as the endpoints that write to it. Generating the key in Go keeps the behavior identical on every Postgres version, from the local database encore run starts to RDS or Cloud SQL in your account, and lets the client supply the ID for idempotent retries:
Run in your terminal to get started locally.
package orders
import (
"context"
"encore.dev/beta/errs"
"encore.dev/storage/sqldb"
"github.com/google/uuid"
)
// migrations/1_orders.up.sql:
// CREATE TABLE orders (id uuid PRIMARY KEY, customer_id uuid NOT NULL, total_cents bigint NOT NULL);
var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"})
type CreateParams struct {
// Optional client-generated ID; retries with the same ID return the same order.
ID string `json:"id,omitempty"`
CustomerID string `json:"customer_id"`
TotalCents int64 `json:"total_cents"`
}
type Order struct {
ID string `json:"id"`
}
//encore:api public method=POST path=/orders
func Create(ctx context.Context, p *CreateParams) (*Order, error) {
var id uuid.UUID
if p.ID != "" {
parsed, err := uuid.Parse(p.ID)
if err != nil {
return nil, &errs.Error{Code: errs.InvalidArgument, Message: "id must be a UUID"}
}
id = parsed
} else {
v7, err := uuid.NewV7()
if err != nil {
return nil, err
}
id = v7
}
_, err := db.Exec(ctx, `
INSERT INTO orders (id, customer_id, total_cents)
VALUES ($1, $2, $3)
ON CONFLICT (id) DO NOTHING
`, id, p.CustomerID, p.TotalCents)
if err != nil {
return nil, err
}
return &Order{ID: id.String()}, nil
}
The databases docs cover migrations and queries, the API errors docs list the error codes, and a test can call Create twice with the same ID against the local database to confirm the retry is a no-op. In production, Encore provisions Postgres 18 on RDS by default according to the AWS infrastructure docs, so uuidv7() is also available server-side there if you prefer a column default.
An AI coding agent adding a new table follows whatever key convention the existing migrations use, so settling on UUIDv7 in the first migration keeps every later table consistent without anyone having to restate the rule.
Best fit: services with public or client-generated IDs that want one key type across every environment.
Pick bigint identity for internal tables with heavy insert rates and no external exposure. Pick UUIDv7 for tables whose IDs appear in URLs, come from clients or are written by several services, and avoid random UUIDv4 as a primary key on large, write-heavy tables. Whatever you choose, use the same convention across the schema.
Use bigint identity columns when IDs stay internal and one database generates them, since they are smaller and insert in order. Use UUIDv7 when IDs are exposed in URLs, generated by clients or several services, or merged across databases, because they are unguessable enough and still insert in roughly time order.
No. Random UUIDv4 keys can be slower on large tables because each insert lands on a random B-tree page, which causes more page splits, more WAL and a larger working set. Time-ordered UUIDv7 keys insert near the end of the index like a sequence, which removes most of that cost.
Yes. Postgres 18 added a built-in uuidv7() function, and gen_random_uuid() for version 4 has been built in since Postgres 13. On older versions, generate UUIDv7 in the application, for example with uuid.NewV7 from the google/uuid Go package.
Declare an id uuid PRIMARY KEY column in the migration and generate the value with uuid.NewV7 in the endpoint before inserting. Generating it in Go works the same on the local Postgres from encore run and on RDS or Cloud SQL.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.