Geek asks and answers

PostgreSQL and SQL Server btree storage basics questions

I know that SQL Server can store row-level row data in a clustered index. I believe PostgreSQL does not. If so, what is its storage paradigm?

My main question is as follows. Consider the following design and data (shown in T-SQL):

CREATE TABLE dbo.Tree
    (
    [Key] int NOT NULL,
    ID int NOT NULL
    ) ON [PRIMARY]
GO
ALTER TABLE dbo.Tree ADD CONSTRAINT
    PK_Tree PRIMARY KEY CLUSTERED 
    (
    [Key],
    ID
    ) WITH (...) ON [PRIMARY]

INSERT INTO TREE ([Key], ID) VALUES (1, 1), (1, 2), (1, 3), (1, 4).

Since this is btree with both columns as PK, I correctly say that "[Key] = 1" will be stored only once, and "ID = [1, 2, 3, 4]" will be separate values ​​in btree, while time as there would be no leaf values ​​in sé, since there are no row columns that are not part of PK?

How does it work in PostgreSQL?

+3
source share
4 answers

TL; DR - , .

PostgreSQL 4 , . SQL Server 4 . B-tree - , .

PostgreSQL . , PostgreSQL - MVCC. xmin xmax, . . PostgreSQL . . , 29, , b-, 48-52 , .

SQL Server , - . , SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Tree'), NULL, NULL, NULL). SQL Server, - :

-- Locate the first page of the index
DBCC IND('test', 'Tree', 1);
GO
-- tell SQL Server to show DBCC output in the message page, not the SQL Server log
DBCC TRACEON (3604);
GO
-- look at nasty, dirty, on page data.
DBCC PAGE(test, 1,155,3);

DBCC PAGE, . , :

Slot 0 Offset 0x60 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x000000006D6FA060

0000000000000000:   10000c00 01000000 01000000 020000††††...............  

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Key = 1                              

Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4

ID = 1                               

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (e2338e2f4a9f)

, SQL Server. Key = 1 , . .

PostgreSQL SQL Server MVCC PostgreSQL. PostgreSQL, . , PostgreSQL . SQL Server ( ), .

+13

- Postgres , . . .

CLUSTER, DML.

+2

, SQL Server . , PostgreSQL . , ?

SQL Server , PostgreSQL , , .

( ctid , , ).

, , , , .

, PostgreSQL: , .

+2
source

Take a look at my SQL indexing tutorial if you want to learn more about indexing in general.

+2
source

More articles:


All Articles