I want to store a serialized object somewhere and after looking around, realize there are a couple of ways to do this.
I'm looking at storing the file somewhere in the file system and storing the file location as entries in a table.
Assuming the files are around 100-500Kb, is this a suitable way of doing so? Or using bytea or blob a better solution?
Small objects (which 500Kb) qualifies as will work fine as bytea. The main limitation on the size is that you will need to materialize the whole field in memory first at the server and then at the client (there are ways aorund it, but they are limited). For half a megabyte, that shouldn't be an issue, but if you start storing much larger objects it may need some consideration.
Storing it in the filesystem and storing the location (or storing it named by a surrogate primary key from a SERIAL sequence) is a good way to deal with larger files, and you can of course apply it here as well. The downsides are that you loose transactional integrity (you can't be sure that both the filesystem and the database was updated, so you need to implement some kind of verification tool to run regularly to compare these), and probably more importantly you can't get consistent backups (what if the file was backed up and not the db record? or the other way around) There are methods to deal with this as well, of course, but it rapidly becomes much more complex than keeping the data stored in the table.
