The challenges of supporting foreign key constraints

How often do you expect to make the schema changes? I mean I quoted this bit "...make schema changes sometimes on a daily basis" – is this realistic, or a kind of business insanity typically caused by bad management? Ditto "...but continual, recurring downtime due to schema changes". This really looks like a failure of management rather than a technical problem to be solved.

Also aren't you likely to be doing something larger than just a schema change very often, in which case that would necessitate replacing your application, so changes are not just restricted to the database. You now have a bigger problem of co-ordinating app and DB changes.

I also asked to do you need permanent uptime because in a lot of systems, especially smaller ones (and by the long tail most systems are going to be smallish) the users are very tolerant of an hours' downtime a month, for example.

"Once you have beyond a few million rows in a normal RDBMS, schema changes can take minutes to hours depending on the type"

That's a pretty strong claim; what kind of thing is going to take hours that your database can do consistently? Does it even take hours? I had a 100,000,000 row table of unique ints lying around so I put a foreign key from itself to itself (a bit daft, but just for timing purposes. DB is MS SQL, table is fully hot in memory)

alter table [tmp_ints_clustered] add constraint ffffkkkkk foreign key (x) references [tmp_ints_clustered](x);

21 seconds.

What you're doing (if you can get it correct! Which I have to wonder at) is doubtless excellent for some very large companies, but in general... I'm afraid I'm not so sure.

Edit: I feel I'm perhaps missing your bigger picture.

Ive been researching databases now for several days straight, the choices are overwhelming but I've pretty much narrowed my use case down to an RDBMS system.

I need to essentially handle 100's of millions of "leads" (and 10s of millions per day) which can make up any number of user fields. over 1B total

I need to resolve duplicate leads either in realtime or near realtime. A duplication can occur across a combination of 1 or more fields, so basically OLTP type operations (select, update, delete on single rows)

I do need to run large OLAP queries as well across all data

I've looked at things like scylla and whatnot but they seem too heavy duty for my volume. it's not like i need to store trillions of messages like discord in some huge event log.

I was considering these 3 options...

1. planetscale

2. citus

3. cockroachdb

I havent really narrowed it down further than this, but i liked the idea of still having RDBMS features without needing to worry about storage and scaling with just sheer write volume.

It seemed i could then do my basic OLTP stuff that i need, and citus had a cool demo how some OLAP query on 1B rows ran in 20s with 10 nodes, and that also fits a reasonable time for queries (BI tools will be used for that)

If code has many maintainers, pinky promises to keep referential integrity are bound to be broken eventually. Humans like shortcuts.

When you make schema evolution really easy to do with no downtime, you can also start doing phased deployments where you think in terms of backwards-compatibility. First you add some new/optional parts to the schema. Then you update the applications to use it, where the application has to tolerate data with or without the new bits.

Eventually, you might convert something from optional to required, first for new data entry and later as a conversion of existing data, where that makes sense. Then finally you might deprecate the application ability to handle the old missing bits, and remove that from your code maintenance problems.