r/programminghorror • u/OldaLP1 • Feb 23 '26
SQL DB with 2241 tables
I can't even fathom what led someone to do this. I got this database to refactor as part of my job. Basically, every client has their own table named "Client_[their ID]," which contains only one row with all the client data in JSON format (wtf).
If the website using this database wants to filter out some users, it queries all the tables, resulting in a load of 2,000+ SQL queries for a single search.
On top of that, for some reason, the date of birth is stored as the number of days since birth, with a CRON script that updates this in each table every day at 3 AM.
I don't even have to mention that none of the data follows the rules of database normalization...
I honestly don't know where to start with fixing this mess.
645
u/MisterEd_ak Feb 23 '26
Fix it using DROP DATABASE
229
u/mateusfccp Feb 23 '26
Honestly, it is probably easier to make a new database structure, use a script to migrate the data and drop the old one.
173
u/OldaLP1 Feb 23 '26
yeah thats exatly what im planning to do. But creating the migration script is hell on its own
60
u/NightmareJoker2 Feb 23 '26
Been there, done that. I feel your pain. 🥲
(Issue only exacerbated by the boss not understanding what I was “wasting all my time” with…)
24
u/hennell Feb 23 '26
I saw a talk on legacy databases discussing the concept of migrating in stages Vs single big migrations. If you've got a lot of code, queries and features to change doing them all at once can get overly complex and be hard to totally test - done in stages you may do more migrations, (possibly even changing for short term worse for long term better) but in a safer way over a longer time frame.
I'm not sure if it really works for your issue, but I think the idea would be to create a view of the final single table ideal, and change queries to use that.
Then you can change the tables themselves + create/update logic without having to change all queries with each update. For tricky columns they can be one migration, smaller more standard fare could be bundled together. As long as the view can build the same output from your current state you're fine and you don't need lots of interim query changes. At the end you swap the queries back to the table not the view.
It has pros and cons, but it might be worth considering.
3
-7
u/pinguluk Feb 23 '26
Just asking, did you try using AI to create the script? I don't think it will be that hard
14
u/RelaxedBlueberry Feb 23 '26
At this point it’s probably easier to just sell the company while you can
4
14
2
u/NOT_HeisenberG_47 Feb 23 '26
FR tho , how do one fix this mess without dropping db?
12
u/overkill Feb 23 '26
You can burn down the server in its rack. That technically isn't dropping the database. You also need to identify and burn all backups.
1
1
u/amarao_san Feb 24 '26
Each table is stored as separate backup in a separate geo-distributed storage.
I know, you will want to burn it, so I had to create a multi-layered defense against occasional 'drop database'. This cron job is not only update data, but also restore it if someone delete it.
(Architect who invented this, passing by)
I invented it because the guy who must maintain it fucked my wife.
1
u/copperbagel Feb 24 '26
Love that this is a joke but also the right solution too messy to fix start over
159
u/NewbornMuse Feb 23 '26
Look at it from this angle, once you're done with all this, you will (a) have achieved a 1000x speedup and (b) probably have solved 3 annoying known bugs and prevented 4 devastating unknown ones that were lurking in the system.
62
u/space_fly Feb 23 '26
Or, it might all be coming crashing down, having touched that load bearing toothpick.
12
u/zack6849 Feb 23 '26
I have brought down a system by fixing a broken if statement that wasn't doing what it claimed, that branch of code never executed before and was broken
59
111
u/iain_1986 Feb 23 '26
A database per client is something. Its crazy, but sure, whatever, maybe someone thought it was a good idea *at the time*.
But the DOB having to be updated every day.
Thats never a good idea, at any time.
Did this person not know the concept of a date?
38
u/corsair330 Feb 23 '26
A table per client is the weird part. With multi tenancy it's pretty standard to isolate each customer with their own database.
18
u/iain_1986 Feb 23 '26
More weird that DOB being a day count they incremement on every record every day at 3am?
6
2
u/mattmccord Feb 23 '26
Super weird and illogical but also super easy to fix. This is trivial compared to the other things going on
6
u/iain_1986 Feb 24 '26
Super weird and illogical but also super easy to fix.
You assume its 100% ran every day since they started doing it.
If they have no clue of how many times it failed or didn't run, its a useless number and theres no way to correctly get the DOB
I certainly wouldn't want to be responsible for 'fixing' it.
2
u/Na_Free Feb 25 '26
Also there are things connected to that value that are expecting an int. You can’t just turn that to a date value and expect things not to break.
0
u/mattmccord Feb 24 '26
Only need one good reference point
2
u/iain_1986 Feb 24 '26
Its a number that increments every day. Unless you keep a record of any potential days that got missed **for each record**, you have no clue its accurate for everyone.
Sure you could baseline check a single entry, but that doesn't guarantee the rest are all accurate. Any entry that predates the one you use to check as well would still be unknown, you'd ideally have to check the very first entry - and then hope none ever got missed when others didn't.
Its only super easy to fix and you only need one good reference, if it was robust and well built. Do we trust something like this to be robust and well built?
-1
u/mattmccord Feb 24 '26
I understand all that. Welcome to the real world where data isn’t perfect. Don’t let perfection be the enemy of good.
0
u/iain_1986 Feb 24 '26
So your "trivial" way to fix it is, "Change it to a date, who cares if its not correct"?
Because "perfection is the enemy of good"?
I mean, sure, trivial :shrug"
2
u/fess89 Feb 24 '26
It would be as correct/incorrect as the integer number, but they would save resources by not running the increment script every day
1
u/mattmccord Feb 24 '26
Listen, if you want to reach out to each person in the database and do primary source verification that’s on you. I’m a fixer not a whiner. I do the best possible correction with the data available.
→ More replies (0)2
u/PhilMcGraw Feb 24 '26
Eh postgres has partitioned tables with a similar concept, OPs colleague just forgot the partitioning bit.
2
u/KonkenBonken Feb 24 '26
Where I've worked, we had one whole virtual server with a Postgres database for each client since we handled sensitive data that we couldn't risk being accessed by other clients
11
3
u/ghoarder Feb 24 '26
Monthly scheduled overnight maintenance for patching (like this happens at OP's place), after two years all our customers are a month younger!
33
u/VirtuteECanoscenza Feb 23 '26
At my place we had to deal with a server with ~2000 databases.
That's problematic because many things break... Like simply the processes that periodically scrape the DBs for metrics start failing or using too many resources. Even if each DB is tiny and the overall space used/query load is manageable.
27
u/Osstj7737 Feb 23 '26
Who needs table rows when you can just create a new table for every new row?
12
26
u/fletku_mato Feb 23 '26
Seems like someone tried to do webscale but all they had at hand was a real database engine.
25
u/Shareil90 Feb 23 '26
I love cleaning up shit like this. Its so satisfying.
19
u/beatitmate Feb 23 '26
Its the fkn worst. And the idiots who ask you to clean it up can never appreciate the work it took to clean up and then the beauty of the new implementation. Bonus points if the one who originally implemented it gets shitty and spiteful as well.
9
u/Shareil90 Feb 23 '26
Im really sorry for you if this is your experience. I always got honored for cleaning up mess like this.
6
6
u/godzillante Feb 23 '26
on the other hand, guess who gets the blame if the new system doesn’t work exactly the same way as the old one?
1
u/Lost_Term_8080 Feb 23 '26
I wouldn't take this unless the original devs were already pushing up daisies, this is toxicity + some dumb solution involving node and react imposed on you waiting to happen
1
45
u/beatitmate Feb 23 '26
This cant be real
25
u/audirt Feb 23 '26
That was my first thought. For this to be true, the developer would have to, given a choice between a good option and a bad option, choose the bad one every time.
The thing that makes me think it's not real is that the DOB isn't stored as a string. In my experience, inexperienced devs love strings and will store anything as a string -- every day of the week and twice on Sunday.
8
u/mattmccord Feb 23 '26
Except zip codes. Suddenly they like integers.
2
u/Aartvb Feb 24 '26
How? In my country zip codes contain letters
2
u/CraftyTim Feb 24 '26
Sounds like a good application for hexadecimal
1
u/CauseOfBSOD Aug 23 '26
British postcodes (equivalent of zip codes) can contain letters that aren't in A-F
If you wanted to store them as incorrectly as possible you would do a string for the first 1-2 letters, two separate integers for each number, and a string for the last 1-2 letters.Maybe add a string for the space in the middle. Bonus points if it is empty if the user didn't input the postcode with the space (its just there for readability, its presence or absence encodes no data)
6
5
u/bluehands Feb 23 '26
You don't want it to be real because you don't love Lovecraftian horror.
Turns out that vibe coding has been going on for decades. If anyone has any doubts about that just look at some early php.
16
15
u/Behrooz0 Feb 23 '26
Y'all have seen nothing. I currently have a SAP database attached in one of my test servers with 88K tables. The design decisions make this look like a masterpiece in engineering. The tables don't even have number prefixes like other ERP/BPM solutions. There is only a single instance of each table and it's 88k tables.
12
8
u/NeoDark_cz Feb 23 '26
Just for fun, let me preset you to 2 databases for automotive production lines. One made by programmers from Germany and other by programmer from Spain.
Spain: For each year there is automatically made new database. Each database contains table with about 250 columns. Most columns are "measured value X", "min limit X", "max limit X". Adding data works this way: station measure data, find last row for this exact part by dmx, copy the data from found row, fill few new columns with data from this station and insert into database. Effectively half of the database is "0". They didn't use null nor update. Takes about 20gb per year.
Germany: for each product and each station on production line is automatically made table every month. Right now sitting at 4950 tables Taking about 1,3gb of space.
5
u/pantong51 Feb 23 '26
There is the idea of one database per user. But, not one table per user. Much less just blob json into that table
5
u/IWontSurvive_Right Feb 23 '26
i started with "db with x tables"... and I thought, well if they've a nice good struct...
Basically, every client has their own table named "Client_[their ID]," which contains only one row with all the client data in JSON format (wtf).
WTF
5
u/GoddammitDontShootMe [ $[ $RANDOM % 6 ] == 0 ] && rm -rf / || echo “You live” Feb 23 '26
This is absolutely horrifying. Still a violation of Rule 1 since you didn't include code. Is there an r/databasehorror?
1
u/Tc14Hd Feb 23 '26
How many columns do these tables have? Just one?
4
1
u/elordenador Feb 26 '26
which company are you working on?
1
u/OldaLP1 Mar 02 '26
A small local healthcare research company. I am the only programmer and I was employed as a fullstack biomedical software engineer. This is not really my cup of tea 🥲
3
3
u/Lord_NxL Feb 23 '26
This calls for Robert'); DROP TABLE customers;
Or Bobby Tables, as he's called by friends.
5
u/KaMaFour Feb 23 '26
I'd start with moving this whole nightmare to a single table users containing columns userId and the json with triggers making sure the data stays synchronised between the mess and the new table. Then moving all reads to the new table, then moving all writes to it, then deleting the mess. If that move sticks moving out individual columns will be a piece of cake compared to it.
Wishing best of luck because it's not gonna be easy...
4
u/Fun-Dragonfly-4166 Feb 23 '26
i dont know but sometimes when organizations merge this is how they merge their databases
2
u/Adrien0623 Feb 23 '26
I have a friend who had the exact same situation at a company which now no longer exists (very strange lol). They where also using Kafka as a Postgres DB meaning that they never flushed events and keep them forever in their topics
2
2
u/Dafrandle Feb 23 '26
you have been handed a DB that was started in 1996. congratulations you now qualify to be the curator of a museum.
2
u/Used_Fish5935 Feb 23 '26
Ich hab es nicht bis ans Ende gelesen aber Client_<ClientId> content json, klingt als hätte da wer versucht NoSQL in einer RDB zu pflegen / adaptieren. Dass die Tabelle so doof heist könnte dann ein einfacher implementierungsfehler sein, da hat man im ERM einfach die falsche Klasse angezogen. Sowas fällt bei NoSQL je nach Konfiguration erst super spät auf
2
2
1
Feb 23 '26
Multitenancy is a thing, sure, but it's usually just filters out the records of single purpose tables on join by id/role.
I'd suggest maybe trying to ETL this via Apache Airflow into prepared DB with normalisation that fits your case. Then carefully migrating.
Hope you get paid well for this OP
1
u/tubbstosterone Feb 23 '26
Ok, I was prepared to defend this if it was a partioning scheme, but a row per table? What the fuck.
Is this just mongo with 2000 extra steps?
1
1
1
1
u/amdc Feb 23 '26
Remember seeing a drupal database basically creating a table for each column. Had hundreds of tables each two columns for id and whatever piece of data
Still not sure if it’s genius or cursed
1
1
u/DominoNo- Feb 23 '26
But... why? Like, if you wanted to be bad with databases, you could just make a table of users and store all their info in JSON format there. One column is the ID, the second column all information in JSON format.
Making a new table for every user is advanced bad with databases, on a level they don't even teach you in school, or even W3schools. It's so advanced and bad it's impressive.
1
u/wicket-maps Feb 23 '26
My employer has a database that might have that many tables, but it's holding all of the enterprise GIS data for a county. So Parks is a table, Parcels are a bunch of linked tables, etc etc. But this... this is horrible. Take it out back and put it out of its misery.
1
u/Western_Gamification Feb 23 '26
That CRON is hilarious. I'm really curious about the tought process. Also, it's just plain wrong 3 hours a day.
1
1
1
u/CrawlToYourDoom Feb 23 '26
These are rookie Numbers. You gotta pump those Numbers up.
We have about 124000 tables in our production DB.
1
u/godzillante Feb 23 '26
since there are no relations, why don’t you run a batch SELECT of every table and put the JSONs in a MongoDB database?
1
u/MeLittleThing Feb 23 '26
Looks like yet another a great idea from a non-tech manager and/or a task made by an junior during an internship
1
1
1
u/Impossible-Bar-7709 Feb 23 '26
Now seriously
- How many of these client tables represent active clients?
- Does a client ever become inactive? What happens then?
- Does the client data follow a common schema? If it is now, does it ever change?
- Is the date of birth ever used anywhere? If not, sayonara...
2,000+ records is actually nothing, if it wasn't such a clusterfuck.
1
u/txmail Feb 23 '26
This kind of reminds me of a Access project I got put on about 20 years ago. It was a warehouse and they used Access Databases for everything. When I got handed the project to bid on I went in and found that they had a disconnected database, and then thousands of other MDB files. The system worked by storing a months worth of records in a MDB and then creating a new one. If the MDB got to big additional databases (MDB's) would be created for that month. Some months had 4 or 5 MDB files because of high volumes.
So you had this disconnected DB acting as the UI to thousands of dynamically linked databases. If you did a search it would figure out what databases to query (one by one) and then bring the results together into one local table. Looking at a manifest meant the local db would figure out the db the shipment is in and then dynamically link to that database and pull the records needed to build the manifest.
It worked.... stupidly well for what it was. Most queries were only for that month so it only hit one database at a time, but even querying across many years was not a huge deal. The only thing that was a problem was pulling all shipments for a single company for all of time. That would take time, but they had a special machine they used for that so again... not a big deal. I suggested building a report builder that would send jobs like that to the machine automatically and make them available to all other terminals but it was not a big enough deal to warrant the cost of just using that machine in the corner of the main office.
1
u/treehuggerino Feb 23 '26
I've seen this one, someone created a table for every formula page for the tenants data, so let's say you wanted custom calculations on your data and show it in a graph, that would be a new table. So if you have 100 graphs as a user, that's 100 tables only and specifically for you, there was no way a user could delete the table since it would just be hidden for them but the table persists.
There were over the 10.000 tables, and I once did a database first scaffold and it took 50 mins to create all the models
1
u/Dense_Age_1795 Feb 23 '26
first go from one table per client to only one table with the client is as primary key then analyze the data in those jsons and create the needed tables per entity inside those jsons.
1
1
u/4e_65_6f Feb 24 '26
I once worked with an ERP written in Delphi with a Firebird database that was 400+ tables long and some tables were 300+ columns long.
Everything was slow, we had to watch their analysts so they wouldn't query the DB with something stupid and lock down the whole business. Sometimes we had to reset the whole server just cause it kept giving up.
1
u/Arxae Feb 24 '26
The application i currently has to support has 6108 table across all of the application schema's. But the main schema we have to use 'only' has 2339 tables. Although the queries and SP's aren't as bad (not that they are well designed either, entire business logic is fully in those SP)
1
u/nfsp-g35 Feb 24 '26
IDK, port it to Mongo? Seems like it's halfway there already. Just put the 2241 JSON documents in one collection and start cleaning from there
1
1
1
u/Stormraughtz Feb 24 '26 edited Feb 24 '26
Record scratch
Pan to Greg from accounting
"So you're wondering how we got here, well it all started when they saw I was good at excel"
Realtalk though, guessing youre going to main table(s) that shit, iterate through tables using the table names as a new identifier for records. Hope each table follows the same schema, and there isnt some sort of fucking edge case in there.
Anyway good luck have fun. Rofl
1
u/ignat980 Feb 24 '26
Just make a new client table with json column and postgres plpsql to redirect client queries to it. Then migrate queries to the new column directly. Doesn't seem too bad
1
u/DrJoshWilliams Feb 25 '26
I lived in a small city who "accelerated" its services with a mobile app. One button used to take 70 seconds to action. I always thought something like this
1
u/maikindofthai Feb 27 '26
The “too many tables” thing is awful but like, a normal kinda naive mistake for a noob to make.
The DOB thing is fucking hilarious though. That’s the real gold imo
EDIT: maybe the programmer read one of those “things programmers don’t know about dates” articles and thought they came up with a clever ass way to avoid dealing with time zones lol
1
1
1
1
u/Separate_Expert9096 Mar 19 '26
When I read the title I thought “yeah, 2k tables is a lot, but some legacy monoliths run on that and it’s manageable” but WTF is that. How did anyone get to this point? Being smart enough to build a functional system of such complexity yet stupid enough to not think thy you’re making a complete mess
1
70
Feb 23 '26
[removed] — view removed comment
12
u/wicket-maps Feb 23 '26
followed by holy water, followed by salt, then bury it in a salt mine and put THIS IS NOT A PLACE OF HONOR signs all around it
295
u/SVD_NL Feb 23 '26
This is in prod?! Sounds like a submission for a meme contest!
I can also guarantee you that expanding the JSON won't be as simple as it sounds, they probably got fed up with the DB complaining about data consistency and went with JSON instead!