Custom post type posts are left in the database if post type is no longer registered ..
Plugins and themes can use custom post types as way to store data.
These posts stay in the forever if they're not cleaned up by the theme itself upon deletion..
There are lot of plugins that clean your database, but I haven't found one that does cleanup of unused post type posts..
This plugin provides easy way to detect and remove posts from post types that are no in use.
The settings page for this plugin is at wp-admin > Tools > Custom Post Type Cleanup..
Note WordPress delete function wp delete post is used instead of running MySQL query to delete the posts.
This way all associated post data are also deleted from the database..
Since version 1.2.0 you can re-register unused custom post types for period of time.
This allows you to inspect and delete the posts like you would normally ...
Read more