Hot answers tagged

34

Store in the database with a blob A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity. Store on the filesystem with a link in the database I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. ...


21

Number 1 for complete data integrity. Use the other options if you don't care about data quality. It's that simple. Most RDBMS have optimizations for storing BLOBs (eg SQL Server filestream) anyway


15

If going for oracle, take a look at dbfs and Secure Files. Secure Files says it all, keep ALL your data safe in the database. It is organized in lobs. Secure Files is a modernized version of lobs, that should be activated. dbfs is a filesystem in the database. You can mount it similar like a network filesystem, on a Linux host. It is real powerful. See ...


15

as superuser: create or replace function bytea_import(p_path text, p_result out bytea) language plpgsql as $$ declare l_oid oid; r record; begin p_result := ''; select lo_import(p_path) into l_oid; for r in ( select data from pg_largeobject where loid = l_oid order by pageno ) loop ...


10

This solution isn't exactly efficient in terms of runtime, but it's trivially easy compared to making your own headers for COPY BINARY. Further, it doesn't require any libraries or scripting languages outside of bash. First, convert the file into a hexdump, doubling the size of the file. xxd -p gets us pretty close, but it throws in some annoying newlines ...


10

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option. Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps). Call expdp as usual, adding: REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION Short example (schema: mat): create or replace package remap as function ...


10

I think the right answer here depends a lot on your application, and how important those documents are. For a document management system, or a system where recoverability of the stored documents is critical (so most things financial, HR or CRM related), storing documents inline or using your favourite DB vendor's proprietary document tech seems like the ...


10

In the absence of any answers I've explored the issue further myself. It looks like user-defined functions can handle all base types, including bytea and smallint[], so this doesn't affect the choice of representation much. I tried out several different representations on a PostgreSQL 9.4 server running locally on a Windows 7 laptop with a vanilla ...


10

Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors). One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like UPDATETEXT, ...


7

I want to add my experience here as to the tradeoffs. In PostgreSQL, at least, the performance impacts are quite minimal in terms of the db server. Large blobs are stored in separate files, not in the main heap tables so as to move them out of the way of operations that may count large numbers of records. Other dbs may do something similar. The major ...


6

BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB As for TINY/MEDIUM/LONG; how big is your data? TINYBLOB : max 255 bytes BLOB : max 65,535 bytes MEDIUMBLOB : max 16,777,215 bytes LONGBLOB : max 4,294,967,295 bytes In some small minority of cases TINYBLOB may suffice, ...


5

My vote would be for neither. Store the data in a system like Amazon S3 or Microsft's CDN and store that URL in the database. This way you get reliability of having the data always accessible without having monster sized databases to deal with.


5

Back in the day, Microsoft hyped up the ability to store images (and similar blob data types) in the database. The was a cool new feature of SQL Server 2000 (I am pretty sure it was 2000, not 7.0) and many people jumped on the bandwagon. Storing BLOBS in the database has advantages and disadvantages: On one hand, all your data and related images or ...


5

Although it partly depends on the application/environment (people included), I'd go for the blob. Keeping everything in the database means replication works for file data. You'd need a separate mechanism to synchronise FS files. In some applications, the filesystem shouldn't be modified anyway. For example, on a production website, I'd avoid ever using the ...


5

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies). This means however, you ...


4

Memory related settings You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file. Actually, there's a litany of good info in this ...


4

The idea behind the large object API is to mimic a file-like API. The OID is like the path of the file, and the file descriptor obtained by lo_open or lo_creat is the equivalent of the POSIX open() and creat() system calls for files. JDBC provides LargeObject.truncate() and the both libpq (in C) and the server have built-in lo_truncate() functions. So yes ...


4

For postgres: It's actually straight foreward. There is a BYTEA type that can be used for storing binary strings. Per default, there are no build in utilites like the ones mentioned for MS or Oracle. So storing lots of big files and retrieving them can get tedious. You also need to do the conversion of the files within the application (like with a ...


4

Just do SELECT ItemId FROM MyTable WHERE Value = 0xAAFF You are converting the varchar representation to varbinary(max) which is not correct. SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.


4

Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small. I have discussed TOAST before in the DBA StackExchange May 01, 2012 : what is bigger than a longblob? Mar 21, 2012 : Are many NULL columns harmful in mysql ...


3

You can get the size in bytes of the BLOB column via DBMS_LOB.GETLENGTH(...). To get a total size in bytes of column for the entire table SUM it up like this: SELECT SUM(DBMS_LOB.GETLENGTH(my_blob_column)) as total_bytes FROM my_table If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual ...


3

I have created a very simple demo of how partition switching might work for you: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1 IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date ...


3

Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are. I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully. In your specific case, however, there may be ...


3

You can find out from a purely theoretical generic RDBMS view with the details posted, however think about what is happening when a RDBMS has to find and sort data. RDBMS has to track each data page (or lowest amount of data read/written), and it has to track if it's in memory (dirty page), if it's on disk, if it's indexed, if it's sorted physically, if so ...


3

If your files are going to be >1MB, then FILESTREAM is your best option. You can look at BOL documentation here, which clearly describes and advises you when is the best situation to use it.


3

There's more to life than performance, like data integrity. If you only care about performance you should be writing all your code in Assembly. There is a higher risk of screwing things up by using the file system: If you keep files in the file system, you now have two things to backup instead of one. Other stuff: dealing with orphaned files. Dealing ...


3

The table in question is a heap. This storage structure does not release pages made empty by delete operations, unless the delete occurs while an exclusive table lock is taken (and not always then). From DELETE (Transact-SQL) in the documentation: To delete rows in a heap and deallocate pages, use one of the following methods. Specify the TABLOCK hint ...


3

Consider using de-duplication if you have lots of identical XML - documentation link. I wrote a huge unit test, but it turns out that SecureFile LOBs are always stored in a Lob Segment outside the row!


3

Don't do it. There really isn't an upside to having files stored in the database. Doesn't it already feel weird and fishy when you think to yourself: Should I store files in a database or a filesystem? Even better, say it out loud. On to the facts: Using the database "PROS"... but not quite: "Atomicity" which is correct but it's a double edged ...


3

Either of these models look reasonable. The NoticeMasterFile intersection table is not strictly needed since you can get the information through either model. The question is: How do you intend to use the NoticeMasterFile information? Perhaps this change would support some future plans that you have. Of course, the problem is that looking at a data model ...



Only top voted, non community-wiki answers of a minimum length are eligible