Reply All: The Crime Machine, Part I

New York City cops are in a fight against their own police department. They say it’s under the control of a broken computer system that punishes cops who refuse to engage in racist, corrupt policing. The story of their fight, and the story of the grouchy idealist who originally built the machine they’re fighting.

Click to listen

 

#128 The Crime Machine, Part II

Click to listen

 

 

 

 

WooCommerce Custom Product Tables Beta

Today, we’re pleased to announce the beta of our Custom Products Tables plugin that’s designed to improve the performance of WooCommerce stores by adding database tables that are designed for eCommerce.

This builds on the investment we made in 3.0 which introduced Data Stores and CRUD functionality that provides a uniform way of accessing a store’s data regardless of where it is saved.

Why products first? Simply because it’s one of the most complex Data Stores to change, and one which can positively impact stores of all sizes.

The plugin replaces the current WooCommerce product Data Store with one that makes use of brand new, normalised, dedicated product tables.

Performance Improvements

During our tests we set up two identical stores on the same hosting provider with the same theme and WooCommerce settings, the only difference being one was running the Custom Products Table Feature Plugin.

To test the new product tables we needed to easily create a lot of store data – specifically products, orders, and customers. To do that, we created the WooCommerce Smooth Generator – an easy to use development plugin which allows you to generate WooCommerce data to test the scalibilty of any of your development.

For the tests, we created one data set of 500 products and imported that into two identical stores with Storefront as the theme and no other plugins – one store with the normal WooCommerce data structures and the other with our new feature plugin.

Both stores also had 70,000 orders in the database and meta data in the range of 1.4 million rows.

Average Load times

The results, so far, have been great – with improvements of up to 30% on page load times! Checkout, arguably the most important part of the store experience, has seen the biggest performance gains.

Installing the plugin

To install the plugin you will need :

During the beta period we’ve limited access to the plugin’s functionality so that we can gather feedback from our developer community.

We do not recommend that you install and use this on a live store.

Data Migration

There are two WP-CLI commands available at present to migrate your data:
* wp wc-product-tables migrate-data [--clean-old-data] – This command will migrate data from the current WordPress tables to the new tables, passing the –clean-old-data flag will result in the old data being removed from the WordPress tables. Please be cautious when using this flag as it can result in you loosing your product data if not backed up and you need to revert.
* wp wc-product-tables recreate-tables – This command will drop all the new product tables, removing all data within them and then create new clean tables. This command is useful for testing the migration process from fresh again should you wish. Again, please be cautious as if you ran the migrate-data command with the –clean-old-data flag, running this will result in all your data being lost, so always keep a backup on hand of your products.

Do note that any new products created after you have enabled the plugin will be created in the new tables and will not be available if you disable the plugin again.

What tables are added

  • wc_products – Contains product data eliminating the majority of meta data table storage. A few fields in the meta data table not accessed often, and all extension specific data, will stay in meta data.
  • wc_product_attributes – Contains product attributes and their settings.
  • wc_product_attribute_values – Contains product attribute values and their settings.
  • wc_product_downloads – Contains product downloads and their settings.
  • wc_product_relationships – Contains all product relationships to other products or objects within the WooCommerce ecosystem, i.e. other products, images etc.
  • wc_product_variation_attribute_values – Contains variation data for products.

These tables currently cover all aspects of products, including product settings, attributes, variations, downloads, as well as other relationships such as up-sells, cross-sells, grouped products, and images.

Compatibility with extensions and themes

We’ve kept backwards compatibility in mind by adding specific functionality that checks meta data calls and then intervenes to ensure the data gets routed to and from the correct tables.

Whilst we have taken a care in ensuring developers that are not using CRUD can get/set data in the correct tables, we do advise developers with plugins and themes that make direct meta calls to avoid that and rather use the getter and setter methods provided by the Data Stores since version 3.0. We’ve published documentation here to help you move to the CRUD system.

Release Schedule

Our next milestone is to get this plugin released on WordPress.org so that any store can benefit from the performance improvements. To get there, we need your help putting the beta through its paces so we can catch all issues and better understand the performance improvements and problems you’re seeing in the wild.

Once you’ve installed the beta, please open an issue for any problems you encounter on the GitHub repository. And, let us know in the comments what performance improvements you’re seeing – we’d love to hear from you!

We plan to include the new product tables in a major version update early next year.

Add “Faux” Predicates: Postgres Optimization

Made by Developers and Non-Developers

The query fetches sales that were modified before 2019. There is no index on this field, so the optimizer generates an execution plan to scan the entire table.

Let’s say you have another field in this table with the time the sale was created. Since it’s not possible for a sale to be modified before it was created, adding a similar condition on the created field won’t change the result of the query. However, the optimizer might use this information to generate a better execution plan:

db=# (
  SELECT *
  FROM sale
  WHERE modified < '2019-01-01 asia/tel_aviv'
  AND   created < '2019-01-01 asia/tel_aviv';
);
                                           QUERY PLAN
--------------------------------------------------------------------------------------------------
Index Scan using sale_created_ix on sale (cost=0.44..4.52 rows=1 width=276)
  Index Cond: (created < '2019-01-01 00:00:00+02'::timestamp with time zone)
  Filter: (modified < '2019-01-01 00:00:00+02'::timestamp with time zone)

After we added the “Faux Predicate” the optimizer decided to use the index on the created field, and the query got much faster! Note that the previous predicate on the modified field is still being evaluated, but it’s now being applied on much fewer rows.

A “Faux Predicate” should not change the result of the query. It should only be used to provide more information to the optimizer that can improve the query performance. Keep in mind that the database has to evaluate all the predicates, so adding too many might make a query slower.