IntelMole wrote:titan wrote:After reading that for a bit, I have come to the conclusion that Large Objects are not how I would store the image data. I need to work with the image as a whole, and the manual makes it sound like it will stream the data and break it down into chunks, which in turn each take their own row. The image data will not be that large -- a few megabytes at most -- so bytea really sounds like the way to go now.
10,000 images * a few megabytes (let's say 3) = about 30 GB of data stored. How important is the speed of this database? I'm no expert on database indexing and the theory behind it, but damn that's still a lot of data to sort through to get (typically?) one image out at a time.
PostgreSQL allows me to create an index on a per column basis. I'll have to study this a bit more, but it seems similar to MySQL. In MySQL, however, I was able to specify multiple columns to be a part of the index, but when the query accesses the table it needs to search the columns sequentially.
For example, let's assume a table that has three columns and the first two columns are what make each row unique. An index is created for the first two columns. A query that is structure as:
SELECT * FROM table WHERE column1=1 AND column2=2
or
SELECT * FROM table WHERE column1=1
Will take full advantage of the index. In fact, MySQL automatically marks the primary key for indexing. I'll be able to access the data from the entire row without a problem, and theoretically quicker than if it didn't have an index. Whereas, a query structured as:
SELECT * FROM table WHERE column2=2
or
SELECT * FROM table WHERE column2=2 AND column1=1
will not take full advantage of the index. In fact, it won't take advantage of the index at all.
So, in short, my first column will be the unique ID of each image. Also, the files that will be accessed the most are the preview images. Those will be stored on the disk and not in the database. Again, I'll have to do some studying to make sure that PostgreSQL performs in a similar manner.