FR: Query Rewriting using regular expressions

[lizztheblizz]

Slack Context link: https://planetscale.slack.com/archives/C02JPHAQHB7/p1701190291867839

This request is heavily inspired by features available in ProxySQL, which we use as a stop gap at times in Tech Solutions and Customer Engineering to work around various compatibility challenges for customers with limited ability/capacity to implement changes.

Many customers and prospects coming to us with legacy environments experience some or all of the below challenges:

• Application code can no longer be changed so queries can't be rewritten
• Team evaluating database choices is not the team with direct access to code
• Application code changes will only be considered when a POV has successfully been completed
• Prior reliance on MySQL features like lowercase_table_names, which break compatibility with Vitess (i.e. case insensitivity expectation on table names and table aliases)
• Queries use reserved keywords that were alright to use in MySQL 5.6/5.7, but have been blocked in MySQL 8
• Queries can't be individually adapted to use Boost (or specific query comments needed to help analyze a performance problem)
• Heavy analytical queries can't easily be sent over a separate connection to make use of replicas.

If we had a way to rewrite queries on the fly, based on individualized sets of rules per customer, we could overcome many of these challenges, and reduce the friction to initial adoption.

Core implementation goals might be:

• Toggling rewrite rules on/off altogether as a feature without deleting underlying configuration. 99% of users likely won't need this.
• Configuration store to enter individual rules, probably needing at least the following properties:
  • active (to individually toggle rules on or off)
  • priority (to decide the order in which rules are applied)
  • username (to filter incoming queries)
  • keyspace/database (to filter incoming queries)
  • match_digest (pattern applied against full query to see if the rule should be applied)
  • match_pattern (finding specifically the part of the query to be rewritten)
  • replace_pattern (preferably implementing regex transformations and ability to insert previously isolated groups, etc)
  • regex modifiers
  • target tablet type (would be great for rudimentary read/write splitting)

As discussed on Slack, we currently use ProxySQL to help offer a lot of this, and having this functionality built into pscale would offer a solution analogous to that without needing to resort to 3rd party tooling. However, if this were built straight into Edge, it would offer us the exact same benefits without requiring the customer to do anything.

[mattrobenolt]

@lizztheblizz how do you see this being configured, starting with the context of a consumer of pscale connect?

I feel like fully supporting syntax like ProxySQL with it's in-memory database is quite over the top, but being able to support some CLI arguments or runtime config would be more ideal in our situation.

One option I was considering, we could expose some HTTP/RPC interface to add/remove/enable/disable things at runtime. But I kinda feel like we can back this by a config file instead to just add in the rules.

I think having static config in this case can be quite a bit more flexible, or even being able to reload the config through a SIGHUP or similar would be better than some in-memory and SQL interface.

What are your thoughts?