How long does a database table live?
Study
How long does a database table live?
mcdview.dev · 7 min read
We replayed the git history of the database schema of nine open-source apps (Mastodon, GitLab, Forem, Cal.com, Lobsters, diaspora\*, Documenso, Formbricks and Umami), one state per week, and followed 1,112 tables from the week they were created to the week they were dropped. Tables have a life expectancy, and it is lopsided.
Most tables are forever
Once a table exists, it tends to stay: 84.1% of tables are still there one year after they were created, 77.5% after three years, 72.8% after five. The founding tables are even tougher: of the 71 tables present in the first recorded state of each schema, 56 still exist today.
50 %60 %70 %80 %90 %100 %01 yr2 yr3 yr4 yr5 yr6 yr7 yr8 yr
Share of tables still alive, by age. Kaplan-Meier, 1,112 tables.
The curve drops early, then flattens. That shape is the whole story of this study.
When a table dies, it dies young
229 tables were dropped over the period. 35.8% of them were gone within a month of being created, 49.3% within three months, 70.7% within a year. The median age at death is 106 days.
These are experiments, features that did not survive review or production, tables replaced during a redesign. A table that makes it through its first year is almost always there for good. Design decisions harden fast: the name, the keys and the columns you give a table in its first months are very likely the ones the project will live with.
Schemas grow in jumps
A schema does not grow along a smooth line. In each project, the busiest 10% of the weeks with a schema change account for 49.0% to 80.0% of all the tables added. Long quiet stretches, then a migration that adds a whole feature at once. GitLab added 87 tables in a single week; Lobsters, at the other end, went 519 days without touching its schema.
| Project | Period | Tables | Tables dropped | Before the 1st foreign key | | ------------------------------------------------------------ | --------- | -------- | -------------- | -------------------------- | | GitLab | 2011-2020 | 6 → 324 | 15.8% | 4.7 years | | Forem | 2018-2026 | 29 → 128 | 19.5% | 0.1 years | | Mastodon | 2016-2026 | 2 → 116 | 12.8% | 1.0 years | | Cal.com | 2021-2026 | 3 → 102 | 21.5% | from the start | | Documenso | 2022-2026 | 1 → 51 | 19.0% | 0.1 years | | diaspora\* | 2010-2017 | 13 → 49 | 41.0% | 0.2 years | | Formbricks | 2022-2026 | 5 → 49 | 30.0% | from the start | | Lobsters | 2012-2026 | 8 → 38 | 2.6% | 6.3 years | | Umami | 2020-2026 | 4 → 26 | 48.0% | from the start |
Each project name links to its schema time-lapse on mcdview.
Foreign keys come late, or leave
The older Rails apps lived for years with no foreign key in the database: GitLab for 4.7 years, Lobsters for 6.3 years, Mastodon for 1.0 years. The relationships were in the models, and the database only learned about them later, in batches. The projects started in the Prisma era declared foreign keys from day one.
And one project went the other way. Umami dropped its foreign keys on purpose: the commit of 2022-12-27 is titled "drop foreign keys, add missing indexes", and the schema has used `relationMode = "prisma"`ever since, which leaves referential integrity to the ORM. Its tables are still related; the database just does not enforce it.
Renames are rare, except when they are not
We detected 23 renamed tables across the nine projects, 14 of them at Formbricks, where the table that holds organizations has been called, in turn, `team` → `workspace` → `organisation` → `team` → `organization`. Naming the core concept of a product is hard, and the schema keeps the record of every attempt.
Columns churn more quietly: 7.6% of the columns ever added to a table that still exists were later removed from it.
What to take from it
- Spend your design effort on the first tables: they will very likely outlive the original team.
- Expect experiments to fail fast, and keep them in tables you can drop without touching the core.
- Declare foreign keys early. Adding them years later is possible, GitLab and LinuxFr did it, but it takes a cleanup project to get there.
- Source: every commit that touches the schema file (`db/schema.rb` or `schema.prisma`, following renames of the file), reduced to one state per ISO week (the last commit of the week), dated with the commit date: 1,605 states. GitLab is covered from 2011 to 2020, the years it kept `db/schema.rb`.
- Each state was read by mcdview 0.34.2, in the same container as mcdview.dev. 14 Prisma states that the Prisma CLI rejects were skipped, not counted as deletions.
- A table that disappears the week another appears with at least 70% of the same columns is counted as a rename, not a death and a birth.
- Survival uses the Kaplan-Meier estimator: tables still alive at the last state are censored, otherwise lifetimes would be underestimated.
- Discourse is left out: its `db/structure.sql` only exists in early 2013 and again from 2026, which makes lifetimes unmeasurable.
- Limits: a table created and dropped within the same week is not seen, and a rename that also changes most columns counts as a death and a birth.
To see these schemas move, open the time-lapses linked in the table above, or the open-source schemas page. The LinuxFr.org story, where 29 foreign keys arrived in a single commit, is told here.