<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Il 29/07/2016 15:13,
FarjadFarid(ChkNet) ha scritto:<br>
</div>
<blockquote
cite="mid:000c01d1e99a$f2825c40$d78714c0$(at)checknetworks(dot)com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.moz-txt-tag
{mso-style-name:moz-txt-tag;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">If
you add a URL to an ftp with SSL certificate. Your backup
will be much quicker and if someone stole the computer the
images are still encrypted as before. It is just the source
where data comes from that changes. <br>
</span></p>
</div>
</blockquote>
... and if while working the Internet connection drops....? or my
office is not covered by broadband at all (and, still in 2016, in
Italy there are so many places not covered by broadband... no adsl,
no wi-max, low-performing mobile)?<br>
Local copies of databases that we synchronize are made to permit
users to work even if no internet connection is available (since
they're doctors, they have to have their data available almost all
the time)<br>
<br>
This architecture is made by design. Some years ago, when we started
designing our software, we went into this situation, and the
question was "Why don't we have just a remote server and users
connect remotely, instead of having replicas in their places?"<br>
This can ease updates, troubleshooting, almost everything.<br>
After a while, the answer we went into is exactly as above. Until we
have slow and unreliable Internet connections (fiber coverage is
growing, but it's still very sparse) so we can't count on them, we
can't rely only to a remote server.<br>
<br>
Thanks<br>
Moreno<br>
<br>
<blockquote
cite="mid:000c01d1e99a$f2825c40$d78714c0$(at)checknetworks(dot)com"
type="cite">
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p><br>
<br>
<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
lang="EN-US">From:</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
lang="EN-US"> Moreno Andreo
[<a class="moz-txt-link-freetext" href="mailto:moreno(dot)andreo(at)evolu-s(dot)it">mailto:moreno(dot)andreo(at)evolu-s(dot)it</a>] <br>
<b>Sent:</b> 29 July 2016 12:08<br>
<b>To:</b> FarjadFarid(ChkNet)
<a class="moz-txt-link-rfc2396E" href="mailto:farjad(dot)farid(at)checknetworks(dot)com"><farjad(dot)farid(at)checknetworks(dot)com></a>;
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a><br>
<b>Subject:</b> Re: [SPAM] Re: [GENERAL] WAL directory
size calculation<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Il 29/07/2016 11:44, FarjadFarid(ChkNet)
ha scritto:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">The
question to ask is what benefit would you gain by saving
BLOB object on a database than on say a flat file server
or url on an ftp server? Specially larger ones. </span><o:p></o:p></p>
</blockquote>
<p class="MsoNormal">Privacy. Blobs are stored encrypted, since
they are health-related images or documents.<br>
You should be right if all of this data would be resident only
on our server (that can only be accessed by application), but
every user has a small PG cluster in his PC with his patients
data and images that replicates continuously with our server.<br>
Our application runs on Windows. To get into patient data from
another user (say, someone that stole the computer) is a bit
tricky, because you have to know how to exclude authentication
in postgres and even after this, you have to know where to
search and what to search and sometines what is the meaning on
the encodings.<br>
Imagine if we have a folder containing all images.... double
click and open...<br>
<br>
Another point is a bit of self-defense. Our users are far to
be smart computer users, and in the past we had some cases in
which someone, trying to clean up a filled-up disk, deleted a
directory under his Paradox database (!!!) and then asked us
why the app was not loading anymore....<br>
<span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">BLOB’s
cause a lot problem for all DBs. Not unless the DB engine
can understand their structure and process them. It is not
worth the effort. </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">It
can hit the DB performance in Indexing, backups,
migrations and load balancing. </span><o:p></o:p></p>
</blockquote>
<p class="MsoNormal">Regarding backups I disagree. Files related
to database must be consistent to the database itself, so
backup must be done saving both database and images. AFAIK
there's not a big difference in backing up image files versus
BLOBS in a database.<br>
I agree about load balancing, but only in case of a bulk load
of several megabytes. (our actual server got an overload 2
months ago when a client we were activating sent a transaction
with the insertion of 50 blobs sizing about 300 megabytes)<br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">Hope
this helps. </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
lang="EN-US">From:</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
lang="EN-US"> <a moz-do-not-send="true"
href="mailto:pgsql-general-owner(at)postgresql(dot)org">pgsql-general-owner(at)postgresql(dot)org</a>
[<a moz-do-not-send="true"
href="mailto:pgsql-general-owner(at)postgresql(dot)org">mailto:pgsql-general-owner(at)postgresql(dot)org</a>]
<b>On Behalf Of </b>Moreno Andreo<br>
<b>Sent:</b> 29 July 2016 10:19<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a><br>
<b>Subject:</b> Re: [SPAM] Re: [GENERAL] WAL directory
size calculation</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<div>
<p class="MsoNormal">Il 29/07/2016 10:43, John R Pierce ha
scritto:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal">Aside of this, I'm having 350 DBs
that sum up a bit more than 1 TB, and plan <br>
to use wal_level=archive because I plan to have a
backup server with barman. <o:p></o:p></p>
</blockquote>
</blockquote>
</blockquote>
<p class="MsoNormal"> <o:p></o:p></p>
<p>With that many databases with that so many objects<o:p></o:p></p>
</blockquote>
<p class="MsoNormal">350 DBs with about 130 tables and a bunch
of sequences each, for the sake of precision.<br>
With extensive use of BLOBs.<br>
<br>
<br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p>and undoubtable client connections, <o:p></o:p></p>
</blockquote>
<p class="MsoNormal">Yes, that's another big problem... we run
normally between 500 and 700 concurrent connections... I had
to set max_connections=1000, the whole thing grew up faster
than we were prepared for...<br>
<br>
<br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p>I'd want to spread that across a cluster of smaller
servers.<o:p></o:p></p>
</blockquote>
<p class="MsoNormal">That will be step 2... while migration is
running (and will run for some months, we have to plan
migration with users) I'll test putting another one or two
machines in cluster, make some test cases, and when ready,
databases will be migrated on other machines, too.<br>
I posted a question about this some months ago, and I was
told that one solution would be to set the servers to be
master on some databases and slave on others, so we can have
a better load balancing (instead of having all writes on the
sole master, we split among all masters depending on which
database is getting the write command, especially when
having to write BLOBs that can be some megabytes in size).<br>
I don't know to achieve this, but I will find a way
somewhere.<br>
<br>
<br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p>just sayin...<o:p></o:p></p>
</blockquote>
<p class="MsoNormal">ideas are always precious and welcome.<br>
<br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p> <o:p></o:p></p>
<pre>-- <o:p></o:p></pre>
<pre>john r pierce, recycling bits in santa cruz<o:p></o:p></pre>
</blockquote>
<p> <o:p></o:p></p>
</blockquote>
<p><o:p> </o:p></p>
</div>
</blockquote>
<p><br>
</p>
</body>
</html>