What you get out will depend on what type of scheme - the public sector now often offers different types, but broadly I think:
- You put money in, your employer puts in some too. What you get paid on retirement will depend on a calculation like a % of your income (and these days instead of it being a % of your final salary, they will average out your income over your career or similar and take the % from that).
- The other type of pension is where you are putting money in, so does your employer, and all this money is invested into stocks and shares, so will grow (but also be subject to rise and fall of stock market over the decades). When you come to take your pension the value will depend on the stock market at the time, but then you either have a lump sum, or you use the money to buy an annuity which pays you a regular income until you die, or you do a combination of lump sum + smaller annuity.
As many people will have much reduced expenses (no commute, no mortgage repayments, no children at home), you can sustain your standard of life in retirement on a much smaller annual budget. Financial Advisors and other sources on the web will tell you what is equivalent to your current salary (this could be completely wrong but, imagine the quality of life on a 50k salary today could be achieved on a 25k annual income in retirement, assuming you're no longer paying mortgage etc). This is the typical private sector pension, but is now offered in the civil service etc too.
You will obviously get state pension, but it is not enough to live on, and many people who have only that option have to carry on working part-time to top up their state pension. Therefore your options are:
A. Take only state pension, topped up with pension tax credit --> struggle for money and quality of life in retirement, or
B. Take state pension + whatever is in the pension scheme you've signed up to --> slightly or much better quality of life, depending on its value. Even some private pension is going to give you a better quality of life than no private pension at all!
Even if you don't stay in the public sector for long, you would still have a pension pot from there, which you add to your state pension pot, and any other pots from other jobs that you do in the next 25 years, and it's the combined total that gives you quality of life/total pension. These days most people who work in the private sector end up with multiple pots from different jobs, and you can work with a financial advisor to combine them if that makes sense.
If you think you will need more money in retirement and can afford to, you can save money into a private pension in addition to the money you put into the workplace pension. The difference with the entirely private one is that the only money going in is from you, but it should still grow over the next 25 years as it will be invested in the stock market. If you have a DH/DW who is a higher earner then you might be able to use some of their income to add to your pension pot too, though you'd want to take advice from a FA about that as you'd want to use the tax allowances to full effect.
Unfortunately at 40 years old you are too old to start a LISA (lifetime ISA) but for anyone 39 and under reading this, a LISA is another good way to increase your pension pot: You put money in (up to a limit annually) and the government tops it up by 25%. It then gets invested in the stockmarket and grows over time. You can have a LISA in addition to other pensions.