>>
PostgreSQL Overview
• PostgreSQL
• PostgreSQL Online
• User View of PostgreSQL
• PostgreSQL Functionality
• PostgreSQL Architecture
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [0/13]
∧ >>
❖ PostgreSQL
PostgreSQL is a full-featured open-source (O)RDBMS.
• provides a relational engine with:
◦ efficient implementation of relational operations
◦ transaction processing (concurrent access)
◦ backup/recovery (from application/system failure)
◦ novel query optimisation (based on genetic algorithm)
◦ replication, JSON, extensible indexing, etc. etc.
• already supports several non-standard data types
• allows users to define their own data types
• supports most of the SQL3 standard
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [1/13]
<< ∧ >>
❖ PostgreSQL Online
Web site: www.postgresql.org
Key developers: Tom Lane, Andres Freund, Bruce Momjian, …
Full list of developers: postgresql.org/contributors/
Source code: ~cs9315/21T1/postgresql/src.tar.bz2
Documentation: postgresql.org/docs/12/index.html
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [2/13]
<< ∧ >>
❖ User View of PostgreSQL
Users interact via SQL in a client process, e.g.
$ psql webcms
psql (12.5)
Type “help” for help.
webcms2=# select * from calendar;
id | course | evdate | event
—-+——–+————+—————————
1 | 4 | 2001-08-09 | Project Proposals due
10 | 3 | 2001-08-01 | Tute/Lab Enrolments Close
12 | 3 | 2001-09-07 | Assignment #1 Due (10pm)
…
or
$dbconn = pg_connect(“dbname=webcms”);
$result = pg_query($dbconn,”select * from calendar”);
while ($tuple = pg_fetch_array($result))
{ … $tuple[“event”] … }
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [3/13]
<< ∧ >>
❖ PostgreSQL Functionality
PostgreSQL systems deal with various kinds of entities:
• users … who can access the system
• groups … groups of users, for role-based privileges
• databases … collections of schemas/tables/views/…
• namespaces … to uniquely identify objects (schema.table.attr)
• tables … collection of tuples (standard relational notion)
• views … “virtual” tables (can be made updatable)
• functions … operations on values from/in tables
• triggers … operations invoked in response to events
• operators … functions with infix syntax
• aggregates … operations over whole table columns
• types … user-defined data types (with own operations)
• rules … for query rewriting (used e.g. to implement views)
• access methods … efficient access to tuples in tables
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [4/13]
<< ∧ >>
❖ PostgreSQL Functionality (cont)
PostgreSQL’s dialect of SQL is mostly standard (but with extensions).
• attributes containing arrays of atomic values
create table R ( id integer, values integer[] );
• insert into R values ( 123, ‘{5,4,3,2,1}’ );
•
• table-valued functions
create function f(integer) returns setof TupleType;
•
• multiple langauges available for functions
◦ PLpgSQL, Python, Perl, Java, R, Tcl, …
◦ function bodies are strings in whatever language
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [5/13]
<< ∧ >>
❖ PostgreSQL Functionality (cont)
Other variations in PostgreSQL’s CREATE TABLE
• TEMPORARY tables
• PARTITION’d tables
• GENERATED attribute values (derived attributes)
• FOREIGN TABLE (data stored outside PostgreSQL)
• table type inheritance
create table R ( a integer, b text);
• create table S ( x float, y float);
• create table T inherits ( R, S );
•
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [6/13]
<< ∧ >>
❖ PostgreSQL Functionality (cont)
PostgreSQL stored procedures differ from SQL standard:
• only provides functions, not procedures
(but functions can return void, effectively a procedure)
• allows function overloading
(same function name, different argument types)
• defined at different “lexical level” to SQL
• provides own PL/SQL-like language for functions
create function ( Args ) returns ResultType
as $$
… body of function definition …
$$ language FunctionBodyLanguage;
• where each Arg has a Name and Type
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [7/13]
<< ∧ >>
❖ PostgreSQL Functionality (cont)
Example:
create or replace function
barsIn(suburb text) returns setof Bars
as $$
declare
r record;
begin
for r in
select * from Bars where location = suburb
loop
return next r;
end loop;
end;
$$ language plpgsql;
used as e.g.
select * from barsIn(‘Randwick’);
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [8/13]
<< ∧ >>
❖ PostgreSQL Functionality (cont)
Uses multi-version concurrency control (MVCC)
• multiple “versions” of the database exist together
• a transaction sees the version that was valid at its start-time
• readers don’t block writers; writers don’t block readers
• this significantly reduces the need for locking
Disadvantages of this approach:
• extra storage for old versions of tuples (until vacuum’d)
• need to check “visibility” of every tuple fetched
PostgreSQL also provides locking to enforce critical concurrency.
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [9/13]
<< ∧ >>
❖ PostgreSQL Functionality (cont)
PostgreSQL has a well-defined and open extensibility model:
• stored procedures are held in database as strings
◦ allows a variety of languages to be used
◦ language interpreters can be integrated into engine
• can add new data types, operators, aggregates, indexes
◦ typically requires code written in C, following defined API
◦ for new data types, need to write input/output functions, …
◦ for new indexes, need to implement file structures
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [10/13]
<< ∧ >>
❖ PostgreSQL Architecture
Client/server process architecture:

The listener process is sometimes called postmaster
COMP9315 21T1 ♢ PostgreSQL Overview ♢ [11/13]
<< ∧ >>
❖ PostgreSQL Architecture (cont)
Memory/storage architecture:

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [12/13]
<< ∧
❖ PostgreSQL Architecture (cont)
File-system architecture:

COMP9315 21T1 ♢ PostgreSQL Overview ♢ [13/13]
Produced: 15 Feb 2021