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
If you store the data in a blob field then it will be placed in the largeobject store and your table will only contain pointers to the store. If I remember correctly Postgres does not perform access control on these items. If you store the items as Bytea then any items will be stored inline if <2Kbyte else it will go into a TOAST table which is external to you own table, altough you won't see the difference. Advantage of TOAST is no large binaries in your own table and compression is performed to decrease storage size. But as long as you query on an indexed column and the index is used, use explain to find out, then postgres will only read your index file in memory and the specific data your query requested.