2

I might sound a little naive to ask this question but I couldn’t find out the answer anywhere. So my understanding is that the buffer in a sql db maintains disk pages in cache so that when a query comes in and if the page is available in the cahce the data can be served without going to the disk. Now my question is that does the pages in bufferpool maintains the actual data (complete rows) and directly serves the data or it keeps the offset of the disk and whenever needed directly fetches from the offset. My earlier understanding was the former but when i was reading more I learnt about index organised tables and heap organised tables which made me confused about how it actually does or it could be both.

3
  • "SQL" is a language; it does not have a concept of a buffer pool. Do you have a particular DBMS in mind? If so, please tag your question accordingly.
    – mustaccio
    Commented Oct 20, 2023 at 21:22
  • SQL is definitely a language but all DBMS’ that are SQL based have a concept of buffer pool (page cahce). For the sake of the discussion we can talk about Mysql and Postgres Commented Oct 21, 2023 at 5:30
  • 1
    @saurabhsingh That's not true. Also the implementation of the buffer pool varies between certain database systems, as will the answer to your question then. In general, it's important to tag the database system you're referring to. Just a heads up.
    – J.D.
    Commented Oct 21, 2023 at 14:27

4 Answers 4

0

I cannot speak for MySQL, but I would be quite surprised if they did it substantially differently.

The page cache ("shared buffers" in PostgreSQL) contains the authoritative copy of the database pages. In the case of PostgreSQL, the whole page (8kB) is copied to cache the first time it is accessed, and a table row is always contained in an 8kB page. If data are modified, they are only modified in shared buffers. Later, the checkpointer writes the modified page out to disk.

Perhaps the important message is that shared buffers contains the authoritative copy of the data. The data files on disk contain outdated, inconsistent data. This is no problem, because the transaction log (WAL in PostgreSQL) contains instructions how to repair the data files.

data files on disk + transaction log = consistent database

data files on disk + data page cache in memory = consistent database

0

Table pages contain the row data. Index pages contain offsets into the table pages. Both types of pages can be found in the buffer pool. In postgresql the offset given by the index points to disk, but there is a mapping layer that translates that disk offset so it points instead into the relevant place in the buffer pool (first populating the buffer pool from disk if it were not already there).

Postgresql doesn't have index organized tables.

0

Engine=InnoDB in MySQL: The buffer_pool (size configured via innodb_buffer_pool_size in bytes) contains "blocks" (pages) of size 16KB.

A block contains

  • Some number of complete rows of a B+Tree of data blocks. All the columns of a row are in this page -- except that big TEXT or BLOB columns are stored "off-record" in some other place.
  • Some number of index "rows" of a B+Tree of index blocks. The columns of the secondary key and the PRIMARY KEY are the "columns" in the index "rows".
  • Other miscellany.

Data and index blocks are structured in B+Tree order by the key (Primary or secondary). Each secondary index lives in its own B+Tree. On disk all the B+Trees for a given table live in the same .ibd file. All are cached in the one buffer_pool.

(FULLTEXT and SPATIAL indexes are not discussed here.)

Yes, the buffer_pool is an [approximately] least-recently-used cache. Often a SELECT won't have to look up an index and the row(s) it needs without touching the disk.

Writes (INSERT, UPDATE, DELETE) also use the cache. Modified blocks are marked as "dirty" for eventual writing to disk. Index blocks in the buffer pool are always [logically] kept up to date and eventually written back to disk. (See also "change buffering," which further delays writes of index blocks.)

In all cases, the buffer_pool is identical to what is on disk. However, there are a lot of tricks going on also to be efficient while still handling deadlocks, rollback, delayed writing, crashes, etc. "ACID" is very important.

As a crude Rule of Thumb, 100 rows are in a block.

B+Tree is efficient for both "point queries" (fetching one row via an index) and "range queries" ("WHERE x > 5"). Hash indexing is good for only point queries; MySQL does not bother with such redundant effort.

There are other "Engines" in MySQL; perhaps MyISAM is close to Postgres's main engine in implementation.

0

You should keep in mind that though both products have something called "buffer," the implementation is different. This is why comments asked you to be specific about which brand of RDBMS you're talking about. The answer to your question might be different.

The other answers cover PostgreSQL, so I'll answer for MySQL.

MySQL implements a buffer pool only for tables using the InnoDB storage engine. MySQL supports other storage engines, which may also use RAM to cache data or indexes, but they do so differently.

For InnoDB, the buffer pool contains verbatim copies of the pages as they exist on disk storage. These pages contain row data, or index structures (which are references to primary key values, not offsets as in PostgreSQL), or extended BLOB data, or various other types of pages to support MVCC. Basically anything that is stored in pages in a tablespace file on disk may be stored in the buffer pool. The buffer pool also has some RAM dedicated to metadata of the buffer pool itself, such as lists of free pages.

InnoDB only supports index organized tables. It's either index organized by the primary key, or by the first non-null unique key, or else it creates a 6-byte synthetic key internally to use as the clustered index. Thus all tables are stored as indexes.

2
  • Is the INNODB Buffer Pool row data COMPRESSED or NOT COMPRESSED for a table with COMPRESSED table storage? Just curious. Commented Dec 30, 2023 at 1:58
  • @WilsonHauck, Both compressed and non-compressed form of the same data is represented in the buffer pool. This means compressed data saves disk space, but actually uses more RAM. Read the section "Compression and the InnoDB Buffer Pool" in this manual page: dev.mysql.com/doc/refman/8.0/en/… Commented Dec 30, 2023 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.