8 ms·

PgPDF: Pdf Type and Functions for Postgres

97 points by fforflo 2y ago

LunaSea 2y ago
Interesting! I wonder what the use case is compared to extracting this information in the programming language and then storing it alongside the PDF in separate table columns?
- cha42 2y ago
  It can be useful for improving ingestion pipeline: put your pdf collection in a temp table and then extract with pure SQL the information you want.
aargh_aargh 2y ago
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.
xrd 2y ago
This is fun. It would be interesting to add the able to query references inside the page, like images. That could be modeled as a foreign key relationship to the page. I'm using some Python libraries to do that and everything is wrapped in try/except blocks because PDFs are a mess. I wonder how poppler handles those kind of files.
- andai 2y ago
  Fascinating, you wouldn't happen to have the code up would you? What kind of stuff are you doing, and what libraries did you use?
  - xrd 2y ago
    Sorry it's not my code to publish. I'm happy to answer general questions over email (on profile page). Pypdf2 and pillow to process images at a high level.
fernandohur 2y ago
The postgres ecosystem keeps impressing me with it's creativity
- deleted 2y ago
  [deleted]
anonu 2y ago
Now create a dbeaver extension to view the type
nathanwallace 2y ago
Readers may also enjoy Steampipe [1], an open source tool to live query 140+ services with SQL (e.g. AWS, GitHub, CSV, Kubernetes, etc). It uses Postgres Foreign Data Wrappers under the hood and supports joins etc with other tables. (Disclaimer - I'm a lead on the project.) 1 - https://github.com/turbot/steampipe https://github.com/turbot/steampipe
- wvh 2y ago
  We've been using Steampipe every once in a while and it works as advertised, though perhaps SQL isn't always the abstraction of choice mentally. Congrats on hitting v1!
  - nathanwallace 2y ago
    thank you! We believe that SQL makes the simple stuff easy and the hard stuff doable :). Hopefully our repos & hubs have good enough examples to get you started most of the time!
- oulipo 2y ago
  Interesting! Can you tell me the typical use-case for this? Is it more for dev-ops stuff? Like to query all your servers to do some dashboards of your infra? Or can that be used also for ML / datascience etc?
  - nathanwallace 2y ago
    Steampipe is most commonly used for DevSecOps. It works with any BI tool for dashboards (it's just Postgres), but is really great when paired with Powerpipe [1] for security benchmarks, visualizing infrastructure and much more [2]. It can definitely be used for a wide range of data scenarios, e.g. there is an OpenAI plugin [3]. 1 - https://github.com/turbot/powerpipe https://github.com/turbot/powerpipe 2 - https://hub.powerpipe.io https://hub.powerpipe.io 2 - https://github.com/turbot/steampipe-plugin-openai https://github.com/turbot/steampipe-plugin-openai
- nlm 2y ago
ape4 2y ago
Slightly related - Are PDFs natively compressed? They would probably compress well since they're often mostly text. Saving space in the database.
- aidos 2y ago
  As a general rule, yes they are compressed. As you point out, they compress really well.
- ZiiS 2y ago
  Yes; but so are any PostgreSQL columns the size of PDF blobs.
ok123456 2y ago
It would be neat to see this as a TOAST type in Postgres, where the PDF was kept in a data structure with the PDF parsed. It would be relatively straightforward to perform searches and index/reindex deep into the documents.
- fforflo 2y ago
  I'm missing something I think. Currently the `pdf` type is indeed a blob of bytes. Read from disk, but not parsed. The type is created with storage=extended to avoid the typical 8KB tuple size limit. In practice though, a PDF for most cases has text-like semantics, so with ::pdf::text you can have all the text-indexes you want.
  - ok123456 2y ago
    This way, you could keep pagination intact and only retrieve individual pages or sections on demand.
branko_d 2y ago
Following the links, I find... pgPDF: The actual PDF parsing is done by poppler. Poppler is a PDF rendering library based on the xpdf-3.0 code base. Xpdf is based on XpdfWidget/Qt™, by Glyph & Cog. XpdfWidget is based on the same proven code used in Glyph & Cog's XpdfViewer library. The XpdfViewer® library / ActiveX control provides a PDF file viewer component for use in Windows applications. Quite the rabbit hole! Any licensing complications? Is it cross-platform? XpdfViewer seems to be propriatary and Windows-only.
- karel-3d 2y ago
  yeah but the xpdf library part of xpdfviewer is open-source (gplv2) https://www.xpdfreader.com/download.html https://www.xpdfreader.com/download.html note that the source code is not on github, but is just dumped each version as a tar https://www.xpdfreader.com/old-versions.html https://www.xpdfreader.com/old-versions.html I once needed to have it for some PDF experiments and I put it on github (this is the newest version; I did _not_ go old versions one by one; I just dumped 2 newest versions) https://github.com/karelbilek/xpdf-mirror https://github.com/karelbilek/xpdf-mirror
- bzzzt 2y ago
  Beside licensing issues I'm not sure the solution should be to link Poppler (which has a multiple CVE's every year on average) into the database server, especially if you process untrusted data. Seems to be a great way to gain access to the database server. Functionally it looks useful, but if those kind of 'helpers' catch on there really should be a way to sandbox these 'parser' processes.
  - sitkack 2y ago
    Totally agree, this data should be supplied by a "page server" (analogous to a frame server in video production) over http using pdf.js so it can run in a browser based sandbox. The risks of running this code are just way too high without an org level security policy about what access this compromised machine would have.
    - 2y ago
skwee357 2y ago
Im having difficulty to understand what’s the use case for this..
- bbor 2y ago
  RAG! AFAICT it doesn’t do OCR off the shelf, but surely you could hook up Nougat/Tesseract/TOCR without too much difficulty and start parsing any PDFs that’s aren’t parsed yet.
- kachapopopow 2y ago
  Instead of having to have 27 columns you have one... I guess? This also makes interop more difficult when working with indexing databases (i.e. elastic). This does minimize the amount of client code required to parse pdf's though.
  - skwee357 2y ago
    But then it means I need to save the PDF in the database rather than some kind of storage
    - kachapopopow 2y ago
      psql is very good at that since 9.0
fforflo 2y ago
Interesting: I posted this a few days back, and certainly not "10 hours ago". Who was kind enough to re-surface this? Thanks :) Some clarifications on a few comments I see downstream: The motivating example was to easily support Full-Text Search (FTS) on PDFs with SQL only (see blog post https://tselai.com/full-text-search-pdf-postgres https://tselai.com/full-text-search-pdf-postgres ). You can treat `pdf` as an alias for `text` and do everything possible. On the next iteration, I made `pdf` a type (typical varlena object of bytes) to avoid hitting disk all the time. The file is loaded from the disk only once (if it's a valid pdf). One can store the `pdf` type (blob of bytes) as a standard Postgres type. And use that for subsequent calls. Postgres will do it's magic as usual. There is a potential next step of storing the parsed document just to save some time from re-parsing the bytes, but I deemed it a premature optimization.
- scrlk 2y ago
  > Interesting: I posted this a few days back, and certainly not "10 hours ago". Who was kind enough to re-surface this? Thanks :) It's HN's second-chance pool: https://news.ycombinator.com/item?id=11662380 https://news.ycombinator.com/item?id=11662380
dennisy 2y ago
Whilst this is cool, why would we want to push this logic into the DB? It seems cleaner to keep this in the service layer and use any PDF parsing library and subsequent schema to store the parsed files.

97 points by fforflo 2y ago

Interesting! I wonder what the use case is compared to extracting this information in the programming language and then storing it alongside the PDF in separate table columns?

cha42 2y ago
It can be useful for improving ingestion pipeline: put your pdf collection in a temp table and then extract with pure SQL the information you want.

aargh_aargh 2y ago

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.

xrd 2y ago

This is fun. It would be interesting to add the able to query references inside the page, like images. That could be modeled as a foreign key relationship to the page. I'm using some Python libraries to do that and everything is wrapped in try/except blocks because PDFs are a mess. I wonder how poppler handles those kind of files.

andai 2y ago
Fascinating, you wouldn't happen to have the code up would you? What kind of stuff are you doing, and what libraries did you use?
- xrd 2y ago
  Sorry it's not my code to publish. I'm happy to answer general questions over email (on profile page). Pypdf2 and pillow to process images at a high level.

fernandohur 2y ago

The postgres ecosystem keeps impressing me with it's creativity

deleted 2y ago
[deleted]

anonu 2y ago

Now create a dbeaver extension to view the type

nathanwallace 2y ago

Readers may also enjoy Steampipe [1], an open source tool to live query 140+ services with SQL (e.g. AWS, GitHub, CSV, Kubernetes, etc). It uses Postgres Foreign Data Wrappers under the hood and supports joins etc with other tables. (Disclaimer - I'm a lead on the project.) 1 - https://github.com/turbot/steampipe https://github.com/turbot/steampipe

wvh 2y ago
We've been using Steampipe every once in a while and it works as advertised, though perhaps SQL isn't always the abstraction of choice mentally. Congrats on hitting v1!
- nathanwallace 2y ago
  thank you! We believe that SQL makes the simple stuff easy and the hard stuff doable :). Hopefully our repos & hubs have good enough examples to get you started most of the time!
oulipo 2y ago
Interesting! Can you tell me the typical use-case for this? Is it more for dev-ops stuff? Like to query all your servers to do some dashboards of your infra? Or can that be used also for ML / datascience etc?
- nathanwallace 2y ago
  Steampipe is most commonly used for DevSecOps. It works with any BI tool for dashboards (it's just Postgres), but is really great when paired with Powerpipe [1] for security benchmarks, visualizing infrastructure and much more [2]. It can definitely be used for a wide range of data scenarios, e.g. there is an OpenAI plugin [3]. 1 - https://github.com/turbot/powerpipe https://github.com/turbot/powerpipe 2 - https://hub.powerpipe.io https://hub.powerpipe.io 2 - https://github.com/turbot/steampipe-plugin-openai https://github.com/turbot/steampipe-plugin-openai
nlm 2y ago

ape4 2y ago

Slightly related - Are PDFs natively compressed? They would probably compress well since they're often mostly text. Saving space in the database.

aidos 2y ago
As a general rule, yes they are compressed. As you point out, they compress really well.
ZiiS 2y ago
Yes; but so are any PostgreSQL columns the size of PDF blobs.

ok123456 2y ago

It would be neat to see this as a TOAST type in Postgres, where the PDF was kept in a data structure with the PDF parsed. It would be relatively straightforward to perform searches and index/reindex deep into the documents.

fforflo 2y ago
I'm missing something I think. Currently the `pdf` type is indeed a blob of bytes. Read from disk, but not parsed. The type is created with storage=extended to avoid the typical 8KB tuple size limit. In practice though, a PDF for most cases has text-like semantics, so with ::pdf::text you can have all the text-indexes you want.
- ok123456 2y ago
  This way, you could keep pagination intact and only retrieve individual pages or sections on demand.

branko_d 2y ago

Following the links, I find... pgPDF: The actual PDF parsing is done by poppler. Poppler is a PDF rendering library based on the xpdf-3.0 code base. Xpdf is based on XpdfWidget/Qt™, by Glyph & Cog. XpdfWidget is based on the same proven code used in Glyph & Cog's XpdfViewer library. The XpdfViewer® library / ActiveX control provides a PDF file viewer component for use in Windows applications. Quite the rabbit hole! Any licensing complications? Is it cross-platform? XpdfViewer seems to be propriatary and Windows-only.

karel-3d 2y ago
yeah but the xpdf library part of xpdfviewer is open-source (gplv2) https://www.xpdfreader.com/download.html https://www.xpdfreader.com/download.html note that the source code is not on github, but is just dumped each version as a tar https://www.xpdfreader.com/old-versions.html https://www.xpdfreader.com/old-versions.html I once needed to have it for some PDF experiments and I put it on github (this is the newest version; I did _not_ go old versions one by one; I just dumped 2 newest versions) https://github.com/karelbilek/xpdf-mirror https://github.com/karelbilek/xpdf-mirror
bzzzt 2y ago
Beside licensing issues I'm not sure the solution should be to link Poppler (which has a multiple CVE's every year on average) into the database server, especially if you process untrusted data. Seems to be a great way to gain access to the database server. Functionally it looks useful, but if those kind of 'helpers' catch on there really should be a way to sandbox these 'parser' processes.
- sitkack 2y ago
  Totally agree, this data should be supplied by a "page server" (analogous to a frame server in video production) over http using pdf.js so it can run in a browser based sandbox. The risks of running this code are just way too high without an org level security policy about what access this compromised machine would have.
  - 2y ago

skwee357 2y ago

Im having difficulty to understand what’s the use case for this..

bbor 2y ago
RAG! AFAICT it doesn’t do OCR off the shelf, but surely you could hook up Nougat/Tesseract/TOCR without too much difficulty and start parsing any PDFs that’s aren’t parsed yet.
kachapopopow 2y ago
Instead of having to have 27 columns you have one... I guess? This also makes interop more difficult when working with indexing databases (i.e. elastic). This does minimize the amount of client code required to parse pdf's though.
- skwee357 2y ago
  But then it means I need to save the PDF in the database rather than some kind of storage
  - kachapopopow 2y ago
    psql is very good at that since 9.0

fforflo 2y ago

Interesting: I posted this a few days back, and certainly not "10 hours ago". Who was kind enough to re-surface this? Thanks :) Some clarifications on a few comments I see downstream: The motivating example was to easily support Full-Text Search (FTS) on PDFs with SQL only (see blog post https://tselai.com/full-text-search-pdf-postgres https://tselai.com/full-text-search-pdf-postgres ). You can treat `pdf` as an alias for `text` and do everything possible. On the next iteration, I made `pdf` a type (typical varlena object of bytes) to avoid hitting disk all the time. The file is loaded from the disk only once (if it's a valid pdf). One can store the `pdf` type (blob of bytes) as a standard Postgres type. And use that for subsequent calls. Postgres will do it's magic as usual. There is a potential next step of storing the parsed document just to save some time from re-parsing the bytes, but I deemed it a premature optimization.

scrlk 2y ago
> Interesting: I posted this a few days back, and certainly not "10 hours ago". Who was kind enough to re-surface this? Thanks :) It's HN's second-chance pool: https://news.ycombinator.com/item?id=11662380 https://news.ycombinator.com/item?id=11662380

dennisy 2y ago

Whilst this is cool, why would we want to push this logic into the DB? It seems cleaner to keep this in the service layer and use any PDF parsing library and subsequent schema to store the parsed files.