It is important to know about PostgreSQL data storage

PostgerSQL object identifier

OID

OID is an identifier used internally by PostgreSQL to identify database objects (databases, tables, views, stored procedures, and so on) as an unsigned 4-byte integer. It is the primary key of most system tables in PostgreSQL.

The type OID represents an object identifier. There are also alias types for multiple oids: Regproc, RegProcedure, RegOper, RegOperator, RegClass, RegType, Regrole, RegNamespace, RegConfig, and RegDictionary.

The ALIAS type of OID has no operations other than specific input and output routines. These routines can accept and display the symbolic name of the system object instead of the raw numeric value used by the type OID. The alias type makes it easy to find the OID value of an object. For example, to check the PG_attribute row associated with a table course, you could write:

SELECT * FROM pg_attribute WHERE attrelid = 'course'::regclass;
Copy the code

Oids are usually hidden columns in system tables and are allocated uniformly across the entire PostgreSQL Database Cluster. Because it is only four bytes, it is not sufficient to provide database-wide uniqueness in large databases, or even table-wide uniqueness in some large tables.

In earlier versions, OID can also be used to identify tuples. In this case, THE OID serves as the unique ID of a duplicate row without a primary key. In this case, the SPECIFIED row data can be deleted based on the OID. When we created the table earlier, default_WITH_OIDS was turned off by default. In older versions, you can specify whether to enable OID when executing the CREATE table statement.

create table foo (
    id integer,
    content text
) with oids;
Copy the code

However, starting with Postgres 12, the use of OID as an optional system column on the table was removed. Will no longer be available:

The CREATE TABLE... WITH OIDSThe command
default_with_oids (boolean)Compatibility setting

The data type OID is retained in Postgres 12. You can explicitly create a column OID of type.

XID

Transaction ID:

It consists of 32 bits, which can cause transaction ID rollback problems, refer to the documentation
Sequential generation, sequential increase
There are no data changes, such as INSERT, UPDATE, DELETE, etc., and the transaction ID does not change during the current session

The data types used in database systems are Xmin and Xmax.

Xmin stores the transaction ID that generated the tuple, which could be an INSERT or UPDATE statement
Xmax stores xids that delete or lock the tuple

A simple example is as follows:

select id, xmin, xmax from course;
Copy the code

When the PostgreSQL XID reaches 4 billion, it will overflow and the new XID will be 0. PostgreSQL’s MVCC mechanism allows previous transactions to see the tuple created by the new transaction, but the new transaction cannot see the tuple created by the previous transaction, which violates the visibility of the transaction. Specific reference documents

CID

The CID name is a command identifier. PG Each table contains some system fields. The data types used for CID are cmax and Cmin.

Cmin: the identifier of the command to insert this tuple in the insert transaction (summing from 0)
Cmax: identifier of the command to delete the tuple in the insert transaction (summation from 0)

Cmin and cmax are used to determine whether changes in line versions caused by other commands within the same transaction are visible. If all commands within a transaction are executed in strict order, each command can always see all changes in the previous transaction, without the need for command identification.

A simple example is as follows:

select id, xmin, xmax,cmin,cmax from course;
Copy the code

TID

TID is called a tuple identifier (row identifier), and a tuple ID is a pair (block number, intra-block tuple index) that identifies the physical location of the row in its table.

A simple example is as follows:

select ctid,id, xmin, xmax,cmin,cmax from course;
Copy the code

With these four identifiers in mind, let’s move on to how data is stored in PostgreSQL.

PostgreSQL data store

With respect to data storage, we all know that data is stored in a certain table in the database, and each data record corresponds to a certain row in the table, so we look at the hierarchical data store from top to bottom.

PGDATA directory structure

PGDATA is where PostgreSQL stores all its data.

To set PGDATA, run the following command.

postgres=# show data_directory;
       data_directory        
-----------------------------
 /Library/PostgreSQL/12/data
(1 row)
Copy the code

Let’s take a look at what files are in the PGDATA folder, first opening the command line window and then going to the above directory.

MacBook-Pro 12 % cd /Library/PostgreSQL/12/data
cd: permission denied: /Library/PostgreSQL/12/data
Copy the code

If you encounter any of the above problems, run the following command to try to simulate a PostgresQL user login using sudo:

MacBook-Pro 12 % sudo -u postgres -i

The default interactive shell is now zsh.
To update your account to use zsh, please run `chsh -s /bin/zsh`.
For more details, please visit https://support.apple.com/kb/HT208050.
Copy the code

Then run the following command:

Tree - FL 1 / Library/PostgreSQL / 12 / data/Library/PostgreSQL / 12 / data ├ ─ ─ PG_VERSION ├ ─ ─ base / ├ ─ ─ current_logfiles ├ ─ ─ Global / ├ ─ ─ the log / ├ ─ ─ pg_commit_ts / ├ ─ ─ pg_dynshmem / ├ ─ ─ pg_hba. Conf ├ ─ ─ pg_ident. Conf ├ ─ ─ pg_logical / ├ ─ ─ pg_multixact / ├ ─ ─ pg_notify / ├ ─ ─ pg_replslot / ├ ─ ─ pg_serial / ├ ─ ─ pg_snapshots / ├ ─ ─ pg_stat / ├ ─ ─ pg_stat_tmp / ├ ─ ─ pg_subtrans / ├ ─ ─ Pg_tblspc / ├─ pg_twophase/ ├─ pg_wal/ ├─ Pg_Xact / ├─ Postgresql.auto └ ─ ─ postmaster. PidCopy the code

Introduce a few common folders:

base/: Stores database data (in addition to specifying other tablespaces) in a subdirectory named for the database inpg_databaseThe OID.
postgresql.conf: indicates the postgresQL configuration file

Database Data store

As mentioned above, each database data is stored in the base/ directory, where the file name is called dboID.

The OID is a hidden column in the system table. Therefore, when viewing the OID of the database object in the system table, you must specify it explicitly in the SELECT statement. Enter the postgres command line window and execute the following command:

postgres=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 13635 | postgres
     1 | template1
 13634 | template0
 16395 | mydb
 16396 | dvdrental
 16399 | testdb
(6 rows)
select oid,relname from pg_class order by oid;

Copy the code

We can see the OID in the base directory under the PGDATA folder

MacBook-Pro:base postgres$ ls /Library/PostgreSQL/12/data/base
1	13634	13635	16395	16396	16399
Copy the code

Postgres database data is stored in the PGDATA/ Base /13635 directory.

Table Data store

After locating the location of the database, let’s locate the location of the data table.

$PGDATA/base/{dboID}/{relfilenode} $PGDATA/base/{dboID}/{relfilenode} Such as TRUNCATE, REINDEX, CLUSTER, and some forms of ALTER TABLE.

CREATE TABLE public.cities (
	city varchar(80) NOT NULL,
	"location" point NULL.CONSTRAINT cities_pkey PRIMARY KEY (city)
);

postgres=# select oid,relfilenode from pg_class where relname = 'cities';
  oid  | relfilenode 
-------+-------------
 16475 |       16475
(1 row)

insert into cities values('Beijing'.null);
insert into cities values('Shanghai'.null);

truncate cities ;

postgres=# select oid,relfilenode from pg_class where relname = 'cities';
  oid  | relfilenode 
-------+-------------
 16475 |       16480
(1 row)

SELECT * FROM pg_attribute WHERE attrelid = 'course'::regclass;

Copy the code

In addition to the above SQL statement, you can use the system function pg_relation_filepath to view the file storage location of a specified table.

postgres=# select pg_relation_filepath('cities');
 pg_relation_filepath 
----------------------
 base/13635/16480
(1 row)
Copy the code

When you check the PGDATA/base/13635/ directory, you will find the 16480 folder. Besides, you will find some files named relFILenode_FSM, relFILenode_VM, relFILenode_init. There are three types of 16480 files: 16480, 16480_FSM, and 16480_VM. They are the data or index files of the corresponding tables of the database, the corresponding free space mapping files, and the corresponding visibility mapping files.

If the data file is too large, what will it be called?

After a table or index exceeds 1GB, it is divided into 1GB segments. The first segment has the same filename as the filenode, and subsequent segments are named filenode.1, filenode.2, and so on. This arrangement avoids problems on some platforms that have file size limits.

postgres=# create table bigdata(id int,name varchar(64));


postgres=# insert into bigdata select generate_series(1.20000000) as key, md5(random()::text);


postgres=# select pg_relation_filepath('bigdata');
 pg_relation_filepath 
----------------------
 base/13635/16486
(1 row) # Switch command line interface to MacBook-Pro:base postgres$ ls 13635 |grep 16486
16486
16486.1
16486_fsm  
Copy the code

Tuple data storage

When we mentioned table storage above, each data file (heap file, index file) can store 1G capacity, and each file is composed of several fixed pages. The default size of the page is 8192 bytes (8KB). These pages in a single table file are numbered sequentially from 0, also known as Block Numbers. If the first page space is already filled with data, Postgres immediately adds a new blank page at the end of the file (at the end of the filled page) to continue storing data until the current table file size reaches 1GB. If the file reaches 1GB, create a new table file and repeat the process.

Inside each Page is a Page Header, several Line Pointers, and several Heaple tuples. Because the default size of each file is 1GB and the page size is 8KB, each file has approximately 131,072 pages.

Let’s start with the page structure.

Among them:

Page header: contains 24 bytes of basic page information, including PD_LSN, PD_checksum, PD_special…

Pd_lsn: Stores the xlog that recently changed the page. Pd_checksum: checksum of storage pages. Pd_lower, pd_upper: pd_lower points to the end of a line pointer, and pd_upper points to the last tuple. Pd_special: used in index pages to point to the beginning of a special space. Pd_flags: Used to set the bit flags. Pd_pagesize_version: indicates the pagesize and page version. Pd_prune_xid: old XID that can be deleted. If no XID exists, the value is zero.Copy the code

Line pointe: a 4-bytes (offset, length) binary pointer to the associated tuple
Heap tuple: Used to store row data, note that tuples are stacked forward from the end of the page, and between the tuple and the row pointer is the free space of the data page.
Blank space: if no space is requested, a new line point is requested from the first end and a new tuple is requested from the last end

Row (page_index, item_index) (page_index, item_index); CTID(ItemPointer);

select ctid,* from course;
Copy the code

The query result is as follows:

For a detailed explanation of tuple structures and data variations, see this article.

extension

schema

In addition to the default public schema, PostgreSQL has two heavier system schemas: Information_SCHEMA and PG_catalog.

View all schemas in the current database by viewing pg_catalog.pg_namespace.

postgres=# select * from pg_catalog.pg_namespace ;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13335 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)
Copy the code

We create tables, views, indexes, and so on under public by default.

Information_schema is provided for users to view information about tables/views/functions, which are mostly views.

select * from information_schema."tables";
Copy the code

Pg_catalog contains the system tables and all the built-in data types, functions, and operators. There are many system tables under pg_catalog, such as PG_class, PG_attribute, pg_authID, etc. You can refer to this article for detailed descriptions of these tables.

reference

PostgreSQL the OID

SQL, Postgres OID, what are they and why are they useful?

PostgreSQL cmin and cmax

PgSQL · Feature analysis · ANALYSIS of MVCC mechanism

PgSQL · Feature analysis · Transaction ID rollback problem

Dry goods | PostgreSQL data table file structure and layout of the underlying analysis

PostgreSQL Directory structure base directory

It is important to know about PostgreSQL data storage

PostgerSQL object identifier

OID

XID

CID

TID

PostgreSQL data store

PGDATA directory structure

Database Data store

Table Data store

Tuple data storage

extension

schema

reference

Related Posts

How to build a complete video broadcast system?

Logical structure of PostgreSql

The Design and Implementation of Libvirt Virtual Machine Based on Nomad (2)