PostgreSQL - inserting string of 30,000 characters doesn't change size? -


via command 

 select     relname "table",    pg_size_pretty(pg_total_relation_size(relid)) "size",    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) "external size"    pg_catalog.pg_statio_user_tables order pg_total_relation_size(relid) desc;

i can retrieve size of tables. (according this article) works. came weird conclusion. inserting multiple values contain approx 30,000 characters each, doesn't change size.

when executing before inserting 

tablename   | size text |external size text ------------------------------------------- participant |   264kb   |  256kb

after inserting (btw base64 encoded images) , executing select command, exact same sizes returned.

i figured couldn't correct wondering, command wrong? or postgresql special large strings?

(in pgadminiii strings not show in 'view data' view shown when executing select base64image participant).

and next wondering (not main question nice have answered) if best practice (since app generates base64 images) or should f.e. convert them image on backend , store images remotely on server instead of in database?

storage management

when insert (or update) data requires more space on disk uses, postgres (or any dbms) allocate space store new data.

when delete data either setting column smaller or deleting rows, space not released operating system. assumption that space re-used subsequent updates or inserts , extending file relatively expensive operation database tries avoid (again dbms do).

if space allocated bigger space stored, can influence speed of retrieval - table scans ("seq scan" in execution plan) more blocks necessary need read harddisk. known "table bloat".

it possible shrink space used using statement vacuum full. should used if suspect problem "bloat". this blog post explains in more details.

storing binary data in database

if want store images in database, means use bytea instead of string value. image encoded in base64 takes twice spaces raw data would.

there pros , cons regarding question if binary data (images, documents) should stored in database or not. subjective decision make , depends on lot of external factors.

see e.g. here: which best method store files on server (in database or storing location alone)?


Comments

Post a Comment

Popular posts from this blog

php - How to add and update images or image url in Volusion using Volusion API -

actually have store importing products store product data insert , update image or image url not insert , update. this xml code name datapro.txt. <products_joined> <productcode>3710_012t</productcode> <vendor_partno>eah5450silent/di/1gd3(lp)</vendor_partno> <productname>test product ta</productname> <hideproduct>n</hideproduct> <stockstatus>20</stockstatus> <lastmodified>1/5/2016 10:25:00 am</lastmodified> <lastmodby>2</lastmodby> <productweight>0.9</productweight> <productprice>100</productprice> <productmanufacturer>asus tek</productmanufacturer> <vendor_price>32.69</vendor_price> <numproductssharingstock>0</numproductssharingstock> <categoryids>107</categoryids> <producturl>http://tebkq.mvlce.servertrust.com/productdetails.asp?productcode=3710_012t</producturl>
Read more

javascript - jQuery UI Splitter/Resizable for unlimited amount of columns -

using jqueryui splitter or resizable functions want create layout allows unlimited amount of columns added , columns need resizable. so fiddle: https://jsfiddle.net/f3glh3mw/ but in fiddle html contains out of nested sets instead of inline. limits possibilities above fiddle can have equal widths 2,4,8,16,32 etc columns. in between shows crooked can see in fiddle. when researching came on question: jquery ui , splitter if @ top answer shows structure like: <div class="wrap"> <div class="resizable resizable1"></div> <div class="resizable resizable2"></div> </div> full fiddle: http://jsfiddle.net/8qztj/86/ which structure want javascript hardcoded 2 resizable items. so here question: how modify last fiddle works amount of .resizable divs. or know of plugin can this. thanks helping! so far found plugin: http://www.bacubacu.com/colresizable/ which says , close functionality want. in
Read more

javascript - IE9 error '$'is not defined -

Image
<!--[if lte ie 9]><html class="lte9"><![endif]--><!--[if gt ie 9]><html><![endif]--> <!--[if !ie]><!--> <html> <!--<![endif]--> blah blah <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script src="{{ asset('js/mypage.js') }}" type="text/javascript"></script> </body> </html> when load page, ie9 brings error '$' not defined . how can solve error? +++ mypage.js $(function(){ //when load page, //ie9 tag not moving. $(window).load(function() { $('div.mask').hide(); }); // when click , willshow show. //for not ie 9 if (!$('html').hasclass('lte9')) { var = $('ul.about > li > span.activebtn');
Read more