5 ms·

I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. I know, I know, "databases are bad for file

I know this sounds icky to some, but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. I know, I know, "databases are bad for files" - but let's take something like an ECM suite where images and documents are literally part of a transaction, having to synchronize those between filesystem and database breaks the Atomic constraint in so many ways. PostgreSQL has LOB support, but oid's being 32-bits severely limits the usefulness of the feature without using convoluted workarounds (multiple databases).

anarazel 9y ago
Postgres dev here. > but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. What sizes of files and such are you interested in? What kind of read/write patterns? I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches. It'd be cool if you could comment on the pgsql-hackers list.
- snuxoll 9y ago
  Small files, 25-50K on average (images of patient charts), and they're all they're all write-once read-many.
  - anarazel 9y ago
    Hm, in that case, what stops you from just using a bytea column?
    - snuxoll 9y ago
      My major issue with bytea is two fold, the same oid issue that you have with LOB's (we have 10's of billions of pages) so you're forced to less than optimal solutions like table inheritance for large numbers of records, plus it severely bloats the size of the heap file making a VACCUM FULL take a century if needed.
      - derefr 9y ago
        > so you're forced to less than optimal solutions like table inheritance for large numbers of records Would the "native table partitioning" feature introduced in this release be a solution (or, at least, more optimal) for this? Alternately, if you want to go off-heap, what about using Foreign Data Wrappers (https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_Wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_...)? These two both sound like they might solve your problem: • https://github.com/ZhengYang/dc_fdw/wiki https://github.com/ZhengYang/dc_fdw/wiki • http://multicorn.org/ http://multicorn.org/
        snuxoll 9y ago
        The native table partitioning removes one of the headaches of inheritance, but it mostly only relieves some of the administrative headaches - the foreign key issues remain. fdw's are great, but they don't participate in replication - which is a pretty big issue. Unfortunately, the "proper" way to do this would be with a custom type, but I can't find any way you could write one with the extension API that wouldn't be stored in the database heap (you could create some native functions similar to the existing LOB functions, but then you run into atomicity issues again). Honestly, this would be easy to write if the extension API supported off-heap storage of custom types - though I understand there's a ton of technical complexity in implementing such a feature.
        derefr 9y ago
        To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap? As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL log and so forth. Just the space overhead of this management metadata will then be prohibitively costly in memory, if you have "tens of billions of files" to keep under management. Personally, I think the pragmatic solution would be to replicate the files themselves outside of Postgres, and have Postgres just hold opaque references to their paths, which you would join through an FDW to fill a column with their contents. As you say, the files are Write-Once-Read-Many—so you (presumably) aren't worried about how Isolated or Consistent writes to the file data would be without Postgres. Plain filesystems provide Atomicity and Durability guarantees all by themselves. It's less convenient to do things this way—you have to manage e.g. "WAL-E + a separate rsync" rather than just WAL-E—but it's not particularly bad in terms of the engineering trade-offs.
flukus 9y ago
Is there an OSS version of DTC (Distributed transaction coordinator)? That gives you the transactionality you want from the file system and other systems.
cryptonector 9y ago
Databases and filesystems duals. If "databases are bad for files", that's mostly just an implementation issue. The only serious impedance mismatches between databases and filesystems have to do with transactional semantics (which are much looser in POSIX and friends than in ACID DBs), and byte-range locking (which I'm thinking can't be advisory, only mandatory, in ACID DBs).

anarazel 9y ago

Postgres dev here. > but what I really want from Postgres is a proper equivalent to MSSQL's FILESTREAM. What sizes of files and such are you interested in? What kind of read/write patterns? I do think we need some improvements in the area. Not enough that I'll drop the other stuff I'm working on, which I think is higher priority, but enough to discuss approaches and review patches. It'd be cool if you could comment on the pgsql-hackers list.

snuxoll 9y ago
Small files, 25-50K on average (images of patient charts), and they're all they're all write-once read-many.
- anarazel 9y ago
  Hm, in that case, what stops you from just using a bytea column?
  - snuxoll 9y ago
    My major issue with bytea is two fold, the same oid issue that you have with LOB's (we have 10's of billions of pages) so you're forced to less than optimal solutions like table inheritance for large numbers of records, plus it severely bloats the size of the heap file making a VACCUM FULL take a century if needed.
    - derefr 9y ago
      > so you're forced to less than optimal solutions like table inheritance for large numbers of records Would the "native table partitioning" feature introduced in this release be a solution (or, at least, more optimal) for this? Alternately, if you want to go off-heap, what about using Foreign Data Wrappers (https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_Wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_...)? These two both sound like they might solve your problem: • https://github.com/ZhengYang/dc_fdw/wiki https://github.com/ZhengYang/dc_fdw/wiki • http://multicorn.org/ http://multicorn.org/
      - snuxoll 9y ago
        The native table partitioning removes one of the headaches of inheritance, but it mostly only relieves some of the administrative headaches - the foreign key issues remain. fdw's are great, but they don't participate in replication - which is a pretty big issue. Unfortunately, the "proper" way to do this would be with a custom type, but I can't find any way you could write one with the extension API that wouldn't be stored in the database heap (you could create some native functions similar to the existing LOB functions, but then you run into atomicity issues again). Honestly, this would be easy to write if the extension API supported off-heap storage of custom types - though I understand there's a ton of technical complexity in implementing such a feature.
        derefr 9y ago
        To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap? As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL log and so forth. Just the space overhead of this management metadata will then be prohibitively costly in memory, if you have "tens of billions of files" to keep under management. Personally, I think the pragmatic solution would be to replicate the files themselves outside of Postgres, and have Postgres just hold opaque references to their paths, which you would join through an FDW to fill a column with their contents. As you say, the files are Write-Once-Read-Many—so you (presumably) aren't worried about how Isolated or Consistent writes to the file data would be without Postgres. Plain filesystems provide Atomicity and Durability guarantees all by themselves. It's less convenient to do things this way—you have to manage e.g. "WAL-E + a separate rsync" rather than just WAL-E—but it's not particularly bad in terms of the engineering trade-offs.

flukus 9y ago

Is there an OSS version of DTC (Distributed transaction coordinator)? That gives you the transactionality you want from the file system and other systems.

cryptonector 9y ago

Databases and filesystems duals. If "databases are bad for files", that's mostly just an implementation issue. The only serious impedance mismatches between databases and filesystems have to do with transactional semantics (which are much looser in POSIX and friends than in ACID DBs), and byte-range locking (which I'm thinking can't be advisory, only mandatory, in ACID DBs).