Situation at equilibrium
If you have pure water in contact with air in a closed system (like a capped bottle filled halfway with water), the $\ce{CO2}$ concentration will reach an equilibrium.
$$\ce{CO2(g) <=> CO2(aq)}$$
"(aq)" refers to aqueous concentration, not aquarium.
Currently, our outside air contains about 400 molecules of $\ce{CO2}$ in a million molecules total (400 ppm or 0.04% by volume or by number of particles). At room temperature, the concentration of $\ce{CO2}$ in the water will be about $\pu{13E-6 mol/L}$, corresponding to 0.6 mg of $\ce{CO2}$ per liter.
If you let water stand in pure $\ce{CO2}$ at atmospheric pressure (or bubble pure $\ce{CO2}$ through pure water that is not in contact with air), the concentration of $\ce{CO2}$ at equilibrium will be about 2500-times higher, so $\pu{0.033 mol/L}$. This corresponds to 1400 mg of $\ce{CO2}$ per liter.
Getting "30 ppm"
In the aquarium, the goal is to have 30 mg of $\ce{CO2}$ per liter (confusingly described as 30 ppm, which is different from 30 ppm in the gas phase). This is 50-times higher than the equilibrium concentration you get with air, and about 50-times lower than you get in an atmosphere of pure $\ce{CO2}$.
So what happens in the aquarium is that $\ce{CO2}$ enters the water from the $\ce{CO2}$ bubbles, and exits when water has contact with the air. In a covered aquarium, the rate of $\ce{CO2}$ removal is a bit slower because the air has more $\ce{CO2}$ than fresh air. There are also biological processes (plants, fish, microbes) that produce or use up $\ce{CO2}$. For these reasons, you monitor the $\ce{CO2}$ content and adjust the $\ce{CO2}$ flow (input) to maintain the desired concentration.
The $\ce{CO2}$ concentration is measured with a "drop checker" via the pH of a test solution of defined carbonate hardness (4 dHK, or degrees of carbonate hardness). As the $\ce{CO2}$ concentration in the aquarium changes, the $\ce{CO2}$ partial pressure in the air gap between aquarium water and test solution changes, eventually changing the $\ce{CO2}$ concentration in the test solution, and with it its pH. This takes a while, so the measurement lags by minutes or hours.
Your questions
Update I edited this after Poutnik posted a comment
I'm skeptical of this logic -- I'd think while the bubble is dissolving, water vapor and whatever other dissolved gasses will be diffusing into the bubble. At some point, the bubble becomes just humid air in equilibrium with the surrounding water, and no amount of stirring will get more $\ce{CO2}$ out of it.
If the bubble is pure $\ce{CO2}$, it should dissolve completely given sufficient pure water. However, the aquarium water is not pure water, and some oxygen, nitrogen and water will be transferred from the liquid into the gas bubble while $\ce{CO2}$ dissolves in the water. I would guess that in the vicinity of the $\ce{CO2}$ bubbler, the concentration of $\ce{CO2}$ in the water is already near its maximum, so the $\ce{CO2}$ concentration in the bubble does not go to zero. On the other hand, the partial pressure of oxygen and nitrogen will increase in the bubble until it matches the vapor pressures of the solutes. What the final volume of the bubble will be depends on the kinetics, but if you prevent it from escaping to the surface, it will not disappear completely.
[...] does it mean the aquarist's quest for a "100% efficient" $\ce{CO2}$ bubbler that never lets bubbles pop at the surface is doomed to eventual failure?
In pure water, if the bubble is small and there is plenty of time before it reaches the surface, it should be possible for bubbles to completely dissolve.
And if so, what are the basic principles that describe this equilibrium and how can we figure how much $\ce{CO2}$ can be extracted from a bubble, and how big the bubble will be after it's become just humid air?
It is not an equilibrium situation. If you let the system come to equilibrium, the composition of the bubbles (near the surface, i.e. at ambient pressure) will be the same as the air on the surface. However, as explained in the first section, the system is never at equilibrium if you bubble in $\ce{CO2}$.
If not, why does it seem the bubbles will rapidly dissolve in the water at first, but never fully dissolve even though the solution is far from saturated?
I suspect the solution surrounding the bubble becomes saturated very quickly, and then the $\ce{CO2}$ has the diffuse away from the bubble before more can dissolve. Also, once nitrogen and oxygen have entered, they will maintain the bubble as long the water is saturated with nitrogen and oxygen.