Re: How to store "blobs" efficiently for small and large sizes, with random access - Mailing list pgsql-general

.ck-content { --ck-color-image-caption-background: #f7f7f7; --ck-color-image-caption-text: #333333; --ck-color-mention-background: #990030E6; --ck-color-mention-text: #990030; --ck-color-table-caption-background: #f7f7f7; --ck-color-table-caption-text: #333333; --ck-highlight-marker-blue: #72ccfd; --ck-highlight-marker-green: #62f962; --ck-highlight-marker-pink: #fc7899; --ck-highlight-marker-yellow: #fdfd77; --ck-highlight-pen-green: #128a00; --ck-highlight-pen-red: #e71313; --ck-image-style-spacing: 1.5em; --ck-spacing-large: 2px; --ck-inline-image-style-spacing: calc(var(--ck-image-style-spacing) / 2); --ck-todo-list-checkmark-size: 16px; /* This works when email is READ in Visena-reader, because there the content of the email is in "shadow-DOM", which has it's own :root, but needs to be declared in ".ck-content p, .ck-content div" as well for the actual CKEditor to display it correctly, else it get's its default from .liftTemplate */ font-family: Arial, Helvetica, sans-serif; font-size: 14px; }.ck-content .image.image_resized { max-width: 100%; display: block; box-sizing: border-box; }.ck-content .image.image_resized img { width: 100%; }.ck-content .image.image_resized > figcaption { display: block; }.ck-content .image > figcaption { display: table-caption; caption-side: bottom; word-break: break-word; color: var(--ck-color-image-caption-text); background-color: var(--ck-color-image-caption-background); padding: .6em; font-size: .75em; outline-offset: -1px; }.ck-content p, .ck-content div { font-family: Arial, Helvetica, sans-serif; font-size: 14px; }.ck-content p.visenaNormal { margin: 0; }.ck-content .text-tiny { font-size: .7em; }.ck-content .text-small { font-size: .85em; }.ck-content .text-big { font-size: 1.4em; }.ck-content .text-huge { font-size: 1.8em; }.ck-content .image-style-block-align-left, .ck-content .image-style-block-align-right { max-width: calc(100% - var(--ck-image-style-spacing)); }.ck-content .image-style-align-left, .ck-content .image-style-align-right { clear: none; }.ck-content .image-style-side { float: right; margin-left: var(--ck-image-style-spacing); max-width: 50%; }.ck-content .image-style-align-left { float: left; margin-right: var(--ck-image-style-spacing); }.ck-content .image-style-align-center { margin-left: auto; margin-right: auto; }.ck-content .image-style-align-right { float: right; margin-left: var(--ck-image-style-spacing); }.ck-content .image-style-block-align-right { margin-right: 0; margin-left: auto; }.ck-content .image-style-block-align-left { margin-left: 0; margin-right: auto; }.ck-content p + .image-style-align-left, .ck-content p + .image-style-align-right, .ck-content p + .image-style-side { margin-top: 0; }.ck-content .image-inline.image-style-align-left, .ck-content .image-inline.image-style-align-right { margin-top: var(--ck-inline-image-style-spacing); margin-bottom: var(--ck-inline-image-style-spacing); }.ck-content .image-inline.image-style-align-left { margin-right: var(--ck-inline-image-style-spacing); }.ck-content .image-inline.image-style-align-right { margin-left: var(--ck-inline-image-style-spacing); }.ck-content .image { display: table; clear: both; text-align: center; margin: 0 auto; min-width: 50px; }.ck-content .image img { display: block; margin: 0 auto; max-width: 100%; min-width: 100%; }.ck-content .image-inline { /* * Normally, the .image-inline would have "display: inline-block" and "img { width: 100% }" (to follow the wrapper while resizing).; * Unfortunately, together with "srcset", it gets automatically stretched up to the width of the editing root. * This strange behavior does not happen with inline-flex. */ display: inline-flex; max-width: 100%; align-items: flex-start; }.ck-content .image-inline picture { display: flex; }.ck-content .image-inline picture, .ck-content .image-inline img { flex-grow: 1; flex-shrink: 1; max-width: 100%; }.ck-content .marker-yellow { background-color: var(--ck-highlight-marker-yellow); }.ck-content .marker-green { background-color: var(--ck-highlight-marker-green); }.ck-content .marker-pink { background-color: var(--ck-highlight-marker-pink); }.ck-content .marker-blue { background-color: var(--ck-highlight-marker-blue); }.ck-content .pen-red { color: var(--ck-highlight-pen-red); background-color: transparent; }.ck-content .pen-green { color: var(--ck-highlight-pen-green); background-color: transparent; }.ck-content hr { margin: 15px 0; height: 4px; background: #dedede; border: 0; }.ck-content blockquote { overflow: hidden; padding-right: 0; padding-left: 1ex; margin-left: 0; margin-right: 0; font-style: unset; border-left: solid 1px #cccccc; }.ck-content .blockquote { font-style: unset; }.ck-content[dir="rtl"] blockquote { border-left: 0; border-right: solid 1px #cccccc; }.ck-content code { background-color: #c7c7c7; padding: 0 1px; font-size: small; border-radius: 2px; }.ck-content .table > figcaption { display: table-caption; caption-side: top; word-break: break-word; text-align: center; color: var(--ck-color-table-caption-text); background-color: var(--ck-color-table-caption-background); padding: .6em; font-size: .75em; outline-offset: -1px; }.ck-content .table { margin: 0 auto; display: table; }.ck-content .table table { border-collapse: collapse; border-spacing: 0; width: 100%; height: 100%; border: 1px double #b3b3b3; }.ck-content .table table td, .ck-content .table table th { min-width: 2em; padding: .4em; border: 1px solid #bfbfbf; }.ck-content .table table th { font-weight: bold; background: #000000E6; }.ck-content[dir="rtl"] .table th { text-align: right; }.ck-content[dir="ltr"] .table th { text-align: left; }.ck-content .table { margin-left: 0; }.ck-content .table table { }.ck-content .table table td { }.ck-content .page-break { position: relative; clear: both; padding: 5px 0; display: flex; align-items: center; justify-content: center; }.ck-content .page-break::after { content: ''; position: absolute; border-bottom: 2px dashed #c4c4c4; width: 100%; }.ck-content .page-break__label { position: relative; z-index: 1; padding: .3em .6em; display: block; text-transform: uppercase; border: 1px solid #c4c4c4; border-radius: 2px; font-family: Arial, Helvetica, sans-serif; font-size: 0.75em; font-weight: bold; color: #333333; background: #ffffff; box-shadow: 2px 2px 1px #000000; -webkit-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; }.ck-content .media { clear: both; margin: 0 0; display: block; min-width: 15em; }.ck-content .todo-list { list-style: none; }.ck-content .todo-list li { margin-bottom: 5px; }.ck-content .todo-list li .todo-list { margin-top: 5px; }.ck-content .todo-list .todo-list__label > input { -webkit-appearance: none; display: inline-block; position: relative; width: var(--ck-todo-list-checkmark-size); height: var(--ck-todo-list-checkmark-size); vertical-align: middle; border: 0; left: -25px; margin-right: -15px; right: 0; margin-left: 0; }.ck-content .todo-list .todo-list__label > input::before { display: block; position: absolute; box-sizing: border-box; content: ''; width: 100%; height: 100%; border: 1px solid #333333; border-radius: 2px; transition: 250ms ease-in-out box-shadow, 250ms ease-in-out background, 250ms ease-in-out border; }.ck-content .todo-list .todo-list__label > input::after { display: block; position: absolute; box-sizing: content-box; pointer-events: none; content: ''; left: calc( var(--ck-todo-list-checkmark-size) / 3 ); top: calc( var(--ck-todo-list-checkmark-size) / 5.3 ); width: calc( var(--ck-todo-list-checkmark-size) / 5.3 ); height: calc( var(--ck-todo-list-checkmark-size) / 2.6 ); border-style: solid; border-color: transparent; border-width: 0 calc( var(--ck-todo-list-checkmark-size) / 8 ) calc( var(--ck-todo-list-checkmark-size) / 8 ) 0; transform: rotate(45deg); }.ck-content .todo-list .todo-list__label > input[checked]::before { background: #26ab33; border-color: #26ab33; }.ck-content .todo-list .todo-list__label > input[checked]::after { border-color: #ffffff; }.ck-content .todo-list .todo-list__label .todo-list__label__description { vertical-align: middle; }.ck-content span[lang] { font-style: italic; }.ck-content pre { padding: 1em; color: #353535; background: #c7c7c7; border: 1px solid #c4c4c4; border-radius: 2px; text-align: left; direction: ltr; tab-size: 4; white-space: pre-wrap; font-style: normal; min-width: 200px; }.ck-content pre code { background: unset; padding: 0; border-radius: 0; }.ck-content .mention { background: var(--ck-color-mention-background); color: var(--ck-color-mention-text); }

På onsdag 19. oktober 2022 kl. 13:21:38, skrev Dominique Devienne <ddevienne@gmail.com>:

On Wed, Oct 19, 2022 at 1:00 PM Andreas Joseph Krogh <andreas@visena.com> wrote:
> Ok, just something to think about;

Thank you. I do appreciate the feedback.

> Will your database grow beyond 10TB with blobs?

The largest internal store I've seen (for the subset of data that goes
in the DB) is shy of 3TB.
But we are an ISV, not one of our clients, which have truly massive
scale for data.
And they don't share the exact scale of their proprietary data with me...

> If so try to calculate how long it takes to restore, and comply with SLA,
> and how long it would have taken to restore without the blobs.

Something I don't quite get is why somehow backup is no longer needed
if the large blobs are external?
i.e. are you saying backups are so much more worse in PostgreSQL than
with the FS? I'm curious now.

I'm not saying you don't need backup (or redundancy) of other systems holding blobs, but moving them out of RDBMS makes you restore the DB to a consistent state, and able to serve clients, faster. In my experience It's quite unlikely that your (redundant) blob-store needs crash-recovery at the same time you DB does. The same goes with PITR, needed because of some logical error (like client deleted some data they shouldn't have), which is much faster without blobs in DB and doesn't affect the blobstore at all (if you have a smart insert/update/delete-policy there).

 

Also, managing the PostgreSQL server will be the client's own concern
mostly. We are not into Saas here.
As hinted above, the truly massive data is already not in the DB, used
by different systems, and processed
down to the GB sized inputs all the data put in the DB is generated
from. It's a scientific data heavy environment.
And one where security of the data is paramount, for contractual and
legal reasons. Files make that harder IMHO.

Anyways, this is straying from the main theme of this post I'm afraid.
Hopefully we can come back on the main one too. --DD

There's a reason “everybody” advices to move blobs out of DB, I've learned.

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com