[HN Gopher] Postgres Insider Terminology
       ___________________________________________________________________
        
       Postgres Insider Terminology
        
       Author : todsacerdoti
       Score  : 81 points
       Date   : 2022-11-04 06:46 UTC (1 days ago)
        
 (HTM) web link (www.crunchydata.com)
 (TXT) w3m dump (www.crunchydata.com)
        
       | ryandotsmith wrote:
       | Missing a fun one: ctid
        
         | pramsey wrote:
         | Yes! Actually, I was thinking "WAL" is constantly thrown around
         | without explanation too.
        
         | asah wrote:
         | ctid gives me ptsd !
        
       | leetrout wrote:
       | That cookie banner takes you to the main page when you try to
       | close it on mobile.
       | 
       | Super annoying.
        
       | remram wrote:
       | Array and relation are definitely not insider terms.
        
       | didgetmaster wrote:
       | When studying the internals of Postgres years ago, I learned that
       | pages could greatly affect the disk space required to hold a
       | table. If the page size was 8192 bytes (the default) and if the
       | schema defined each row as holding 4097 bytes, then two rows
       | would not fit within a single page. This would cause every row to
       | be within its own page and would waste almost half of the space.
       | 
       | Anyone know if this is still true?
        
         | j16sdiz wrote:
         | Afaik, this is still true in most cases.* this is true for
         | almost every database.
         | 
         | * Except, maybe, toast, hot or compressed..
        
           | hashmash wrote:
           | > this is true for almost every database.
           | 
           | ...that uses fixed sized pages.
        
             | smegsicle wrote:
             | so true for almost every sql database? or not?
        
               | didgetmaster wrote:
               | I think it is true for every row-oriented database that
               | stores all the values for a single row together within a
               | fixed-sized structure. Columnar stores on the other hand
               | may or may not do this.
               | 
               | I have built a database engine that is a columnar store.
               | All the values for each column are stored together. This
               | requires just about every query to fetch each row value
               | separately, but it has proven to be incredibly fast.
               | Queries on big tables run several times faster than on
               | Postgres and it is also about 5x faster than on SQLite.
               | https://www.youtube.com/watch?v=Va5ZqfwQXWI
        
               | hashmash wrote:
               | MyRocks is MySQL backed by an LSM tree, and so it doesn't
               | have fixed sized pages.
        
               | smegsicle wrote:
               | so a mysql storage engine that uses the rocksdb key/value
               | store?
               | 
               | sounds like could be one of those rule-proving
               | exceptions, even if it is a big facebook project
        
         | jeff-davis wrote:
         | What you are describing is called "internal fragmentation" and
         | it's always a problem at some level in any system.
         | 
         | There are tons of ways to mitigate the problem including
         | variable-length data, out-of-line storage, and compression.
         | Postgres does those things, but I suppose there's always room
         | to improve.
         | 
         | Best to just see how much storage a given table uses, and see
         | if it's a problem.
        
         | Helmut10001 wrote:
         | Only slightly related, but the biggest increase in space I saw
         | was after transitioning my 1.8 TB Postgres database to ZFS,
         | which has compression turned on by default. Afterwards, the
         | size needed was 310 GB, with no noticeable loss in speed.
        
       | ialad wrote:
       | I was confused by the author's definition of a relation. They
       | said that a single tuple is an "unary relation". They probably
       | meant a single-tuple (1-tuple). Removing the hyphen changes the
       | meaning. https://en.wikipedia.org/wiki/Finitary_relation
        
         | layer8 wrote:
         | The author seems to have misunderstood Codd's definition (which
         | is just the usual definition for mathematical relations). An
         | N-ary relation is an N-column table (in the idealized database
         | model where a table is a mathematical set of rows, i.e. no
         | duplicate rows).
        
       ___________________________________________________________________
       (page generated 2022-11-05 23:01 UTC)