Re: [SPAM] Re: WAL directory size calculation - Mailing list pgsql-general

Actually you can increase the over-all performance of your system several fold by distributing the source of data with encryption. CDN services use this old technique consistently all the time.

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.  

Of course for small amount of data, say encrypted user name, password or id credential, db engine is still the best. But for larger files you could benefit substantially by looking at hybrid solutions.

Check out companies like www.maytech.com, not related to me at all. But they have secure network used for NHS (UK).

Their ftp service does have user name password protection which could be customised for different customer. They also distributed servers around the world.

Hope this helps.

 

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.

Privacy. Blobs are stored encrypted, since they are health-related images or documents.
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.
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.
Imagine if we have a folder containing all images.... double click and open...

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....
 

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.

It can hit the DB performance in Indexing, backups, migrations and load balancing.

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.
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)

 

 

Hope this helps.

 

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Moreno Andreo
Sent: 29 July 2016 10:19
To: pgsql-general@postgresql.org
Subject: Re: [SPAM] Re: [GENERAL] WAL directory size calculation

 

Il 29/07/2016 10:43, John R Pierce ha scritto:

 

Aside of this, I'm having 350 DBs that sum up a bit more than 1 TB, and plan
to use wal_level=archive because I plan to have a backup server with barman.

 

With that many databases with that so many objects

350 DBs with about 130 tables and a bunch of sequences each, for the sake of precision.
With extensive use of BLOBs.

and undoubtable client connections,

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...

I'd want to spread that across a cluster of smaller servers.

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.
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).
I don't know to achieve this, but I will find a way somewhere.

just sayin...

ideas are always precious and welcome.

 

-- 

john r pierce, recycling bits in santa cruz