With a lot of things in security, it depends on your threat model. Who are you defending yourself against?
Normalfags - yes, deleting posts is effective. Long-term retention (archival, search engine caches, local download, screenshots/screen recording etc) is a foreign concept to most. In my experience opening up a laptop or unlocking a phone is like driving in the rain: there is an immediate 30 IQ dropoff as a result. 90% of people fall into this category and rely on the other 10% to get their information on the Internet. Depending on who you're talking about, journalists may also fall into this category.
Kiwi Farmers - deleting posts is possibly effective. "ARCHIVE EVERYTHING" is a common refrain and some will do so proactively. Once it's bubbled up through this channel and made its way out to the 90%, it doesn't matter if the original post was deleted or not. This of course depends on how interesting the deleter or his content is: if a cow has DFE'd faster than a farmer can archive, then it's likely lost to the public internet, barring more specialized cases.
The site itself/its jannies - deleting posts is probably not effective. It's become common practice for companies that can afford to maintain mega-sized databases to never truly destroy data, only to flag it as "deleted", with the data itself intact and no longer presented to anyone but the jannies, who can read it or restore it at any time. Even revising the post by way of blanking it does not prevent this - with modern technology all revisions are stored and restoring any of them is trivial.
Here at KF there have been multiple instances where a janny will provide a public service and restore a cow's post because it was funny. While unlikely, this measure could be taken by large sites like Facebook or Twitter where the data could be retrieved by said jannies at the behest of law enforcement, or perhaps for personal vendetta purposes. In the latter case the data could perhaps be laundered through a friendly journalist, or just leaked anonymously, again if the data or the deleter is of interest. Regardless, the "deleted" data is still there.
The State - deleting posts is not effective. Everything you post is hoovered up and stored for what amounts to forever, be it through a backdoor like the PRISM program or snooping directly on the wire. If it is encrypted, you can bet it will be decrypted eventually. This is why it's important to obfuscate the source of the information through technology like I2P or Tor and to not overshare. This is ostensibly protected by the Fourth Amendment, where hoovering this data is illegal and thus cannot be used in a court of law, but parallel construction is always an option for the motivated government agent.