4 ms·

For one minute I thought - what a stupid idea, wrong level of abstraction. Now I think I might actually use this in an analysis setting for convenience. I guess

For one minute I thought - what a stupid idea, wrong level of abstraction. Now I think I might actually use this in an analysis setting for convenience. I guess I'll quickly find out what kinds of timeouts I'll run into once I ask for the titles of 10k documents.

andy_ppp 2y ago
I looked at it and initially thought this is a stupid idea to store PDF files in your database and I continued to think this is even worse when it uses files on the file system. If you want this, index your PDF files into a table when they are uploaded. The only functions here all take a filesystem path which your database should definitely not have access to - why would you upload files/store PDFs on a database server!!? These functions to be able to get the title or modification time of a PDF are also just not that useful.
- robertlagrant 2y ago
  > why would you upload files/store PDFs on a database server!!? I don't really understand this question. You can put data where you like. There are no "database servers". These aren't whole toys, take them or leave them. They're made of Lego bricks, and so you can change them.
  - lmz 2y ago
    Yes, but clients shouldn't be encouraged to refer to server paths. It's unnecessary exposure of details and makes it harder to move things around. Also I wonder if this requires extra permissions. I know COPY from server files requires extra permissions: > COPY naming a file or command is only allowed to database superusers or users who are granted one of the roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.
    - robertlagrant 2y ago
      What do you mean by clients in this case?
- claudex 2y ago
  > The only functions here all take a filesystem path From the documentation: > Creating a pdf type, by casting either text path or bytea blob. With the example: SELECT ''::bytea::pdf; So it's convenient to use the path to test quickly, but you can use anything in PostgreSQL which return (or can be convert) a bytea
  - andy_ppp 2y ago
    So potentially there’s a full text search case (?) for doing this but I’d always just default to adding documents like this to elastic search instead. I just cannot see a good use case for this and lots of bad ones.
    - vundercind 2y ago
      It's probably mainly aimed at data pipeline use cases. It's common to have stages of very "messy" data that you'll want to clean up. This lets you keep that part of it all in Postgres, if you like, if your messy data is PDFs, which is a fairly common case. Not every object in a database needs to be ready for public consumption. Some of it's there for processing, for certain use cases.
    - fforflo 2y ago
      The first iteration of this was actually FTS only https://tselai.com/full-text-search-pdf-postgres https://tselai.com/full-text-search-pdf-postgres In the second pass I made `pdf` a type.
- branko_d 2y ago
  It is perfectly legitimate (and in fact preferable in many cases) to store files in database BLOBs. Backups, consistency, security and (yes!) scalability come to mind. Some early BLOB implementations had performance problems, which is where this notion that "you shouldn't store files in BLOBs" seems to come from. But modern DBMSes have fast BLOBs, especially if used properly through their streaming API (don't materialize the entire file in memory!). We have a system in production that stores millions of files in BLOBs, some of them reaching multi-GB sizes, being accessed across the globe (a big enterprise company) by thousands of engineers, and never had performance problems with BLOBs.

andy_ppp 2y ago

I looked at it and initially thought this is a stupid idea to store PDF files in your database and I continued to think this is even worse when it uses files on the file system. If you want this, index your PDF files into a table when they are uploaded. The only functions here all take a filesystem path which your database should definitely not have access to - why would you upload files/store PDFs on a database server!!? These functions to be able to get the title or modification time of a PDF are also just not that useful.

robertlagrant 2y ago
> why would you upload files/store PDFs on a database server!!? I don't really understand this question. You can put data where you like. There are no "database servers". These aren't whole toys, take them or leave them. They're made of Lego bricks, and so you can change them.
- lmz 2y ago
  Yes, but clients shouldn't be encouraged to refer to server paths. It's unnecessary exposure of details and makes it harder to move things around. Also I wonder if this requires extra permissions. I know COPY from server files requires extra permissions: > COPY naming a file or command is only allowed to database superusers or users who are granted one of the roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.
  - robertlagrant 2y ago
    What do you mean by clients in this case?
claudex 2y ago
> The only functions here all take a filesystem path From the documentation: > Creating a pdf type, by casting either text path or bytea blob. With the example: SELECT ''::bytea::pdf; So it's convenient to use the path to test quickly, but you can use anything in PostgreSQL which return (or can be convert) a bytea
- andy_ppp 2y ago
  So potentially there’s a full text search case (?) for doing this but I’d always just default to adding documents like this to elastic search instead. I just cannot see a good use case for this and lots of bad ones.
  - vundercind 2y ago
    It's probably mainly aimed at data pipeline use cases. It's common to have stages of very "messy" data that you'll want to clean up. This lets you keep that part of it all in Postgres, if you like, if your messy data is PDFs, which is a fairly common case. Not every object in a database needs to be ready for public consumption. Some of it's there for processing, for certain use cases.
  - fforflo 2y ago
    The first iteration of this was actually FTS only https://tselai.com/full-text-search-pdf-postgres https://tselai.com/full-text-search-pdf-postgres In the second pass I made `pdf` a type.
branko_d 2y ago
It is perfectly legitimate (and in fact preferable in many cases) to store files in database BLOBs. Backups, consistency, security and (yes!) scalability come to mind. Some early BLOB implementations had performance problems, which is where this notion that "you shouldn't store files in BLOBs" seems to come from. But modern DBMSes have fast BLOBs, especially if used properly through their streaming API (don't materialize the entire file in memory!). We have a system in production that stores millions of files in BLOBs, some of them reaching multi-GB sizes, being accessed across the globe (a big enterprise company) by thousands of engineers, and never had performance problems with BLOBs.