Your Database Choice Starts With the Primary Key

When starting a new application, one of the first decisions developers make is the database, and the database primary key quietly shapes that choice. The question usually sounds like:

MySQL or PostgreSQL?

Most comparisons focus on SQL features, performance, extensions, or scalability. But there is another question that deserves attention:

How will your database organize and access your data?

One of the simplest ways to understand this is to start with the primary key.

MySQL: The Database Primary Key Shapes the Data Layout

With MySQL's InnoDB storage engine, the primary key is also the clustered index.

This means table data is organized around the primary key.

For example:

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    created_at DATETIME
);

With sequential IDs:

10001
10002
10003
10004
10005

new rows naturally move toward the end of the index.

This works well for workloads with frequent inserts and queries such as:

SELECT *
FROM orders
WHERE id BETWEEN 10000 AND 11000;

The database can efficiently navigate the B-tree and scan the required range.

However, the choice of primary key matters.

Using random UUIDs as the clustered primary key can produce a very different access pattern because new rows may be inserted into different parts of the index.

For large, write-heavy tables, this can affect page locality, page splits, and overall index efficiency.

PostgreSQL: Index and Table Storage Are Different

PostgreSQL takes a different approach.

A primary key creates a unique B-tree index, but PostgreSQL does not automatically organize the table physically according to that index.

Conceptually:

Primary Key Index
       ↓
   row location
       ↓
    Table Heap

The index and table storage are separate structures.

This doesn't mean PostgreSQL cannot perform efficient range queries.

Queries such as:

WHERE id > ?
WHERE id BETWEEN ? AND ?
ORDER BY id

can still use a B-tree index efficiently.

The important difference is how the database gets from the index entry to the actual row.

A Database Primary Key Also Affects Pagination

Consider a large table with millions of rows.

A common pagination query is:

SELECT *
FROM orders
ORDER BY id
LIMIT 20 OFFSET 500000;

As the offset grows, the database may need to process many rows before returning the requested page.

A different approach is keyset pagination:

SELECT *
FROM orders
WHERE id > 500000
ORDER BY id
LIMIT 20;

Now the database can use the index to start from a known position.

If the application displays the newest records first, you might instead design an index around:

(created_at, id)

and use both columns as a stable cursor.

This is why pagination should not be designed independently from database indexing.

Don't Start With "Which Database Is Faster?"

Instead, ask:

How does my application access its data?

Consider these questions:

• Are inserts mostly sequential or distributed?
• Will the application perform large range queries?
• Will users sort by created_at, id, or another column?
• Will tables contain millions or billions of rows?
• Will pagination use OFFSET or cursor/keyset pagination?
• Which columns are commonly filtered and sorted together?

These questions lead naturally to your primary-key and index design.

And that design can influence which database architecture fits your application.

The Bigger Lesson

MySQL and PostgreSQL are not simply two interchangeable choices with different SQL syntax.

They have different storage architectures and indexing behavior.

You don't necessarily need to decide “MySQL vs PostgreSQL” first.

A better process is:

Application workload
        ↓
Query patterns
        ↓
Ordering & filtering
        ↓
Index design
        ↓
Primary key strategy
        ↓
Database choice

Understanding this relationship early can help you avoid expensive redesigns later.

Don't choose a database only by asking which one is faster. Choose based on how your application needs to store and retrieve data.

Further reading: for more on database primary key, see the primary key overview and the PostgreSQL documentation. Related on this site: Integer ID vs UUID primary keys and OFFSET vs last ID pagination.