Is it necessary to auto delete my WP plugin database tables when users deactivate/delete my plugin?

In my opinion, a best practice would be to clean everything on uninstallation. It’s also mentioned as a best practice in the docs:

When your plugin is uninstalled, you’ll want to clear out any plugin options and/or settings specific to to the plugin, and/or other database entities such as tables.

However, if you’re struggling to decide what’s best for your plugin, why not let the user decide? You could always ask them whether they want to keep their data just after they have clicked the uninstall button. Take a look at this similar question for details.