Study

We took 6,008 database schemas from 6,006 public repositories (SQL dumps, Prisma, Rails, Drizzle, DBML, pgModeler) and ran each one through mcdview's linter. This is what real schemas look like, as opposed to what best-practice posts say they should look like.

Updated on September 27, 2026. The first version of this study, published earlier the same day, used mcdview 0.34.2. Its linter missed index declarations in Rails and Drizzle schemas, UNIQUE and KEY clauses written inside CREATE TABLE, and the primary keys that phpMyAdmin exports add with ALTER TABLE. mcdview 0.34.3 reads all of them, so every figure below is recomputed: foreign keys without an index went from 48.0% to 45.1% (Rails and Drizzle are now included), and tables without a primary key from 11.2% to 5.0%.

The raw material is mcdview's own test corpus: 15,197 schema files collected from public GitHub repositories to make sure the parser survives real-world input. Once duplicates (backups, forks, identical copies) and the hand-made test files were removed, 10,789 distinct files remained. We kept every file that parsed and held at least three tables, and left out Mermaid diagrams, which carry no constraints or indexes. That leaves 6,008 schemas from 6,006 repositories: almost exactly one schema per project.

Public repositories are not all production apps, there is coursework and tutorials in there. To see whether that skews the picture, we looked at the 435 schemas with 50 tables or more separately. It does not, as you will see below.

Of the 73,520 foreign keys we could check, 45.1% are not covered by any index: the column is neither the first column of the primary key nor the first column of an index.

On PostgreSQL, SQLite and SQL Server this is not cosmetic, because the database does not create that index for you. Every time a parent row is deleted, or its key updated, the engine has to find the child rows that point to it, and without an index that means scanning the whole child table. Joins from parent to child pay the same price. MySQL's InnoDB does create the index automatically, which is why MySQL is left out of this figure.

The worst scores come from design tools. With DBML (dbdiagram.io) and pgModeler, close to four foreign keys in five have no index. You draw a line between two boxes and the relationship exists; nothing in the drawing reminds you that the column behind it needs an index.

Among ORMs the gap is huge, and it comes down to defaults. In Rails,

t.references creates the index unless you opt out: only

3.5% of foreign keys are left bare. Drizzle creates

nothing for you, and 50.8% have no index. Prisma, which

does not index foreign keys on PostgreSQL either, sits at

36.8%. Hand-written PostgreSQL lands at

49.1%. The convention does the work that

discipline often does not.

For contrast, GitLab, the largest schema in the corpus with 1,066 tables, leaves 42 of its 1,919 foreign keys without a covering index: 2.2%. A schema that size stays usable because someone checks. Explore GitLab's schema on mcdview.

24.5% of the schemas declare no foreign key, and 43.0% of all tables have no relationship in or out. The relationships exist in the application code; the database simply does not know about them.

MySQL stands out: 46.6% of its schemas without a single foreign key, 72.2% of its tables isolated. Some of that is history (MyISAM, the old default engine, ignored foreign keys) and some is habit. At the other end, the tools that write the constraint for you produce the most connected schemas: pgModeler (4.2%), Prisma (5.8%) and DBML (0.0%) declare a relationship as soon as you model one.

Rails sits in between at 23.1%. A belongs_to

in a model does not create a database constraint; that takes

add_foreign_key or foreign_key: true. LinuxFr.org ran that way

until 2018, then added 29 foreign keys in a single commit:

we told that story as a time-lapse.

If the corpus were dominated by toy projects, the large schemas would look healthier. They do not: past 50 tables, 23.4% still declare no foreign key and 50.6% of the tables are isolated. A schema grows by adding tables around an existing core faster than anyone wires them in.

5.0% of tables have no primary key, and

18.3% of schemas contain at least one such table.

Without a primary key a row cannot be addressed reliably, PostgreSQL's logical

replication refuses updates and deletes on the table until you set a replica identity,

and most ORMs struggle. Hand-written SQL sits between 5.6% and

6.9% whatever the engine. The tools that enforce a key do best: Rails gives

every table an id unless told otherwise (1.8%), and

Prisma requires every model to have a unique identifier

(1.0%).

On PostgreSQL, this query lists the foreign keys whose first column does not lead any index:

SELECT c.conrelid::regclass AS table_name,

a.attname AS fk_column

FROM pg_constraint c

JOIN pg_attribute a

ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]

WHERE c.contype = 'f'

AND NOT EXISTS (

SELECT 1 FROM pg_index i

WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);Or drop your schema on mcdview.dev: isolated

tables show up at a glance on the diagram, and mcdview --lint flags

missing primary keys, isolated tables and uncovered foreign keys, which you can run in

CI.

--lint, run in the same container as

mcdview.dev, with the same Prisma, DBML and pgModeler converters.