4 ms·

How big does the database need to be for large BLOBs to become a problem ? "big" and "small" are quite subjective terms. How many BLOBs does one need to have,

How big does the database need to be for large BLOBs to become a problem ? "big" and "small" are quite subjective terms. How many BLOBs does one need to have, and how often to we need to touch them for this solution to become untenable ?

crabbone 3y ago
In storage you measure things in blocks. Historically, blocks were meant to be 512 bytes big, but today the tendency is to make them bigger, 4K would be the typical size in server setting. So, the idea here is this: databases that store structured information, i.e. such that needs to store integers, booleans, short strings are typically something like relational databases, eg. PostgreSQL. Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance. Object stores (eg. S3) are the kinds of storage systems that are supposed to work well for anything larger than typical files. This gives the answer to your question: blobs in a relational database are probably OK if they are under one block big. Databases will be probably able to handle bigger ones too, but you will start seeing serious drops in performance when it comes to indexing, filtering, searching etc. because such systems optimize internal memory buffers in such a way that they can fit a "perfect" number of elements of the "perfect" size. Another concern here is that with stored elements larger than single block you need a different approach to parallelism. Ultimately, the number of blocks used by an I/O operation determines its performance. If you are reading/writing sub-block sized elements, you try to make it so that they come from the same block to minimize the number of requests made to the physical storage. If you work with multi-block elements, your approach to performance optimization is different -- you try to pre-fetch the "neighbor" blocks because you expect you might need them soon. Modern storage hardware has a decent degree of parallelism that allows you to queue multiple I/O requests w/o awaiting completion. This later mechanism is a lot less relevant to something like RDBMS, but is at the heart of an object store. In other words: the problem is not the function of the size of the database. In principle, nothing stops eg. PostgreSQL from special-casing blobs and dealing with them differently than it would normally do with "small" objects... but they aren't probably interested in doing so because you already have appropriate storage for that kind of stuff, and PostgreSQL, like most other RDBMS sits on top of the storage for larger objects (filesystem), so they have no hopes of doing it better than the layer below them.
- brazzy 3y ago
  Most of what you wrote there is simply not true for modern DBMS, specifically PostgreSQL has a mechanism called TOAST (https://www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained https://www.enterprisedb.com/postgres-tutorials/postgresql-t...) that does exactly what you claim "they aren't probably interested in doing" and completely eliminates any performance penalty of large objects in a table when they are not used.
  - crabbone 3y ago
    sigh... lol, an "expert". PostgreSQL just like most RDBMS uses filesystem as a backend. It cannot be faster than the filesystem it uses to store its data. At best, you may be able to configure it to use more caching, and then it will be "faster" when you have enough memory for caching...
    - brazzy 3y ago
      What does that have to do with my comment? I didn't say say that an RDBMS is faster than a filesystem, just that your statements about them "seeing serious drops in performance when it comes to indexing, filtering, searching etc." are clearly wrong. It seems very clear that it is you who knows some vaguely relevant bit of trivia, but actually have no clue about the subject in general, and try to make up for that in arrogance. Which is, honestly, pretty embarrassing.
- TristanBall 3y ago
  "Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance." Sorry what? I mean, ext4, as a special case, has some performance issues around multiple writers to a singe file when doing direct io, but I can't think of a single other place where your statement true... and plenty where it's just not ( xfs, jfs, zfs, ntfs, refs )
  - crabbone 3y ago
spiffytech 3y ago
One datapoint on BLOB performance: SQLite: 35% Faster Than The Filesystem https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html
deleted 3y ago
[deleted]
hot_gril 3y ago
The Postgres TEXT type is limited to 65,535 bytes, to give a concrete number. "Big" usually means, big enough that you have to stream rather than sending all at once.
- asjo 3y ago
  The PostgreSQL manual states: > the longest possible character string that can be stored is about 1 GB. · https://www.postgresql.org/docs/current/datatype-character.html https://www.postgresql.org/docs/current/datatype-character.h... See also: https://www.postgresql.org/docs/current/limits.html https://www.postgresql.org/docs/current/limits.html Where do you have the 64KB number from? A small test: test=# create table test_table (test_field text); CREATE TABLE test=# insert into test_table select string_agg('x', '') from generate_series(1, 128*1024); INSERT 0 1 test=# select length(test_field) from test_table; length -------- 131072 (1 row)
  - hot_gril 3y ago
    You're right. Bad Google suggestion for "max string length postgres" that pointed to https://hevodata.com/learn/postgresql-varchar/ https://hevodata.com/learn/postgresql-varchar/ saying varchar max is 64KB, which is also wrong. I gotta stick to the official docs. Anyway, TEXT is the one we care about. In MySQL, the TEXT (and BLOB) limit is 65KB, but you can get 4GB using LONGTEXT. According to their docs: https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.h...

crabbone 3y ago

In storage you measure things in blocks. Historically, blocks were meant to be 512 bytes big, but today the tendency is to make them bigger, 4K would be the typical size in server setting. So, the idea here is this: databases that store structured information, i.e. such that needs to store integers, booleans, short strings are typically something like relational databases, eg. PostgreSQL. Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance. Object stores (eg. S3) are the kinds of storage systems that are supposed to work well for anything larger than typical files. This gives the answer to your question: blobs in a relational database are probably OK if they are under one block big. Databases will be probably able to handle bigger ones too, but you will start seeing serious drops in performance when it comes to indexing, filtering, searching etc. because such systems optimize internal memory buffers in such a way that they can fit a "perfect" number of elements of the "perfect" size. Another concern here is that with stored elements larger than single block you need a different approach to parallelism. Ultimately, the number of blocks used by an I/O operation determines its performance. If you are reading/writing sub-block sized elements, you try to make it so that they come from the same block to minimize the number of requests made to the physical storage. If you work with multi-block elements, your approach to performance optimization is different -- you try to pre-fetch the "neighbor" blocks because you expect you might need them soon. Modern storage hardware has a decent degree of parallelism that allows you to queue multiple I/O requests w/o awaiting completion. This later mechanism is a lot less relevant to something like RDBMS, but is at the heart of an object store. In other words: the problem is not the function of the size of the database. In principle, nothing stops eg. PostgreSQL from special-casing blobs and dealing with them differently than it would normally do with "small" objects... but they aren't probably interested in doing so because you already have appropriate storage for that kind of stuff, and PostgreSQL, like most other RDBMS sits on top of the storage for larger objects (filesystem), so they have no hopes of doing it better than the layer below them.

brazzy 3y ago
Most of what you wrote there is simply not true for modern DBMS, specifically PostgreSQL has a mechanism called TOAST (https://www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained https://www.enterprisedb.com/postgres-tutorials/postgresql-t...) that does exactly what you claim "they aren't probably interested in doing" and completely eliminates any performance penalty of large objects in a table when they are not used.
- crabbone 3y ago
  sigh... lol, an "expert". PostgreSQL just like most RDBMS uses filesystem as a backend. It cannot be faster than the filesystem it uses to store its data. At best, you may be able to configure it to use more caching, and then it will be "faster" when you have enough memory for caching...
  - brazzy 3y ago
    What does that have to do with my comment? I didn't say say that an RDBMS is faster than a filesystem, just that your statements about them "seeing serious drops in performance when it comes to indexing, filtering, searching etc." are clearly wrong. It seems very clear that it is you who knows some vaguely relevant bit of trivia, but actually have no clue about the subject in general, and try to make up for that in arrogance. Which is, honestly, pretty embarrassing.
TristanBall 3y ago
"Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance." Sorry what? I mean, ext4, as a special case, has some performance issues around multiple writers to a singe file when doing direct io, but I can't think of a single other place where your statement true... and plenty where it's just not ( xfs, jfs, zfs, ntfs, refs )
- crabbone 3y ago

spiffytech 3y ago

One datapoint on BLOB performance: SQLite: 35% Faster Than The Filesystem https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html

deleted 3y ago

[deleted]

hot_gril 3y ago

The Postgres TEXT type is limited to 65,535 bytes, to give a concrete number. "Big" usually means, big enough that you have to stream rather than sending all at once.

asjo 3y ago
The PostgreSQL manual states: > the longest possible character string that can be stored is about 1 GB. · https://www.postgresql.org/docs/current/datatype-character.html https://www.postgresql.org/docs/current/datatype-character.h... See also: https://www.postgresql.org/docs/current/limits.html https://www.postgresql.org/docs/current/limits.html Where do you have the 64KB number from? A small test: test=# create table test_table (test_field text); CREATE TABLE test=# insert into test_table select string_agg('x', '') from generate_series(1, 128*1024); INSERT 0 1 test=# select length(test_field) from test_table; length -------- 131072 (1 row)
- hot_gril 3y ago
  You're right. Bad Google suggestion for "max string length postgres" that pointed to https://hevodata.com/learn/postgresql-varchar/ https://hevodata.com/learn/postgresql-varchar/ saying varchar max is 64KB, which is also wrong. I gotta stick to the official docs. Anyway, TEXT is the one we care about. In MySQL, the TEXT (and BLOB) limit is 65KB, but you can get 4GB using LONGTEXT. According to their docs: https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.h...