iMessage doesn't keep your whole message history on-device. You can scroll back and it will fetch a bit more each time, but that's it.
Is there anyway to pull back to the beginning of time into the local database? I'd like to retrieve some specific old messages from a friend who has passed away. If I could get them out I could search (I know what I am looking for) but it would involve an unfeasible amount of "pull to fetch more"
This is confidently incorrect.
The entire database is stored on device in a SQLite database. It absolutely has a terrible UI for fetching messages, but they’re all there.
If OP wants to find this file, a good search term is "3d0d7e5fb2ce288813306e4d4636395e047a3d28" which apparently is a sum of the string "HomeDomain-Library/SMS/sms.db".
One thing I miss from Android was the simplicity of getting data in and out of the phone. I could just dump everything to CSV and be done with it.
Messages seems to be somewhat random about keeping/removing attachments from your devices regardless of how much storage you have free or whether you have 'Optimise Storage' off.
*note this is specifically when using Messages with iCloud
Unless you’ve changed the “message history” setting, yes, it does keep all history on-device. It’s just incredibly frustrating to access any of it because Messages.app is awful at history.