Module-1
Introduction to PostgreSQL
Module Objective:
• What is PostgreSQL?
• PostgreSQL Naming Conventions
• PostgreSQL Limits
• Page Layout
What is PostgreSQL?
• PostgreSQL is a free and open source object-relational database management
system(ORDBMS).
• PostgreSQL began its journey in 1986 as POSTGRES, a research project of the University
of California at Berkeley .
• Michael Stonebraker and his colleagues developed Postgres.
• PostgreSQL is cross platform and runs on many operating systems such as Linux,
FreeBSD, OS X, Solaris and Microsoft Windows.
• PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID)
properties.
• PostgreSQL manages concurrency through multiversion concurrency control (MVCC).
PostgreSQL Object Naming conventions:
Common Names PostgreSQL Names
Table or Indexes Relation
Row Tuple
Column Attribute
Data Block Page( on the disk)
Page Buffer( when block is in memory)
PostgreSQL Limits
Items Upper Limit Description
Database Size Unlimited
Number of Databases 4,294,950,911
Relations per database 1,431,650,303
Relation size 32TB Default BLCKSZ of 8192 bytes
Rows per table Limited by the number of tuples
that can fit onto 4,294,967,295
pages
Columns per table 1600
Field size 1GB
Identifier length 63 bytes
Indexes per table Unlimited Constrained by maximum relations
per database
Columns per index 32
Partition keys 32
What is Page?
• Page is a smallest unit of data storage.
• Every table and index is stored as an array of pages of fixed size.
• By Default, In PostgreSQL the page size is 8kb.
• We can configure different page size during compiling the server
• All pages are logically equivalent and any row can be stored in any
page.
PostgreSQL Page Layout
Page Layout
ItemIdDat ItemIdDat
Page Header
a a
8k
Free b
Space
Tuple Tuple Special
Overall Page layout
Items Description
PageHeader Data 24 Bytes Long. Contains General
Information about the page, including
free space pointers
ItemIdData Array of pairs pointing to the actual
items. 4 Bytes per item.
Free Space The unallocated space, New item
Pointers are allocated from the start of
this area, New items from the end.
Tuple The actual item themselves.
Special Space Index access method specific data.
Different methods store different data.
Empty is ordinary tables.
Thank you.
