What happens if the remaining balance on your Oyster card is insufficient for the route you took?

Short answer:
You will be allowed to exit. Your Oyster card balance will become negative and you will need to top up before being able to use it again.

Strictly speaking, customers are supposed to ensure they have a sufficient balance on their card for their entire journey. It is hard to enforce, but in some specific scenario you can get a penalty fare. There are several mechanisms (including the £7 fee on new Oyster cards and the minimum balance required for entry) to mitigate abuse anyway.

Long answer:

What happens if you use your Oyster card to get into the tube system and then, when you’re trying to exit, find that you don’t have enough funds on your card to exit?

According to a 2012 TfL FOI response, You will be allowed to exit as long as you are within the maximum journey time limit:

So long as customers have successfully touched in, and thus achieved a
valid entry, they will always be allowed to exit a ticket barrier within
the permitted journey time from the entry, regardless of card balance at
the time of exit.

There are anecdotal experiences that this does not apply in a few non-London Overground, non-Elizabeth line National Rail stations, i.e., the automatic gate barriers will not open and you need to talk to a human at the gateline to be let out.

Your Oyster card will then have a negative balance. For the card to be useful again, you need to top it up such that it has sufficient funds for your next journey. For the Tube, DLR, London Overground, Elizabeth line, and National Rail services, it is the minimum fare from your origin station (see TfL Conditions of Carriage):

[3.5 …] Your card will be accepted if your pay as you go balance is at least the minimum fare from the station.

For buses, you just need a non-negative balance.

[(TfL CoC) 3.6 …] If travelling by bus and the balance on your Oyster card is less than the pay as you go fare for your journey but is not less than £0.00, you will be allowed to make one more journey. You must then top up your Oyster card to clear the negative balance before you can use it again.

The ‘one more journey’ feature always refers to bus journeys in TfL communications. Thus, it is likely that TfL requires your card to hold at least a single fare for trams.

There are some quirks on this domain. First off, there are special provisions for when your Oyster balance becomes negative during your bus and tram hopper fare period:

[(TfL CoC) 3.6 …] On buses and trams, you can make multiple bus or tram pay as you go journeys for the price of one journey within one hour of first touching in using the same card or device. This will not happen if: […]

• Your Oyster card has a negative pay as you go balance after the first journey. If you clear the negative balance within one hour of touching in, you can still make subsequent journeys for free within the hour

Another quirk is that having a season ticket does not bypass the non-negative balance requirement:

[(TfL CoC) 3.3.1 …] You can use the season ticket on your Oyster card or smartcard provided it is valid at the time you travel and any pay as you go balance is not in debit. If you have a negative balance, you must add credit to clear it before you can use it again.

Now technically speaking, one is not supposed to abuse the minimum balance feature and intentionally leave their Oyster card in negative balance:

[(TfL CoC) 3.5 …] Before travelling, you must have sufficient pay as you go credit for the whole of your journey or Auto top-up set on your Oyster card.

Having that said, if one has validated their Oyster card at the start of a TfL-only journey, the penalty fare described in Clause 7.3 will not apply. All TfL can do is via the catch-all clause:

[(TfL CoC) 7.4 …] If we believe that you have used or tried to use any ticket, Oyster card, Oyster photocard or smartcard to defraud us we may cancel and not re-issue it. If this happens, we will not give you or the rightful holder a refund of the remaining value of the ticket, or refund any credit or deposit paid for the Oyster card.

Things get a bit messy if a National Rail leg (excluding London Overground and the Elizabeth line) is involved. According to Oyster Conditions of Use on National Rail services, one is technically in the clear if they use pay as you go all the way. On the other hand, they will be penalised if they are found having an Oyster with a Travelcard loaded into it, travelling outside the zones/stations covered by the Travelcard, and having insufficient pay as you go credit to cover the out-of-zone journey:

3.20 If you are using an Oyster card with a Travelcard season ticket on it and you are travelling on a
Train Company service to a station beyond the availability of your Travelcard season ticket,
but within the London National Rail Pay As You Go Area, you must touch in before you start
your journey and have sufficient pay as you go credit on your card to cover the cost of the
additional travel. If you fail to touch in at the start of your journey, or have insufficient pay as
you go credit, you may be issued with a penalty fare or be prosecuted.

7.1 Penalty fares apply on the National Rail network in accordance with the National Rail
Conditions of Carriage.
If you are within a Compulsory Ticket Area or travelling on National Rail services within the
London National Rail Pay As You Go Area without: […]

• a validated Oyster card, when you are paying as you go, showing a record of the start of
  your journey
• a validated Oyster card showing a record of the start of your journey and sufficient pay as
  you go credit, when using a combination of a Travelcard and pay as you go credit, to make
  a journey beyond the zonal validity of the Travelcard season ticket held on your Oyster
  card

you may be issued with a penalty fare or you may be prosecuted.

The deposit (for cards issued before 23 Feb 2020), £5 fee (issued before 3 Sept 2022), and £7 fee (issued after 4 Sept 2022) are meant to mitigate such abuse. For older cards (issued before 3 Sept 2022), a negative balance will prevent one from getting the deposit / fee as a refund:

[(TfL CoC) 3.2.4 …] If you paid a deposit when you got your Oyster card, we will refund this if you get a refund on your original card at a station ticket machine or return the original card to us.

If you got your Oyster card before 3 September 2022 and paid a fee, we will automatically refund this as pay as you go credit after a year if you keep using your card. […]

If your Oyster card has a negative pay as you go balance, you will need to clear it before a refund can be paid.

For newer cards (issued after 4 Sept 2022), the fee is non-refundable so TfL has £7 from the customer anyway.

I guess in theory this conundrum is an advantage of using a contactless credit card to pay instead of a pre-loaded Oyster card, but despite that, I still see loading up or getting new Oyster cards at the machines quite frequently.

As discussed in the first part of the answer, it is not really a conundrum. You are always free to leave the system (provided you use the transport system like normal travellers do). Moreover, there are still many reasons to get/use an Oyster card, to name a few:

• Below-18s who use Zip Oyster photocards
• People who want their Railcard discounts on off-peak PAYG journeys
• Season ticket holders
• Visitors whose cards impose high overseas transaction fees
• Those who are on the hunt for special edition cards

TFL answered a freedom of information request in 2012 when they where asked:

Please explain, if users are allowed to exit stations (including
national rail) with ticket barriers, when their card balance turns
negative as a result of swiping out at the ticket barriers.

And responded:

So long as customers have successfully touched in, and thus achieved a
valid entry, they will always be allowed to exit a ticket barrier within
the permitted journey time from the entry, regardless of card balance at
the time of exit.

However exit ticket gates will reject any card which is in negative
balance and does not have a preceding entry within maximum journey time
(which may arise from failure to touch in or from exceeding maximum
journey time for the journey attempted or due to an extraneous intervening
validation such as on a bus).

So as long as you did touch in then you will always be allowed to leave. However, if you manage to enter without touching in then ticket barriers will not open if the card balance is negative. You can check the maximum permitted journey times – it depends on the length of the journey and time of day.

TFL also answered another request in 2019 where their response included:

A card with a negative balance may only be reused once it is topped up.