{"id":815,"date":"2026-02-05T13:27:19","date_gmt":"2026-02-05T16:27:19","guid":{"rendered":"https:\/\/lemosdba.com.br\/?p=815"},"modified":"2026-04-17T10:50:53","modified_gmt":"2026-04-17T13:50:53","slug":"where-and-how-is-my-data-stored-diving-into-an-ordinary-table-example","status":"publish","type":"post","link":"https:\/\/lemosdba.com.br\/en\/where-and-how-is-my-data-stored-diving-into-an-ordinary-table-example\/","title":{"rendered":"Where and how is my data stored? Diving into the example of a regular table in PostgreSQL"},"content":{"rendered":"

Introduction<\/h1>\n\n\n\n

Databases use different types of objects (relations) to store data on disk. Even within the same type of object, data can be stored, accessed, or referenced in different ways depending on various factors, such as the data type, data size, type of operation (insert, update, delete), estimated execution cost, TOAST techniques, HOT optimization, and others. It\u2019s also important to note that data may reside in memory without necessarily being persisted to disk.<\/p>\n\n\n\n

A common object for storing persistent data is the regular (ordinary) table, which is why it has been selected for closer examination in the demonstrations. An ordinary table will be created and populated to illustrate key concepts of database architecture. While these operations do not represent every mechanism involved in storing, managing, or accessing data, they cover typical and widely encountered scenarios.<\/p>\n\n\n\n

In addition to basic DDL and DML commands, disk files will be accessed either directly through Bash commands or indirectly via database extension functions to support the demonstrations. The objective is to inspect the contents of these files; however, direct interaction with disk files should be avoided unless absolutely necessary. For investigative purposes, accessing data through extension functions is generally preferred. Overall, the commands used are relatively simple, but if you plan to reproduce these steps, it is recommended to do so in a test environment to avoid impacting production or critical systems.<\/p>\n\n\n\n

Demonstrations<\/h1>\n\n\n\n

<\/p>\n\n\n\n

The demonstrations on the next sections will work around two simple regular tables table01<\/strong> and table02<\/strong>and table02, and most of the examples will be related to the first one.<\/p>\n\n\n\n

\"\"\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n

The postgresql cluster environment and the psql<\/strong> client used in the demonstrations are version 18, running on Ubuntu 24.04.3 LTS.<\/p>\n\n\n\n

P.S:<\/strong> Keep in mind that some definitions can change across different releases\/versions of PostgreSQL.<\/pre>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span>