On Breaking Things... Again

The devs switch underlying databases for SparkFun.com and somehow, miraculously, the sky stays put firmly up above.

Favorited Favorite 0

Back in August we pushed out a big change to the entire SparkFun.com stack. All the good stuff was happening under the hood and by all rights it should have been one of those "transparent" changes - nobody outside of our own dev group would know or care what changed and everything would still work. Meanwhile, said developers would have a cleaner playground on which to play and build.

But as it goes, the one untested thing that did in fact break was our currency converter. In short it made all of our prices look like $0.00 in every currency for the morning, so that was fun.

Well, last night we pulled everything down again. The last time was in preparation for making the leap from MySQL (actually MariaDB) to PostgreSQL, and this one was actually making that leap. If you're reading this then it worked! These words are being piped to you from a Postgres database wherein lies the rest of our many years' worth of data.

-> Last night's order of operations
Last night's order of operations <-

There are plenty of angles on such a transition that could be explored. The fact that we used MySQL's query cache as a speed crutch for years without fully realizing it and having to dive deep into well-formed indexing and materialized views to retain speed with finer control is one. The nightmarish hodge-podge of character encodings that took herculean acrobatics to cram everything into UTF-8 is another. If you've got questions or just want to jaw about database geekery post a comment below; the devs are watching the site like hawks today.

Instead I'm going to avoid the dirty technical details and explore something even dirtier: business politics!

As mentioned here before, SparkFun.com is really the tip of a much bigger iceberg called Sparkle. Sparkle is an internal website where we hang every tool we need. It does everything but the core accounting. Think about what a business like SparkFun might possibly need to do besides the bookkeeping and you'll begin to get an idea of what Sparkle does. It's what the shippers use to ship almost a thousand orders a day. It's what the production techs use to track the builds of millions of widgets. It's what gets used to manage every product, customer, order, tutorial, blog post, job applicant... we even use it to manage the Dog Tribunal to an extent (that's another blog post entirely).

But Sparkle is woefully incomplete. One big shortcoming is our lack of inventory location (as recounted in this blog post from the beginning of 2013. A lot has happened over the course of the year and Sparkle's grown plenty of new tentacles but inventory location - the notion that Sparkle knows more than just how many of a given thing we have in the whole building - remains the most critically needed feature. We also have lofty plans for how rebuild checkout, push our education site into new territory, and about a centillion other projects screaming for attention.

It took some internal wrangling to argue the point but the devs and I put this Postgres transition on the road map firmly before ascending inventory location peak... and every other peak we'll be scaling in all coming years. Such things can be tough sell. After all, MySQL got us this far. Why dump it now, with so much effort and time, when we could be doing so much else?

For us it came down to using the right tool for the job.

-> That tool and that job are incompatible
That tool and that job are incompatible <-

Inventory location is going to be hard. We have a lot of data now, but it's a blip compared to what we'll have once we get to tracking every movement of bunches of items around the building and beyond. It will literally be the scaling of our database by at least an order of magnitude, possibly two. It could be done with MySQL, but what was the trade-off?

From the development perspective Postgres was a necessity. Not a slam dunk but objectively a better tool for our use case. At the end of the day, though, we're building SparkFun.com and Sparkle for the SparkFun community - the customers and the staff that makes it all go. If we're going to divert for a few months to do this thing then, well, we better have a good reason, right? And if we have countless reasons, all good, but they're nuanced and technical and tricky to impart to a non-technical audience, where does that leave us?

In the end it came down to trust, and this is the point of this blog post. The vast majority of the company probably still doesn't get why we went through this painful contortion at the expense of doing, well, a lot of other things. Sound a fury signifying nothing and all that. But I was relieved and energized by the fact that, after probing the technical comparisons and time lines to near exhaustion, all of the stakeholders from the high-up-manager-types on down didn't really need the deep understanding of the problem because they trusted me. They trusted the IT group on whose behalf I spoke and pushed and campaigned. Truth be told I didn't even need to push all that hard - some folks took me at my word that this was a necessary step. Such implicit trust could only be earned and must never be squandered. It's truly one of the strongest assets at this company: there is a lot of trust between departments. People expect that you generally know what you're doing and will do it well. I consider this a vital feature of a healthy organization so count us fortunate in that regard.

My team and I tested our company's trust pretty hard with this one. But at the end of the day I trust my team that we made the right move and it'll pay off.

Comments 34 comments

  • M-Short / about 10 years ago / 4

    With a home built back end that does everything except for our accounting we need an IT staff we can trust. Most of the people in this building have no idea what it is you guys do other than you keep us up and running and without you we'd be in big trouble. Thanks IT for all you do and keeping us up and running.

  • Member #460040 / about 10 years ago / 3

    Just a comment on inventory location. I had the honor of project managing the migration to a shiny new inventory system for a 250,000 sq foot warehouse with about a million SKU's . It was a piece of cake! Other than the voices in my head and the 2d symbologies I have tattooed on my body, I am perfectly fine. Go for it! What could possibly go wrong?

    Rob Keown Room 201 Montague Asylum for the Insane

  • sgrace / about 10 years ago / 3

    Congrats guys on a successful migration (hopefully. crosses fingers). You all deserve a half a day of watching silly cat videos and drinking beer to unravel the stress spring you all felt for the past several months.

    I know a bit about the reason why to move to PostgreSQL from MySQL, but were other database types/architectures you guys explored before making the move?

    • There are always other databases in play. These comments, for instance, are stored in MongoDB along with some other less-relational data. As far as relational data stores go, however, we knew we wanted an open source database that employed ANSI-compliant SQL and would scale well (immediately eliminating big obvious options like Oracle or MSSQL). PostgreSQL was the obvious choice and we've actually been wanting to switch to it for a number of years. In the run-up to actually doing the switch, as we dug into Postgres's intricacies out of necessity, we kept discovering features that reinforced our confidence that it was the right choice, curtailing the need to audit a lot of other options to any great depth.

      • dafoink / about 10 years ago / 2

        I was just wondering, besides "wanting" to go open source. Was there other reasons why open source was the choice? Price, changeability, etc.? Just wondering. If it is the ability to change, do you think that you would ever have to change source if you choose a solution like Oracle or MSSQL? Just wondering. as someone who has done a lot of programming in MSSQL and Oracle, I have never really seen a reason to extend the DB for even some of the most complex operations (they are pretty extendable through stored procs, functions, etc.).

        just wondering. don't hate me because I make a living off of non-open source :-)

        • I was just wondering, besides “wanting” to go open source. Was there other reasons why open source was the choice?

          Of course this is partially an ideological/political decision. On the other hand, it's also a pragmatic one:

          • We're explicitly a company trying to make open source hardware work, and in furtherance of that goal, it helps a lot to walk the walk with regards to our software choices.
          • We don't especially want to pay for MSSQL. We really don't want to pay for Oracle.
          • We're generally averse to Microsoft technology, and keep it out of our core stack as much as we possibly can.
          • Closed systems die.

          We don't have any special intentions of doing development work on PostgreSQL, any more than we hack on the Linux kernel or PHP, but we still benefit from the openness of those systems. (And hey, if we want to contribute, it's nice to know we've got the option.)

          • Jasmine2501 / about 10 years ago / 2

            LOL, tell me again how SQL Server is a dead end. I've been raking in cash for 20 years working with it :)

            I appreciate your decision, but I don't think it's practical. It's political, which is a perfectly good reason.

            • LOL, tell me again how SQL Server is a dead end. I’ve been raking in cash for 20 years working with it :)

              Yeah, I mean, I've generally heard that it's a really solid product. I'll stand by my statement, though: Closed systems die. Of course, all systems die, but all else being equal, I'm convinced the closed end of the spectrum has worse survival characteristics than the open end. Then again, that's a minor consideration in this context - I don't really think any of the major commercial database vendors are going away any time soon. The argument that the openness of code is a practical consideration doesn't especially hinge on it in this case.

              I mean, there's no sharp line between the politics of software and the pragmatics of software. I started a career avoiding Microsoft tech when I could for political and aesthetic reasons, but these days the practical reality is that even if I was inclined to work with that stuff, my accumulated expertise is all elsewhere. I work on a team whose accumulated expertise is mostly elsewhere. We've got a bunch of things that live outside of the Microsoft ecosystem, and it's pretty dirt-level practical to avoid entangling them with an entire extra stack.

              • Jasmine2501 / about 10 years ago / 2

                I take a rather selfish look at that subject. I go where the money is. SQL Server is a great product, for sure - I even think it blows Oracle out of the water in terms of performance these days, something which Oracle has been banking on for decades. I wouldn't be able to hang with you guys. I don't know much about open source stuff unless I use it for personal projects. I like all kinds of development, and I've worked in hardware, software, databases, integration, networks, etc - whatever pays well. I'm a team player who cares about the company welfare, but I don't push political arguments any more - I choose the political environment I want. I've quit some really cool jobs over bad politics (aerospace).

                But, as you say, that's the place I'm in due to experience. I'm a Microsoft-stack hired gun. I'm branching out but I'm limited by my experience, as I think we all are. It would be a bad idea for my own company to go to all open-source stuff because of that. I'd have to fire myself and poach devs from you :)

      • enigma32 / about 10 years ago * / 1

        Is there a previous post that discusses your reasons for switching to Postgres from MySQL? edit: .... reasons for switching away from MySQL?

    • Like Frencil says, PostgreSQL was a pretty obvious choice given the constraints of "needs to be SQL that can handle our existing data, the big proprietary players are right out".

      As far as other kinds of database go, we've got a bunch of legacy data that a) is already in an RDBMS, and b) should be in an RDBMS. I've always kind of hated SQL - it's not the way I naturally think about a lot of problems - but the more time I spend on this stuff, the more I respect SQL. It represents a whole lot of problems already solved by a whole lot of people much, much smarter than I am.

      Aside from Mongo, there're a ton of other data stores out there that we might use sooner or later, but I think the bulk of our core business will remain in a SQL db for the imaginable future.

      • Jasmine2501 / about 10 years ago / 3

        Go read EF Codd's original work on the relational algebra - you'll REALLY respect relational databases if you understand it. Most people never take advantage of their database system, thinking of it as persistent storage for spreadsheets, but if you can get past that obvious relationship you can do some really powerful stuff.

  • BeerCannon / about 10 years ago / 2

    ..and was that a "Day of the Tentacle" graphic in the blog for this post???

    • It was the first thing that came to mind when describing Sparkle growing many tentacles... because it was just such a terrific game. Where else could you put a wig of spaghetti on a mummy wearing roller skates to win a beauty pageant? =)

      • BeerCannon / about 10 years ago / 1

        It was a great game.. as I recall, one of the first CD-ROM based games that actually attempted to sync the animation with the spoken audio, and did a decent job of it. I tried to run it on a modern PC at home to show my kids, but couldn't get more than the title screen to show before I hit my .pif tweaking limit.

  • EricM / about 10 years ago / 2


    I've actually done a large MySQL->PGSQL conversion in the past. That was... fun. And actually need to do it again (with a different site of course) soon.

    Did db_converter work pretty well as it stood for you, or did you guys have to hack it any?

  • l0gikG8 / about 10 years ago * / 1

    When looking at other users' customer profile pages, I do not see any of their new comments made after the db change-over. When I'm logged in looking at my own profile page, I only see new comments, no pre-conversion pithiness. Is this a bug or a feature?

    • Certainly sounds like a bug. I couldn't think what in the MongoDB comments would have been affected by PostgreSQL, but it occurred to me that we might be looking for a string somewhere we have integers, or vice versa. We'll have a look.

  • Calif / about 10 years ago / 1

    Still remember when slashdot encountered the pain of mysql in 1999. Back then, it was memory leaks & bugs. We get older, but the internet never ages. It just represents that stage of career when a sysadmin is tasked with building a 1st website & follows the same path, over & over.

  • kremlan / about 10 years ago / 1

    So... What were the advantages PostgreSQL provided you over MySQL/MariaDB?

    • I'll write up a short blog post with some notes.

    • crlanglois / about 10 years ago / 1

      I had the same question, the 'why' explanation here was pretty vague. I'm more a developer than a DBA and I'm fairly familiar with MySQL and I didn't get that part of the article.

  • SQLServerIO / about 10 years ago / 1

    Are you guys using foreign keys at all? I have found, to my dismay, that MySQL may not honor keys even if they are enforced and you can end up with inconsistent data. Usually, I have found this out during pre-migration testing but it can be a huge PITA. I have also seen FK's defined on the postgres side but not enforced leading to the same kind of condition. Did you have to deal with this and if so how did you handle it? For the record, I'm a MS SQL Server guy as well but really love Postgres quite a bit as well.

  • tuba_man / about 10 years ago / 1

    As a fellow sysadmin-type pokemon, nice work! Having trust from your higher-ups is a great feeling, and makes the job a lot easier to do. Congrats on the successful upgrade. :)

  • Member #11476 / about 10 years ago / 1

    Two locations I've always included in any inventory tracking system I've supported are 'Limbo' and 'Hades'. Inventory is so much like a Dante-inspired story that it always felt appropriate.

  • Jasmine2501 / about 10 years ago * / 1

    So, we should expect to see lower prices now as a result of this windfall of efficiency, right?

    Funny Photo

  • Member #459164 / about 10 years ago / 1

    Congratulations on the migration. I read about the inventory tracking problem. It almost sounds like what you guys need is a small WMS package. WMS - Warehouse Management System. There are many vendors out there and a ton of packages. But basically a WMS package would do a lot for you, including helping you map out the locations in your warehouse.

    A WMS could help: 1. Track incoming receiving and purchase orders. 2. Manage receiving locations, inventory locations, and shipping docks. 3. Help put away stock to correct locations. 4. Help with picking orders from the correct inventory locations. 5. Reports to track inventory flow. 6. Cycle counting - to count locations and figure out what is there ( and identify inventory shortages ). 7. Interfaces to UPS, Fed-Ex, etc for shipping.

    Etc, Etc. I think you get the point.

    You could probably do your own system too, but I would recommend studying the existing WMS/Supply Chain software solutions, because just tracking inventory is the tip of the iceberg and there is a lot more capability in some of the WMS packages then you could just code on your own quickly.

    Good luck! Tracking inventory is definitely something that is needed as you guys ship and receive more products.

    • Erik-Sparkfun / about 10 years ago * / 1

      We've actually slowly built towards our own solution for quite a few years now, while directors have looked at oodles of different off-the-shelf solutions. None of them worked the way we wanted them to, or they came with strings attached we weren't interested in dealing with, so we're doing our own thing (still). The actual inventory location tracking is the big thing we're still missing, and this database migration was one of the final hurdles we had to clear before full implementation.

  • BeerCannon / about 10 years ago / 1

    Congrats on nailing down the infrastructure. I've lived through company-wide platform migrations (ISAM, B-tree, DB2, SQL) a few times in my career, so I know the pain when things go wrong and the exhilaration when they go 'as planned'.

    I've been running MS SQL Server at the core of all of our systems -- operations apps developed in-house with Visual Studio and accounting via Great Plains Dynamics -- for over 15 years without a hiccup. Well, the accounting was initially C-tree but we converted it to SQL perhaps 12 years ago.

    Get your database right, and the sky's the limit. Glad you were able to implement a DB that will expand your capabilities.

  • Ted M / about 10 years ago / 1

    Is "New Products" working correctly? I'm seeing a lot of old products.

Related Posts

Recent Posts

GEO Week 2024


All Tags