How "frequently used data" and cache work in databases

Question

I know that the databases can store the most used data in the RAM memory for fast access, but when, and what data ?

1- The query is executed multiple times in this table

id, name, gender
-- ----- -------
1 , Bill, Male.
2 , Todd, Male.

SELECT * FROM people WHERE gender = 'Male';

• What is the minimum value it can save in memory ( column, row, page, table ) ?
• Having PK or FK make any difference when its can be save ?
• If i UPDATE the table or row, the memory have to be read again ?

2 - And a query with JOIN in tables like this ?

people
id, name, gender_id
-- ----- -------
1 , Bill, 2
2 , Todd, 2

people_gender
id, gender
-- -------
1 , Female.
2 , Male.

SELECT id, name, gender FROM people p JOIN people_gender pg ON p.gender_id = pg.id

• The database read once and save the value of people_gender.id, then it can read only 4 bytes more 2 bytes in people.gender_id = 6 bytes OR it have to JOIN and read every row, even when the value is well know (1+4+1+4 = 10) ?
• The JOIN is worth of it or the first case is better for performance ?

The true base can be in Sql Server up to 16 GB RAM or Postgresql down to 1 GB RAM, it have a column that save a state of an event like WORKING, WAITING, STOPPED and CANCELED ( actually not in English, five states with 8-10 chars each ), its so common updating and/or returning this data in the table Event, that i starting by testing performance things here.

I working in a software, that growing a lot in last years and now is facing performance problems, this is work for the DBA ... but we do not have one :D.

My first question in the dba meta, sorry any mistaking, any tip is welcome.

Thank you in advance.

Answer 1

Caching with these DBMS is at the block/page level. Data must be in memory to be used. The high-level process is that the DBMS first looks in cache, and if not present, reads the needed blocks/pages into memory for use. Implementation details vary greatly by DBMS but generally buffers are reused by a LRU algorithm with the intent to keep hot data in cache and evict less frequently used data so that the greatest benefit is realized by limited memory resources.

To address performance, perform query and index tuning so that the minimal amount of data must be retrieved from storage and add as much memory as you can for caching.