People who build products have a wide array of ways that they can build a cancellation flow.
Simply having a button that deletes your account when it's clicked on is pretty rare. At the bare minimum, you usually see something like Github's "type in the name of your repository if you really want to delete it."
At the other end of the spectrum, there's the "call us to cancel" school of thought ordinarily reserved for high-touch SaaS products (and the New York Times).
That final call is generally supposed to give a retention team an opportunity to talk you out of your terrible decision, give the support/product teams insight into why people are cancelling, and throw some last-ditch coupon offers at you.
There's probably no good rule of thumb for what kinds of cancellation experiences are best. Everything depends on expectations. If I sign up for a $1,000/mo. SaaS product and you let me cancel by just hitting a button, I'm going to be pretty surprised. I'm not necessarily going to be more likely to recommend the product.
If I sign up for a $30/mo. SaaS product and you force me to send you an email, wait for a response, and reply to that email with a "Yes, please cancel" after being offered some kind of discount or opportunity to speak to the support team, then you're going down a dangerous road as far as what people are going to say about your product. Witness: one of the first Google results for "cancel instapage" is this Twitter thread.
Great customer service isn't about always doing a particular kind of cancellation experience, though. There is a time and a place for what Instapage does. The reason why it elicits such derision is the mismatch of expectations.
When you let me instantly sign up for an account with your SaaS product using my Facebook account, offer me a cheap introductory price, and then don't let me cancel without Googling for a solution and then sending an email to your support team, I feel like I've been lured into a roach motel. Easy to enter; hard to escape.