What happens to URL alias when a node is unpublished?
Nothing happens and nothing should.

An alias only exists on the URL level. It simply aliases "/blog/2018-01-23/my-awesome-article" to "/node/27", does a lookup for what when a request is coming in and that's it. After that, the routing and entity (access) systems are taking over to figure out what to display and who is allowed to see it.

If you unpublished node 27, then it still exists and admins can still access it, using that alias. In theory, it wouldn't even care if node 27 is deleted, it would still de-alias it to "/node/27" and then the routing system would return a 404 page as there is no node 27 anymore. This actually doesn't happen with nodes because there is code that listens on nodes being deleted and deletes aliases pointing to them as well.

It also doesn't matter if you use a manual alias or generated one using pathauto, that only differs while saving the node and telling pathauto if it should generate one dynamically or not.

But that's actually not your question, because what you're really asking is how  google updates its index when you unpublish content. And what you describe is exactly what should happen, eventually google will re-index your content, see that it no longer exists and removes it from its index. There isn't really anything else you can do directly. You could use a sitemap.xml to tell Google index your content more or less frequently but that's about it.