We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA. In various pages, I need to check if a book's file exists in the database. But I have noticed that the pages load slowly, so I was wondering if it is being caused by the manner in which I am checking.
Currently, I am checking if "book_file IS NOT NULL". Does PostgreSQL read the whole file, in order to determine whether it is not null, which could be slowing things down? If so, is there another way to check if the file exists, without having to read the whole file?
I read up on some binary string functions on the PostgreSQL documentation and was wondering if functions like octet_length(string), substring(string [from int] [for int]) or get_bit(string, offset) might perform better?
Below is an example of a simplified query I am using to check for the existence of the file.
WHERE books.book_file_pdf IS NOT NULL
ORDER BY books.book_id DESC
Does this kind of condition slow things down? If so, can this query be optimized, to check solely if the file column is not empty, without reading the file?