Local government schemes typically have investment vehicles so are funded and invested, this doesn’t mean they are invested wisely but some are and some probably aren’t.
Non-local government are funded out of taxation (individuals contribute about 10% and department/quangos are charged about 20% on average but it doesn’t go anywhere, just into the big pot of government income). Some CS may have some grandparent rights to final salary entitlements , but most current schemes (but probably not the MPs one) promise an enhanced index linked 2% of that years salary for life (which is quite a good rate)
NI is just a form of Government Income, it isnt ring fenced and is just as likely to be spent filling pot holes, as covering pensions and healthcare. Some people seem to think they have earned their Stage Pension through NI contributions, but they haven’t that money has been spent, all they have done is meet the qualifying criteria which can change at any time, to be paid a benefit on a regular basis as the government of the day sees fit.
Just for comparison the private sector typical rate of matching is up-to about 5% so if you pay 5% that’s 10% in total, with a typical pension payment of 6% of investment, so for the average private sector worker pension accrual is around 0.6% per year against 2% per year for the public sector, although investment performance might boost this towards 1% or more if your really lucky, but this isn’t guaranteed like a public pension and certainly not fully indexed linked.