0

@pozs... this is NOT a duplicate of the one you indicated. That was the first place I looked. I could care less about the difference between text and varchar. I'm asking about physical space used within the medium aka server hard drive.

I know that hard drives are split into blocks of bytes aka chunks, that if used less then the total amount of the block the remaining space is an empty waste of unused space. What I'm curious about is that the text option itself uses a certain amount of storage. Can the space used be reduced rather than just limiting quantity of input. I could say text limit => 1, and it may still use thousands upon thousands of bytes... this is what I'm asking about.

This is a photo of hard drive blocks. This is how I imagine ActiveRecord text type space used

Hard Drive Blocks in chunks of 4K

Here's the wiki on Blocks(data storage) http://en.wikipedia.org/wiki/Block_(data_storage) As you can see they say "Block storage is normally abstracted by a file system or database management system (DBMS)" What they do NOT say is HOW it is abstracted.

According to Igor's blog he says "To my surprise, they determined that the average I/O size of our Postgres databases was much higher that 8KB block size, and up to 1MB." http://igorsf.wordpress.com/2010/11/01/things-to-check-when-configuring-new-postgres-storage-for-high-performance-and-availability/ While this is helpful to know it doesn't tell me the default behaviour between ActiveRecord and PostgreSQL in handling blocks.

According to concernedoftunbridgewells "The database will allocate space in a table or index in some given block size. In the case of Postgres this is 8K". https://dba.stackexchange.com/questions/15510/understanding-block-sizes/15514#15514?newreg=fc10593601be479b8ed697d1bbd108ed So if 8K is used as a block, then how high or low do I set the text type limit to match and fit within the one 8K block, because it may use more then just one block.

I know that PostgreSQL block size setting can be changed. So I would like clarity on "how ActiveRecord PostgreSQL block size handling currently works". I will accept a good answer for that.

Community
  • 1
  • 1
6ft Dan
  • 2,365
  • 1
  • 33
  • 46
  • possible duplicate of [PostgreSQL: Difference between text and varchar (character varying)](http://stackoverflow.com/questions/4848964/postgresql-difference-between-text-and-varchar-character-varying) – pozs Jul 03 '14 at 13:58

2 Answers2

1

A page contains more than one item (assuming there is space obviously). If your row is less than 8k, then other rows will be stored on the same page with it (I simplify slightly - postgres stores large columns separately anyway). Limiting the max length of a column doesn't interact with this.

My reading of the details on character types is that strings under 126 characters incur 3 bytes less overhead, but this happens on a row by row basis, independently of what the maximum length is.

The postgresql docs have details on the exact on disk format and how postgresql deals with large columns.

Frederick Cheung
  • 83,189
  • 8
  • 152
  • 174
0

IMO, the size takes by a text type db column is mainly depends on the content store in the column. The limit setting inside the ActiveRecord will just do a validation before the content is saving into the db column, and didn't has an impact to the actual storage.

nickcen
  • 1,682
  • 10
  • 10
  • So when writing as text to DB it might use, say 1Mb as an example per entry? And limit => 500 won't reduce the space used? – 6ft Dan Jul 04 '14 at 04:21
  • Your saying size is only effected by the actual data itself... where as the limit only sets a cap on what is allowed. So the answer would be that less data is using less physical space. – 6ft Dan Jul 04 '14 at 04:32
  • with the limit => 500, you will not be possible to write to the db with data larger then 500 through ActiveRecord, even thought there is no limitation in your db's column setting. – nickcen Jul 04 '14 at 11:21