SparkFun's migration from MariaDB to PostgreSQL

Doing compression on the ZFS level is significantly worse than InnoDB compression.

InnoDB has a lot of really smart optimizations which make it much better than just zipping things up. Included are the modification log (so you only have to re-compress occasionally) and a dynamic scaling ability to keep compressed pages in memory rather than always decompressing. These optimizations are really only possible with an understanding of the data.

I would only consider ZFS for something like an append-only data warehouse type system.

I don't know the answer in this case, but a few thoughts:

* Why don't you think the optimizer is a consideration? That's a big deal, because it helps the database choose the right algorithms as your data changes. Continuing gracefully while the input data is changing sounds like scalability to me.

* Postgres is generally considered to scale to many concurrent connections and many cores. Useful for OLTP.

* Sometimes the right feature or extensibility hook can make a huge difference allowing you to do the work in the right place at the right time. For instance, LISTEN/NOTIFY makes it easier to build a caching layer that's properly invalidated -- not trivial to get right if the feature is missing, which might mean that you're not getting as much out of caching as you could be.

Tuning for SSDs, compression, buffer caching, etc., are the antithesis of scalability. Those things are pure performance issues. Scalability is about a system that adapts as input data and processing resources change. (Your point about replication does concern scalability, of course.)

Maybe it was not only for scalability, but also for reliability or embracing the Do the Right Thing thing?

From my experience with PostgreSQL and MySQL, it is a bit like the difference between python and php, one is a great work of craftmanship that is reliable, predictible, coherent, enjoyable to work with and minimise the wtf/mn rate (which is the best quality measure in software), while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping.

Multi-version concurrency control (MVCC) is more performant and scalable in PostgreSQL than in MySQL with InnoDB (which uses row-level locking).

The big ball of mud architecture is quite popular in the business world.

I think you are reading too much into it. They never said that there would only ever be one PostgreSQL db with everything. As you point out there are advantages to separating certain functions into separate dbs, but for most businesses that go that route, it makes sense to use multiple instances of the same core tool.

Also, wanting to have a single authoritative view of inventory and order data is NOT a big ball of mud. Nor is a single big db with everything. The bid ball of mud generally starts with the link editor and developers who think that the only way to integrate functionality is to link it all into one big binary. For all we know they are planning to use RabbitMQ as their main integration tool, and build lots of small single purpose apps that work together like an orchestra.

This sounds counter-intuitive, but the best (from the technical angle) companies I worked at -- or know friends at -- are muddle the "internal enterprise tools" and "public facing applications" divide quite a bit, but (and here's a catch) when their perceived and actual primary area of competence is building applications that at least somewhat resemble the internal tools in question.

Sparkfun is in an odd place, they could call themselves a store, but then again so could Amazon -- it's not clear that selling technology is requisite to technology being a primary area of competence.

> If you can share, what's the reason you folks went with this architecture?

In its present state it's a fairly sane architecture for our purposes, but the only honest & concise answer to this question is that we didn't know what we were doing and it just kind of worked out that way.

Not coincidentally, this also describes the architecture of the business as a whole.

I was thinking the same thing about wanting to go PostGIS. Though they could probably do just as well for their needs (presuming the case is in a single building) by using vanilla postgres' geometric functions.

Kind of a moot point since they were using MariaDB anyway, but the MySQL geospatial functionality hasn't gotten much attention in a long time. I remember quite distinctly hearing at the MySQL BoF at OSCON 2011 that the original author of the MySQL geospatial stuff was gone from Oracle. I find it hard to believe that they haven't picked up anyone to continue development on that but when you compare features with PostGIS, there's almost no point in even trying to compete.

/geo-rant

I presume you're recommending PostGIS because of the allusions to the inventory location problem. While better modelling of geo data would be nice it's kind of a different concern. For us inventory location isn't about where on Earth our inventory is, or even where geometrically it is relative to something else, it's about where in a hierarchy of named locations it is. For instance: we have 10 of widget X in the storefront stock room, row F, rack 17, shelf 3, bin 1. Or we have 22 of widget Y in the receiving room on the quarantine table. Stuff like that. When modelling that many semantic nested locations and all of the transfers of items between them the primary issue is scalability and speed, not so much modelling geographic data.

I also have this question. There are technical reasons that PostgreSQL is better than MySQL, especially when it comes to features, but none are mentioned here.

> From the development perspective Postgres was a necessity. Not a slam dunk but objectively a better tool for our use case.

In what way?

> 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?

HN would be interested in those reasons, I know I am.

> 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.

Neither do I, since none are given.

Though I also understand that the point of the post was about trust. It's great that there is that trust within SparkFun, I would love such an environment.

Awright, so I didn't write the linked piece, but Chris isn't talking about scaling our web traffic by an order of magnitude. We're a small-to-mid-size web store & light manufacturer and we do decent business and very occasionally get a bunch of traffic as far as mid-sized web stores go. We're not a big service provider, and we're never going to be one in the sense where we have to worry about scale the way that people with Real Problems do.

We do plan to store a bunch more data than we deal with now, but mostly we migrated because we're looking to build a better ball of mud, to borrow a perfectly legitimate analogy from elsewhere in the thread. What we'd like to scale somewhat cleanly is a business which benefits from us building less broken applications, and PostgreSQL is obviously a less broken foundation for the kind of software where it matters if you are correct about things.

If anyone would like a reason to let us know at length what incomparable buffoons we must be for any of writing PHP, rolling our own application framework, still owning physical hardware, or getting a little overexcited when the code doesn't break too badly, I will cop to all of those and more in public, but please don't read the linked post as a strong technical claim about Hard Problems in the Land of Webscalonia which demands & merits a detailed refutation. Posts on sparkfun.com don't tend to be written for a web nerd audience (we sell blinkenlights) and most of us survey a very different conceptual landscape on a day-to-day basis from the average HN reader.

(For clarity here, I write web stuff at SparkFun, and most of my time lately has been on this project. Author of linked piece is my boss.)

Edit: And my apologies if this reply to a rather simple question comes off a bit reactive; consider this my reply to the thread as a whole?

I think its less hammer more nail vs screw. Both can hold wood to a wall and will be part of the building when you are done. For example a nail can splinter the wood which can cause a break down the line while a screw wouldn't encounter such an issue.

The PostgreSQL documentation. http://www.postgresql.org/docs/9.3/static/index.html

The introduction tutorial is concise but more than sufficient.

The reference guide is well written, laid-out and covers everything you need: the database server, its configuration and tools, the SQL language, its operators, data types and grammar, the built-in procedural languages, the low-level client library, and all the database internals (system tables, data file formats, network protocol, query optimiser, indexes, etc).

For your case, especially using the Django ORM, no reason whatsoever.

Or, ok, there may be a reason, South used to not play so well with MySQL/MariaDB, this has improved over time but still may be a reason to use PostgreSQL

(Or just don't bother and use SQLite, depending on your case it may be just fine)

Yep. South is much happier with postgrSQL as it can do schema changes in a transaction and roll them back if there's a problem.

MySQL will just leave you with a half completed schema change which you'll have to fix by hand.

I don’t know anyone who has ever regretted choosing PostgreSQL or migrating to PostgreSQL. Not a single person. None.

Inventory location is one of many, many problems our ERP system is tasked with. And we've looked at a lot of software packages over the years, both proprietary and open. In terms of open software the options are actually pretty limited and have the potential to introduce more problems with integration and customization than they would purport to solve by bringing in new suites of features. What is less limited and truly abundant are examples of other companies in our same position that went full-bore into an off-the-shelf solution and ended up severely disrupting their business operations and aborting transitions.