hi i have an argument with my boss, he said that the process of sql is that it loads all its resources/data to the memory before the query kicks??which i contracdict as it only loads the queried statement
so for example i have an application that will let people upload images to the database,then the images would be stored to a table that have ac olumn for the actual image as image_full, then the 2nd column is the thumb picture the smaller version of the original image called image_thumb.
he wants me to separate the two column(image_full & image_thumb) to two different tables and i'll just reference them to as foreign. as this will improve the speed and efficiency of the database when it would be use by many people.
as what he have said because these columns would still be loaded to the process/memory even the i have this only query ex. (select image_thumb from table) thus it will also load image_full, which i think do not.
.
Can any one explain me , is it really appropriate to separate the two columns to different table?are there any articles that would explain this?thanks and im using POSTGRES

I would of thought that storing the images in the user record just increases the amount of storage required for each record. The database cache is a fixed size in most RDBMS so having images in records just reduces the number of records that can be stored in the cache i.e. it's slower and user reporting will be much slower.

Storing images in the database is usually slower than storing them in the file system. If you just store the file name in the user record and then get the html to point to that image file. I suppose you could also store the user images as a standard file named after the user id i.e. 12345.gif which would mean you wouldn't even need to pull this data at all.

You do need to be more careful about cleaning up old images that are no longer required when you store images in a file. If you want information more pertinent to POSTGRES then you should post in the POSTGRES forum.

I don't think your boss is correct. Unless you do a "SELECT * FROM..." only the columns specified in the select list will be returned to the client. You can demonstrate it to your boss by doing an EXPLAIN of the query (with the VERBOSE option).

As for the server memory usage, in recent versions of PostgreSQL long data are stored separately from the other data types (see TOAST in the Postgres documentation) and will not be accessed on disk at all, unless referenced in the query, so you should be OK from that point of view as well.

There are some disadvantages of storing images in the file system as well. First of all, file system is outside of the database transaction control, so you have to deal with the ACIDity of files somehow. Also, there is an issue of data integrity at a higher level; for example, nothing prevents you from deleting an image file from the file system and the database will never know about it.