Forem: Optoro

Vacuum after you tidy up

Jeremy Stuckey — Wed, 26 Jun 2019 21:02:05 +0000

A story about a data migration, a sudden performance issue, and how we fixed it.

Here at Optoro, we collect a lot of data from our clients. In an ideal world, all this data conforms neatly to our schema, but that's often not the case. A common technique we use in our Rails applications is to store extra data in a "catch-all" database column such as a serialized hash or a jsonb column. We prefer not to drop any client data on the floor in case we need it later.

In this example, we have an ActiveRecord model named OrderItem with a postgres jsonb attribute called extra_data. The code that populates extra_data looks something like this:

known_attributes = attributes.slice(*OrderItem.column_names.map(&:to_sym))
extra_attributes = attributes.except(*OrderItem.column_names.map(&:to_sym))

known_attributes[:extra_data] = extra_attributes

OrderItem.create(known_attributes)

Storing extra fields in this way allows us to revisit this data if the need arises. Occasionally, we realize one of these client-specific fields is a good fit for our domain model. When this happens, we want to turn it into a proper database column of its own. The procedure is fairly simple:

Run a database migration to add the new column
Routinely run a script to move data from jsonb to the new column
Change the code to start using the new column

We performed this procedure recently with a field named purchase_order_number, during which we migrated data for about 4.5 million rows. Not long after, another team in our company told us that querying by this new field was extremely slow. They even produced a graph to back up the claim. The green line shows queries that include purchase_order_number, and the blue line shows queries that exclude it. Querying by purchase_order_number was taking an average of 2.4 seconds. Not good!

The first thought we had: "surely we indexed that column". A quick inspection of the database schema showed that we had.

The next debugging step was to use postgres' EXPLAIN functionality. We whittled down the query to its simplest form and ran it through EXPLAIN ANALYZE.

EXPLAIN ANALYZE select * from order_items
                join orders on order_items.order_id = orders.id
                where order_items.purchase_order_number = 'PO00000001';

                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=512087.46..1266205.21 rows=23649 width=69) (actual time=2630.284..2813.946 rows=1 loops=1)
   Hash Cond: (order_items.order_id = orders.id)
   ->  Bitmap Heap Scan on order_items  (cost=6553.11..726261.45 rows=341748 width=4) (actual time=0.065..0.067 rows=1 loops=1)
         Recheck Cond: ((purchase_order_number)::text = 'PO00000001'::text)
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on index_order_items_on_purchase_order_number  (cost=0.00..6467.68 rows=341748 width=0) (actual time=0.057..0.057 rows=1 loops=1)
               Index Cond: ((purchase_order_number)::text = 'PO00000001'::text)
   ->  Hash  (cost=444843.38..444843.38 rows=2505917 width=69) (actual time=1557.832..1557.832 rows=2545639 loops=1)
         Buckets: 524288 (originally 524288)  Batches: 16 (originally 8)  Memory Usage: 36865kB
         ->  Bitmap Heap Scan on orders  (cost=54361.42..444843.38 rows=2505917 width=69) (actual time=228.596..694.259 rows=2545639 loops=1)
               Heap Blocks: exact=45059

 Planning time: 0.689 ms
 Execution time: 2821.106 ms
(16 rows)

I don't claim to understand the intricacies of that query plan, but it seemed suboptimal. Note the execution time of 2821.106 ms. That felt like a terribly long execution time to query an indexed column. It also lined up nicely with the response time reported earlier.

After some additional experimentation, a teammate of mine (who is much savvier with postgres) suggested running VACUUM ANALYZE. From the postgres docs:

VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

Frequently-updated tables... we just updated about 4.5 million rows when we migrated the purchase_order_number field. Further on in the docs:

ANALYZE Updates statistics used by the planner to determine the most efficient way to execute a query.

That could explain the suspicious-looking query plan we saw above.

With anticipation and bated breath, we ran VACUUM ANALYZE (it took a while to finish). Afterwards we re-ran our query.

EXPLAIN ANALYZE select * from order_items
                join orders on order_items.order_id = orders.id
                where order_items.purchase_order_number = 'PO00000001';

                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=1.01..2496.88 rows=15 width=69) (actual time=0.063..0.066 rows=1 loops=1)
   ->  Index Scan using index_order_items_on_purchase_order_number on order_items  (cost=0.57..692.77 rows=213 width=4) (actual time=0.043..0.044 rows=1 loops=1)
         Index Cond: ((purchase_order_number)::text = 'PO00000001'::text)
   ->  Index Scan using orders_pkey on orders  (cost=0.44..8.46 rows=1 width=69) (actual time=0.016..0.018 rows=1 loops=1)
         Index Cond: (id = order_items.order_id)
 Planning time: 0.501 ms
 Execution time: 0.113 ms
(8 rows)

Our execution time went from 2821.106 ms to 0.113 ms. That is ~25,000 times faster! We solved the case of the slow purchase_order_number query.

VACUUM and ANALYZE should be part of automated database maintenance, but in our case the data migration had an immediate negative impact. The moral of the story is to remember to vacuum after you tidy up.

UPDATE!

A colleague (and former DBA) pointed out that ANALYZE was likely what resolved this particular issue rather than VACUUM or the combination of the two.

When we added the column and index, postgres gathered statistics on their state. At the time that was about 4.5 million null values, which the query optimizer deemed not so useful. Running ANALYZE after the data migration regathered statistics on the index, which suddenly made it much more appealing to the query optimizer.

Why we chose Vue

Muzafar Umarov — Fri, 17 May 2019 19:50:37 +0000

When I joined Optoro, a technology company that helps retailers manage their returns, five years ago, the world of front-end development was in a completely different place. Grunt was the go-to tool, and Gulp was becoming popular. "Bundling" was just concatenating all the JS into a single script.js. AngularJS was the hottest front-end framework, and CoffeeScript was a socially acceptable language to use.

If you were to time travel from four or five years ago to today, there would be so many new concepts to learn. Changes to ECMAScript alone are huge since five years ago. These things make transitioning from a Gulp + AngularJS to a more modern setup more tricky. On top of that, every developer has their own opinions based on what they've used. Convincing a group of people that X library is better than what they are using takes a lot of effort. Because changing a library/framework isn't as simple as changing your package.json and a few lines of code. These libraries/frameworks are investments. There is always a cost to adopting a new technology. So balancing costs and benefits of a certain technology is tricky when introducing a technology stack.

Originally we started out with “simple” jQuery user interfaces. As our product grew, we wanted to scale our user interfaces. AngularJS was the best choice for us at the time and we paired it with Grunt to create a mono-repo of AngularJS apps. And with a mono-repo of apps, we needed a build system to build every app. Grunt would help us convert CoffeeScript, SASS, and Haml into JavaScript, CSS, and HTML, respectively. We eventually switched to Gulp for our build system, which lasted us a long time.

But, as you can see from the title of this blog post, we eventually ended up picking Vue over all the other awesome frameworks/libraries out there. Before going into why we picked Vue, I wanted to talk about our steps leading into committing to Vue and adding support for it in our build system. Changing the build system would be required for anything that we would have picked since everything has moved towards modules, bundling and code splitting. We can't get away with the string concatenation of the scripts anymore.

Another convention of the newer frameworks/libraries is using component-based architectures. All of the user interface is built using components. These components are expressed like HTML elements and can be easily reused. Components authors can set specific APIs for their components and allow for more predictable user interfaces. Since we already had a number of applications written in AngularJS, we decided to start by adopting a component-based architecture inside of our current systems. This made developers start planning and architecting their user interfaces with components instead of giant monolithic controllers. As we switched to Vue/React/Angular, we can use that same concept. AngularJS added support for components in version 1.5. This was exciting and refreshing. It really made AngularJS more enjoyable and maintainable. I updated our docs and added support for AngularJS components during our Hack days so our developers can start building components.

While making progress on componentizing our apps, newer frameworks and libraries were becoming more and more popular. Every other day there would be blog posts about React, Vue, or Angular. Internally in our tech department, discussions on what we should use next started coming up. Teams were starting to plan new projects. People felt that it would be a good opportunity to look into alternatives to AngularJS.

As our tech department grew, the difficulty of maintaining our front-end application also grew. With global concatenated scripts, it was becoming really difficult to keep track of things. Since AngularJS automatically injects services into a controller with Dependency Injection, it was often harder to track these services across different modules---the assumption developers made with concatenated scripts was that the injected service was somewhere in the script.js. It also made it more difficult to debug. Our bundles were pretty big. We were shipping a lot of unused code. It was pretty easy to make AngularJS perform poorly. We eventually added ESLint and John Papa's Angular style guide to improve our code, but that was limited to the JS code. It didn't check the templates, and it was hard to enforce it on existing code bases. The linter eventually was pretty noisy and fixes would take additional time. Overall, we were ready to try something else.

For the successor for AngularJS in our stack, our goal was to pick something developers could adopt quickly and wouldn’t have to go through a huge mental shift. Something familiar would definitely win the hearts of more developers. React was ruled out due to JSX and a complete different way of doing things compared to AngularJS. Angular (2+) was opposed due to being completely different than AngularJS and TypeScript. In the end, Vue seemed like a good alternative to AngularJS. It has similar template syntax and a lot of shared concepts (Directives, Filters, and Components). It’s also easier to learn than React or Angular (2+) coming from AngularJS.

The learning curve wasn't the only part of Vue that attracted us. A lot of our developers found the documentation to be extremely helpful. Everything is organized nicely and the code examples are easy to understand. After reading through the docs extensively, picking up and building something with Vue is relatively easy. Different code samples and examples really cover a lot of ground. I personally benefited a lot from the documentation.

People joke around and say, "There is a new JS framework every five minutes." It's a bit exaggerated and partly correct. If you are not actively following JS Twitter, the rapid pace of development of new tools and technologies can be overwhelming. It's hard to tell if a new library will stick. So far the sticking factor has been the community behind a framework. AngularJS and Ember had set a good example.

Fortunately with Vue we didn't have to worry about that part. The vibrant community around Vue gave us a ton of confidence. It's not Yet Another Framework™. It's being used by a lot of people and it is growing pretty fast. As of December 31st, 2018, Vue has been downloaded 23 million times on npm, vs 1.7 million in 2017, Vue Devtools had about 700k users, vs 84k in 2017, and the Vue Github repository has 130k stars, vs 37k in 2017. Evan You, creator of Vue, mentions a lot of these stats during his State of Vue talks.

By this point we realized that Vue was the perfect candidate for us. After winning the hearts of the people in our Tech Department with Vue, there was still a lot of work before we could deploy Vue code to production.

Now the difficult part is fitting Vue into our build system. I started working on this during our Hack days again. Vue CLI v2 uses webpack and includes different things based on the template. All of our front end apps live in a single git repository and Gulp builds all of them with different plugins, so throwing webpack into the mix was tricky. Eventually, I used one of the best features of npm: npm scripts. By default, Vue CLI adds a handful of npm scripts to develop locally, build for production (minify, uglify, tree-shake, etc.), run tests and lint the JavaScript, CSS, and HTML code. I took advantage of this to have Gulp build Vue apps by calling the correct npm scripts. This way I didn't have to integrate webpack with Gulp. This made a lot of things easier and it will allow us to swap frameworks/libraries easily as long as the correct npm scripts are available. Once I had a working example, I presented it to our developers and everyone was excited.

Months after polishing and fixing bugs in the integration with Vue, we shipped our first Vue app to production on March 2018. Right after that, other teams started building their new apps with Vue and the feedback has been awesome. With the help of Vue's documentation, our developers were able to pick up Vue in a pretty short amount of time. Some of our developers took online courses and some just learned it on the fly. Thanks to code splitting from webpack, our bundles are much smaller and we are lazy loading all of our non-critical code. These performance improvements are going to make the user experience of our applications better by loading faster.

So that's it, right? Have we fixed all of our front end problems by adding Vue and webpack? Of course not. Once we have more teams on Vue, I next want to focus on transitioning the initial webpack setup from Vue CLI v2 to Vue CLI v3. This will allow us to drop all the repetitive webpack configs and have simpler apps.

There is always something to improve and upgrade. This is our first blog post, and we have a lot of awesome posts coming up. I hope you follow us to keep up with what we’re up to! Thank you for reading!