Does the 'quality' of query optimization differ by database provider? I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.

But the example in the blog post seems trivial to optimize - i.e.

SELECT * FROM foo WHERE id = 500000;


vs

WITH cte AS (
  SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;


If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).

And that made me wonder... Would SQL Server be able to optimize such a query better than postgres? 

Are there known differences between databases to the extent/ability they have in optimizing queries?
A question asking if something is "better" or "worse" is subject to personal opinions, so take this with a grain of salt. Asking about the behaviour of the query optimizer is usually extremely broad as the behave differently in complex situations compared to extremely simple queries as the one in your question.

For Postgres a CTE is (as of Postgres 11) a so called "optimization fence". That means Postgres will optimize the CTE independently of the overall query. There are cases where this results in a "better" (=faster) plan, and then there are cases where this will result in a "bad" (=slower) plan (note that recently the discussion around this design decision started again, so maybe this will change in future versions after Postgres 11).

The example in your question is an example where this approach results in a "bad" plan as the condition of the outer query is not pushed into the CTE. 

SQL Server optimizes the whole query including the CTE, so for your example the execution plan in SQL Server would qualify as "better.

But I have also seen the opposite in Postgres: when moving parts of a (non-trivial) query into a CTE the resulting plan was better because operations where not pushed down which resulted in better estimates and a much "better" (=faster) execution plans. As I hardly use SQL Server, I can't tell if there are similar situations there.