"We ran out of columns" – The best, worst codebase

I miss that direct connection. The fast feedback. The lack of making grand plans.

There's no date on this article, but it feels "prior to the MongoDB-is-webscale memes" and thus slightly outdated?

But, hey, I get where they're coming from. Personally, I used to be very much schema-first, make sure the data makes sense before even thinking about coding. Carefully deciding whether to use an INT data type where a BYTE would do.

Then, it turned out that large swathes of my beautiful, perfect schemas remained unoccupied, while some clusters were heavily abused to store completely unrelated stuff.

These days, my go-to solution is SQLite with two fields (well, three, if you count the implicit ROWID, which is invaluable for paging!): ID and Data, the latter being a JSONB blob.

Then, some indexes specified by `json_extract` expressions, some clever NULL coalescing in the consuming code, resulting in a generally-better experience than before...

My worst codebase story:

In my first real job, I worked for a company that maintained a large legacy product programmed in a combination of COBOL and Java.

In order to work on the Java side of the product, you checked out individual files from source control to work on, which 'locked' the files and prevented other developers from checking out the same files. This functionality was not part of our actual source control system, but was instead accomplished with a series of csh shell scripts you could run after ssh'ing into our development server.

Each of our customers had a 'master' jar file that represented the actual final compiled product (a jar file is really a zip file archive, which bundles together the resulting compiled java class files).

Once you had finished implementing your code changes, you ran another set of scripts which found the master jar file for each customer, unzips it, copies the compiled files from your local machine into it, and zips it back up again. Finally the source control lock is released.

This means, effectively, that the codebase was never compiled as a whole at any point in the process, instead, we just manually patched the jar file over time with individually compiled class files.

Over the years, small errors in the process allowed a huge amount of inconsistencies to creep into the codebase. Race conditions would allow two developers to lock the same file at once, or a developer would change a class that was a dependency of some other code that somebody else was changing. Sometimes code changes would make it into some of the customer jar files, but not others. Nobody knew why.

It took a small team two years to migrate the entire codebase to git with proper CI, and a huge chunk of that time was reproducing a version of the codebase that actually compiled properly as a whole. After the project was finished, I resigned.

When I started at my first company, they had a very complex VB application running on dozens of customers around the country, each having some particular needs of course. There was a LOT of global variables (seemingly random 4 uppercase letters) controlling everything.

At some point, the application had some bugs which were not appearing when the application was run in debug mode in Visual Studio. The solution was obvious: installing Visual Studio for each customer on site and teaching the users to run the app in debug mode from Visual Studio. I don't know how they convinced the users to do this and how they managed with the license but it was done.

What happened next was even worse.

There was no version control of course, the code being available on a shared disk on the local network of the company with the code copied over in multiple folders each having its own version, with no particular logic to it either, V1, V2, V2.3, V2a, V2_customer_name, V2_customer_name_fix, ...

After that, when there was a problem for a customer, the programmer went there to debug and modified the code on site. If the bug/problem was impacting other customers, we had to dispatch some guys for each customer to go copy/edit the code for all of them. But if the problem was minor, it was just modified there, and probably saved on the shared folder in some new folder.

What happened next was to be expected: there was no consensus on what was the final version, each customer having slightly different versions, with some still having bugs fixed years before for others.

Probably some of the worst code I ever worked on was a 12k+ line single file Perl script for dealing with Human Genome Project data, at Bristol-Myers Squibb, in the late 1990s.

The primary author of it didn't know about arrays. I'm not sure if he didn't know about them being something that had already been invented, or whether he just didn't know Perl supported them, but either way, he reimplemented them himself on top of scalars (strings), using $foo and $foo_offsets. For example, $foo might be "romemcintoshgranny smithdelicious" and $foo_offsets = "000004012024", where he assumes the offsets are 3 digits each. And then he loops through slices (how does he know about slices, but not arrays?) of $foo_offsets to get the locations for $foo.

By the time I was done refactoring that 12k+ was down to about 200 ... and it still passed all the tests and ran analyses identically.

He acts like sequence key is odd, but that’s quite normal in database world.

https://www.postgresql.org/docs/current/sql-createsequence.h...

Slicing columns into multiple tables is fairly common type of sharding. Sort of SQL way to do columnary store.

The first line really hits me hard. There’s something so incredibly freeing about being a kid and doing stuff like coding. There’s simply no expectations. Even the smallest project felt like such an achievement. But now I code professionally and I don’t know how to turn off engineering brain. I don’t know how to be okay doing something poorly, but on my terms.

I had dubious pleasure of working with similar codebases and devs. I'll remember one of those guys forever, because whenever he wanted to work on a new branch he would clone the repo, make changes to the master branch, and push code to a new repo numbered repo0001, repo002, ... He refused to change his ways, because "I have a PhD so you are wrong".

Another WTF moment was realisation that MS SQL Server does not support BOOLEAN type. That made porting code fun.

Oh man, this article reminds me of an article that was a parody of some horrid business logic.

Something like this: a "genius" programmer was somehow, for some reason using svn commits as a method dispatcher. Commit ids were sprinkled throughout the codebase. A new hire broke the entire system by adding comments, and comments weren't compatible the bespoke svn method dispatcher.

Does anybody remember this article? I really want to read it again.

Would be great to work in such a company as a Linux guru.

There are so many entangled services and machines that you feel like an Indiana Jones. You ssh into a machine and feel century-old dust beneath your footsteps. And you never know what will you find. Maybe a service which holds the company together. Maybe a CPU eating hog which didn't do anything useful last 3 years.

I don't enjoy writing new code much. But in such an environment even with my limited skills I can do decent improvements, especially from security point of view. Feels great

Honestly, I like to work on such systems because:

- there is so much stuff to improve. There’s nothing better than the feeling of improving things with code (or by removing it)

- it’s back to school again and everything goes. You can implement features in any way you want because the constraints the system imposes. Granted, sometimes it’s painful to add functionality

- there’s usually no room to subjective topics like clean code and architecture. The most important thing with these systems is correctness (and this is usually an objective topic)

- nobody can blame you for something that doesn’t work. It’s always the fault of the legacy system

I wouldn’t recommend working on such systems to junior engineers, though.

I don’t really like to work on “perfect” codebases where everyone follows the same pattern, with linters, where if something breaks is because of your shitty code (because the codebase is “clean”). It’s very frustrating and limiting.

went by the name Munch

How do you pronounce that?

Was it like the word munch in “munching on some snacks”?

Or like the name of the painter Edward Munch? https://www.nrk.no/kultur/nrk-endrer-munch-uttale-1.539667 (note: this link is in Norwegian)

Back about 15 years ago, I worked for a web hosting company that provided some sysadmin consultation services. Customer paid us, and I would take a look.

I had one customer who came back with the same request, slightly differently worded, every single month, and every single month I'd say the same thing. They had this site they were running that was essentially a Yellow Pages type site. They had a large set of companies with contact details, each with multiple business categories associated with it. You'd choose a category, and they'd return a list of matching companies.

The problem was the site was really slow. I took a quick look around, and saw that all the time was lost querying the database. Taking a quick look at the schema I discovered that their approach to categorisation was to have a TEXT column, with semicolon separated 4 character strings in it. Each 4 character string mapped to a business category.

So when someone wanted to load up, say, all pest control companies, it would check the category mapping table, get the 4 character string, and then go to the companies table and do:

    SELECT * FROM companies WHERE categories LIKE "%PEST%"

I guess that's probably okay for the developer without real world scale data, and real world traffic counts to worry about. But they had lots of data in the database, and that category field could have dozens of categories against a company. As soon as they had more than about 4-5 simultaneous customers performance started tanking.

I could never get them to accept that they needed to rethink the database schema. One month they were bleating about how is it possible that Google can manage to do such a search across a much larger amount of data, much faster. They really didn't like my answer that amounted to "By having a sane database schema". All they were willing to do was pay over the odds for our most powerful server at the time, which had enough capacity to hold the entire database in memory.

All that remained were ragtag interns and junior developers.

For many people, their first job in software engineering is the worst codebase they will deal with professionally for this reason. The first job hires lot of people with little/no experience. As soon as someone gains some experience than can move on to better paying jobs, where there are better developers with better standards.

My first day at my first full-time programming gig, I was asked to look at some reporting job that had been failing to run for months. I logged in, found the error logs, found that it needed a bit more memory assigned to the script (just a tweak in the php.ini) and let the team lead know it should run fine that night. He was shocked, "Dude, if you just fixed that report you probably just got a promotion, no one has been able to figure that out for months." He was joking about the promotion, but my boss was just as shocked. I'd realize later that most the other people on the dev team didn't like linux and wanted to rewrite everything in .NET and move everything to Windows so no one even tried with anything related to any of the linux machines.

When it comes to building things, the outcome is dictated by the constraints. But not the way most people realize.

The first way constraints affect outcomes is by absolute limitation. You can't exceed the speed of light, so nothing you make will ever be faster than that.

The second way constraints affect outcomes is by the path of least resistance. You have a mountain with two slopes: a long gradual one, and a short steep one. Most people will climb the long gradual one, but it will take them 4x as long to climb it.

The third way constraints affect outcomes is by strategy. If you are given very limited materials and time to achieve a goal, you will have to think up a novel way to achieve it that maximizes your resources.

Constraints can be good and bad. But a common problem occurs when people build something: if they are not given the correct goals, and they have very little stake in the outcome, they will apply their constraints incorrectly. They may make something that works, but not nearly as well as it could have worked if they were properly motivated. The leadership and vision that guides building is much more important than the people doing the building, because it will determine the outcome.

First real job, stayed for 10yrs, 5yrs too long, put VB6 COM objects into the asp.net codebase.

Working on something like that would drive me absolutely batty. I am happy you were able to find your zen in the middle of that chaos. This post truly speaks to the human condition

I'm glad OP was able to maintain a good sense of humor about it. Such discoveries as the nested classes of empty methods have sent me into teeth-gnashing fury followed by darkness and despair. One such experience is why I would rather change careers if my only option were to build on the Salesforce platform, for instance.

Jira's now discontinued server version had a sequence table to stop you sharding it. It also made disaster recovery from a hard shutdown awful. I have nothing good to say about Atlassian.

This codebase sounds like a haunted graveyard[1], where everyone just fixes their local corner of things and avoid the risk of untangling the existing mess.

Not needing to conform to some company-wide standard is probably really pleasant while it lasted, but every such effort adds to the haunted graveyard, and the lack of consistency will eventually come back to bite whoever is still around.

[1] https://www.usenix.org/sites/default/files/conference/protec...

What a beautiful, humorously written, bitter-sweet piece of writing!

Now the story I heard at the time was that once upon a time SQL Server didn't support auto-incrementing ids. This was the accepted, correct answer.

At a company that I used to work, they heard the same rumor, so instead of using identity columns or sequences, they kept a table with a number of ids "available" (one row per id). Whenever unique id was needed, the table would be locked, an id selected and marked as used. If there were no ids available, more ids would be added and then one used. A scheduled job would remove ids marked as used from time to time. Note that there was a single "sequence table", that was shared among all of the entities.

That was not even the weirdest part. That id was unique, but NOT the primary key of the entity, only part of it.

The structure of the database was fairly hierarchical, so you had for example a table CUSTOMER in 1-to-many relation with a USER table, with a 1-to-many relation with an ADDRESS table.

while the primary key of the CUSTOMER table was a single CUSTOMER_ID column, the primary key of the USER table was (CUSTOMER_ID,USER_ID), and the primary key of the ADDRESS table was (CUSTOMER_ID,USER_ID,ADDRESS_ID). There were tables with 5 or 6 columns as a primary key.

That is why people these days tend to use a single JSON blob instead of multiple columns. And because it is so popular, SQLITE and other DBs are building better and better JSON support into the DB.

I wonder if better support of EAV tables would solve this issue better.

If one could do "SELECT price,color,year FROM cars! WHERE status='sold'" and the "!" would indicate that cars is an EAV table ...

    entity attribute value
    1      price     20750
    1      color     red
    1      year      2010
    1      status    sold

    20750 red 2010

Early on in my career, at a large company, I encountered someone who took “codebase” a little too literally. At the time every department had their own developers, sometimes just employees who had an aptitude for computers.

This one guy established himself by making an Access database for their core business, and when the web became a thing, built a customer site. But not on it—in it. He simply served ASP pages directly from the database, inserting dynamic content in queries. When I was asked to help improve their terrible design, I was forced to untangle that unholy mess of queries, ASP (new to me) and HTML. It was easiest to write all the HTML and insert their ASP right before I sent the files back (because I wasn’t given access to their DB/web server). Thinking “I could do better than this” got me into programming.

He was a Microsoft-everything head. Finally went too far when he presented a new web interface starring a Clippy-like parrot using Microsoft’s DirectX avatar API. The executives were unimpressed and then I noted that 20% of our customers couldn’t use his site. (I probably still had a “best viewed with IE” badge on the main site, lol)

Sounds awfully like my first job, with the addition of not having _any_ sort of test - functional, integration or unit. Nothing.

A few months in, when I approached the CTO and asked if I could start writing a test framework, he deemed it a waste of time and said "by the time you'd commit the test, it would go out of date and you'd need to rewrite it".

Naturally, the build would break about 5 times a week.

Boeing was a major customer of this system, so when shit hit the fan at Boeing a while ago, I wasn't surprised.

sure it might be a mess, but at least it's purpose built. I love that kind of performance gains. Honestly most companies die before purpose built code like that becomes a problem.

Two early databases I worked on.

The first contained monetary values. These were split over two columns, a decimal column holding the magnitude of the value, and a string column, containing an ISO currency code. Sounds good so far, right? Well, I learned much later (after, of course, having relied on the data) that the currency code column had only been added after expanding into Europe … but not before expanding into Canada. So when it had been added, there had been mixed USD/CAD values, but no currency code column to distinguish them. But when the column was added, they just defaulted it all to USD. So and USD value could be CAD — you "just" needed to parse the address column to find out.

Another one was a pair of Postgres DBs. To provide "redundancy" in case of an outage, there were two such databases. But no sort of Postgres replication strategy was used between them, rather, IIRC, the client did the replication. There was no formal specification of the consensus logic — if it could even be said to have such logic; I think it was just "try both, hope for the best". Effectively, this is a rather poorly described multi-master setup. They'd noticed some of the values hadn't replicated properly, and wanted to know how bad it was; could I find places where the databases disagreed?

I didn't know the term "split brain" at the time (that would have helped!), but that's what this setup was in. What made pairing data worse is that, while any column containing text was a varchar, IIRC the character set of the database was just "latin1". The client ran on Windows, and it was just shipping the values from the Windows API "A" functions directly to the database. So Windows has two sets of APIs for like … everything with a string, an "A" version, and a "W" version. "W" is supposed to be Unicode¹, but "A" is "the computer's locale", which is nearly never latin1. Worse, the company had some usage on machines that were set to like, the Russian locale is, or the Greek locale. So every string value in the database was, effectively, in a different character set, and nowhere was it specified which. The assumption is the same bytes would always get shipped back to the same client, or something? It wasn't always the case, and if you opened a client and poked around enough, you'd find mojibake easily enough. Now remember we're trying to find mismatched/unreplicated rows? Some rows were mismatched in character encoding only: the values on the two DBs were technically the same, just encoded differently. (Their machines' Python setup was also broken, because Python was ridiculously out of date. I'm talking 2.x where the x was too old, this was before the problems of Python 3 were relevant. Everything in the company was C++, so this didn't matter much to the older hands there, but … god a working Python would have made working with character set issues so much easier.)

¹IIRC, it's best described as "nearly UTF-16"

Once not long enough, I'd worked on 4 projects which was literally copied from the first made and changed parts of the customers and internal users according to each use of it. So, the main problem is bugs found in one project was found on the other 3 and I'd to fix the same bug!

Codebases like this or from the OP is cool to learn how to not do certain things.

A couple of weeks ago I had a flat on the way to the airport. It was a total blowout, and my car doesn't include a spare. We were already over budget on our trip, so I had the car towed to the closest tire shop and had them put on the cheapest tire that could get me to the airport. I know I'll need to replace other tires, as it's an AWD, and I know it's not a tire I really want. I made a calculated choice to make that a problem for future me due to the time crunch I was under.

Programming is a lot like this.

I really love these kinds of stories. Does anybody know if there's a collection of similar stories/code bases anywhere?

I guess there is dailywtf but that's mostly bugs. Probably good enough though

This is glorious. I can imagine people could write versions of this for every industry out there and they would all be equally fun to read.

The ending is pure gold. Some of the best times in my career were working on a codebase for an application serving folks I knew on a first name basis and had had lunch with.

I could talk through pain points they were having, we’d come up with a solution together, I’d hack up a quick prototype and launch it just to them to try out. We’d tweak it over a couple of weeks and when it was good I’d launch it to all customers.

Messy, ugly code base. But it worked well because it wasn’t over-managed. Just developers doing development. Amazing what happens when you get out of the way of smart, talented people and let them do their work.