1

I would like to design a C# application to store emlployees data, I have around 500 employees. I want to store also pdf scanned profile of each employee. I am planing to use PostgreSQL. Is it practical to store the pdf scanned profiles in the database? Do I need to use blob data-type?

Aan
  • 12,247
  • 36
  • 89
  • 150
  • 3
    Do you really need to store them in the database? It could grow pretty quickly. Could you not store them locally but store the filepaths to them? – Arran Nov 19 '12 at 09:59
  • Off topic but have the employees been notified that they are going to have their information stored in a database rather on paper? – user1378730 Nov 19 '12 at 10:00
  • 1
    http://stackoverflow.com/questions/662488/would-you-store-binary-data-in-database-or-in-file-system – jwaliszko Nov 19 '12 at 10:12
  • Very similar: http://stackoverflow.com/questions/7434530/storing-long-binary-raw-data-strings/7439642 – Erwin Brandstetter Nov 19 '12 at 17:47

2 Answers2

3

Assuming that PDF is not going to be very large (probably less than 5MB I assume) it is ok. You should use type BYTEA for this.

Read more about how to use Npgsql: .NET Postgresql driver (scroll to Working with binary data and bytea datatype)

mvp
  • 111,019
  • 13
  • 122
  • 148
  • but you need to escape/encode binary data before sending to DB then do the reverse after retrieving the data, which is extra buffers to process and buffers to be assigned from C# – Saddam Abu Ghaida Nov 19 '12 at 10:09
  • true but at least you need to encode and decode, every time you want to use this file which could be problematic if you access this frequently on large environment – Saddam Abu Ghaida Nov 19 '12 at 10:12
  • No, driver will do that for you. If size is not big, it will be ok – mvp Nov 19 '12 at 10:15
  • If they're going to be lots bigger (10s or 100s of MB) then using `lo` and large objects, or storage outside the DB, is preferable instead. You imply this but do not state it, so I thought I'd make it explicit. – Craig Ringer Nov 19 '12 at 11:57
2

yes you need to save them as BLOB objects or in bytea or text types, and you need to consider Postgres limitation regarding this. limited 2G's per entry, & 4 Billion per database for blobs and limited to 1G per entry,4 Billion entries per table for bytea or text, but if i were you i will save a reference to this file in the database "where this PDF is located in the local file system" and you stream this file once it is needed

for PostgreSQL limitation check the following link http://wiki.postgresql.org/wiki/BinaryFilesInDB

Saddam Abu Ghaida
  • 6,381
  • 2
  • 22
  • 29