PostgreSQL 8.4 TriLUG 2009-11-12

50 %
50 %
Information about PostgreSQL 8.4 TriLUG 2009-11-12

Published on November 22, 2009

Author: amdunstan

Source: slideshare.net

PostgreSQL 8.4 features Andrew Dunstan [email_address] [email_address]

Topics General info and history PostgreSQL general features (briefly) PostgreSQL 8.4 features Not comprehensive See the release notes A few looks at what's in the pipeline

General info and history

PostgreSQL general features (briefly)

PostgreSQL 8.4 features

Not comprehensive

See the release notes

A few looks at what's in the pipeline

Untopics Why PostgreSQL is better or worse than <fill in blank here> Should the name be PostgreSQL or Postgres? GPL vs BSD Four legs Unix good, two legs Windows bad emacs vs. vi GUI vs command line ...

Why PostgreSQL is better or worse than <fill in blank here>

Should the name be PostgreSQL or Postgres?

GPL vs BSD

Four legs Unix good, two legs Windows bad

emacs vs. vi

GUI vs command line

...

Play along wget ftp://ftp10.us.postgresql.org/pub/postgresql /source/v8.4.1/postgresql-8.4.1.tar.bz2 tar -j -xf postgresql-8.4.1.tar.bz2 cd postgresql-8.4.1 ./configure –prefix=`pwd`/../pg84 --with-pgport=5678 make && make install && cd contrib && make && make install

wget ftp://ftp10.us.postgresql.org/pub/postgresql /source/v8.4.1/postgresql-8.4.1.tar.bz2

tar -j -xf postgresql-8.4.1.tar.bz2

cd postgresql-8.4.1

./configure –prefix=`pwd`/../pg84 --with-pgport=5678

make && make install && cd contrib && make && make install

Who uses PostgreSQL? Yahoo myyearbook Skype Etsy New York Post Afilias Whitepages.com JourneyX IMDB Rockport Apple NTT Cisco National Weather Service

Yahoo

myyearbook

Skype

Etsy

New York Post

Afilias

Whitepages.com

JourneyX

IMDB

Rockport

Apple

NTT

Cisco

National Weather Service

What uses PostgreSQL? Bugzilla Wikipedia Drupal Bricolage OpenACS Gforge xTuple/OpenRPT OpenBravo Serendipity PostGIS OpenStreetMap Reddit Trac LedgerSMB

Bugzilla

Wikipedia

Drupal

Bricolage

OpenACS

Gforge

xTuple/OpenRPT

OpenBravo

Serendipity

PostGIS

OpenStreetMap

Reddit

Trac

LedgerSMB

PostgreSQL history Original Postgres project started at Berkeley 1986 by Michael Stonebraker SQL added in 1995 Current community project dates from 1996 Several developers from then still active, e.g. Bruce Momjian

PostgreSQL License BSD Nobody owns the code, anyone can use the code No monopoly

BSD

Nobody owns the code, anyone can use the code

No monopoly

PostgreSQL philosophy Stability Safety Correctness Robustness Standards compliance Performance

Stability

Safety

Correctness

Robustness

Standards compliance

Performance

PostgreSQL Features Multi Version Concurrency Control (MVCC) Readers don't block writers Writers don't block readers Extensive and extensible type system Joins and Subqueries Foreign keys Namespaces (schemas) Triggers

Multi Version Concurrency Control (MVCC)

Readers don't block writers

Writers don't block readers

Extensive and extensible type system

Joins and Subqueries

Foreign keys

Namespaces (schemas)

Triggers

More PostgreSQL features Stored functions C Plpgsql partial clone of plsql plPerl, plTcl, plPython, plRuby, plJava, plR, ..... Standard modules pgcrypto, dblink, uuid-ossp, ltree, .... Transactional DDL Point In Time Recovery

Stored functions

C

Plpgsql

partial clone of plsql

plPerl, plTcl, plPython, plRuby, plJava, plR, .....

Standard modules

pgcrypto, dblink, uuid-ossp, ltree, ....

Transactional DDL

Point In Time Recovery

PostgreSQL 8.4 Released 1 July 2009 17 months in development Over 200 new features and improvements

Released 1 July 2009

17 months in development

Over 200 new features and improvements

PostgreSQL 8.4 killer features Common Table Expressions Window Functions Parallel Restore

Common Table Expressions

Window Functions

Parallel Restore

PostgreSQl 8.4 GBH features Column permissions Variadic functions Per database locales Significant performance improvements Version aware psql command

Column permissions

Variadic functions

Per database locales

Significant performance improvements

Version aware psql command

Common Table Expressions In SQL standard Put a query in a CTE and later treat it as a table with t as ( select a,b,c from foo ) select * from t; with t as ( select a,b,c from foo ), s as (select a,d,e from bar) select b,d from t,s where t.a=s.a;

In SQL standard

Put a query in a CTE and later treat it as a table

with t as ( select a,b,c from foo ) select * from t;

with t as ( select a,b,c from foo ), s as (select a,d,e from bar) select b,d from t,s where t.a=s.a;

Recursive CTEs with recursive f as (select 0::numeric as a, 1::numeric as b, 1::int as r union select b, a+b, r+1 from f where r < 100 ) select b from f where r = 100;  354224848179261915075

with recursive f as (select 0::numeric as a, 1::numeric as b, 1::int as r union select b, a+b, r+1 from f where r < 100 ) select b from f where r = 100;

 354224848179261915075

Transitive closure with CTEs with recursive ancestors as (select 1::int as gen, parent as anc, child as des from children union select gen+1, anc, child from ancestors join children on des = parent ) select gen, anc from ancestors where des = 'fred'; Forget nested sets similar monstrosities

with recursive ancestors as (select 1::int as gen, parent as anc, child as des from children union select gen+1, anc, child from ancestors join children on des = parent ) select gen, anc from ancestors where des = 'fred';

Forget nested sets similar monstrosities

Interesting effects with CTEs create or replace function hanoi (discs integer, move out integer, a out int[], b out int[], c out int[]) returns setof record language sql as $$ with recursive han as ( select 1::int as move, $1 as ndiscs, '{99}'::int[] || array_agg(discs)as a, '{99}'::int[] as b, '{99}'::int[] as c from generate_series($1,1,-1) as discs union all select move + 1 , ndiscs, hnext(move, ndiscs, a,b,c), hnext(move, ndiscs, b,c,a), hnext(move, ndiscs, c,a,b) from han where array_length(b,1) < ndiscs + 1 ) select move, a[2:$1+1] as a, b[2:$1+1] as b, c[2:$1+1] as c from han order by move $$; Select * from hanoi(4);

Results: move | a | b | c ------+-----------+-----------+--------- 1 | {4,3,2,1} | {} | {} 2 | {4,3,2} | {} | {1} 3 | {4,3} | {2} | {1} 4 | {4,3} | {2,1} | {} 5 | {4} | {2,1} | {3} 6 | {4,1} | {2} | {3} 7 | {4,1} | {} | {3,2} 8 | {4} | {} | {3,2,1} 9 | {} | {4} | {3,2,1} 10 | {} | {4,1} | {3,2} 11 | {2} | {4,1} | {3} 12 | {2,1} | {4} | {3} 13 | {2,1} | {4,3} | {} 14 | {2} | {4,3} | {1} 15 | {} | {4,3,2} | {1} 16 | {} | {4,3,2,1} | {} (16 rows)

Sneak Peak: CTE's in 8.5 with t as (delete from foo where bar returning *) select * from t; And similar for insert and update queries

with t as (delete from foo where bar returning *) select * from t;

And similar for insert and update queries

Window functions In SQL standard Similar to aggregates, but does not collapse rows All aggregate functions can be used as window functions

In SQL standard

Similar to aggregates, but does not collapse rows

All aggregate functions can be used as window functions

Window function examples select *, row_number() over (order by foo) as rownum from bar order by foo select *, row_number() over t as rownum from bar window t as (order by foo) order by foo (you can omit the outer order by, but it's best not to).

select *, row_number() over (order by foo) as rownum from bar order by foo

select *, row_number() over t as rownum from bar window t as (order by foo) order by foo

(you can omit the outer order by, but it's best not to).

More window function examples select salary, salary / avg(salary) over t, rank() over t from employees window t as ( partition by dept order by salary rows between unbounded preceding and unbounded following )

select salary, salary / avg(salary) over t, rank() over t from employees window t as ( partition by dept order by salary rows between unbounded preceding and unbounded following )

Major missing SQL features grouping sets merge

grouping sets

merge

Parallel pg_restore My humble contribution :-) Only works with custom format dumps Uses specified number of connections to the database Especially useful for partitioned databases Typical speedup is around number_of_processors/2 Sweet spot is between number_of_processors and 2 * number_of_processors

My humble contribution :-)

Only works with custom format dumps

Uses specified number of connections to the database

Especially useful for partitioned databases

Typical speedup is around number_of_processors/2

Sweet spot is between number_of_processors and 2 * number_of_processors

Parallel pg_restore continued Uses a separate connection for each step Individual steps are not parallelized Uses dependency information to make sure steps are done in right order Parallel clients are forked processes on *nix, threads on Windows pg_restore -d dbname -j 4 dumpfile

Uses a separate connection for each step

Individual steps are not parallelized

Uses dependency information to make sure steps are done in right order

Parallel clients are forked processes on *nix, threads on Windows

pg_restore -d dbname -j 4 dumpfile

Parallel pg_restore TODO Support tar format dumps Parallel pg_dump Needs snapshot cloning Needs new archive format (directory) Parallel COPY, index build

Support tar format dumps

Parallel pg_dump

Needs snapshot cloning

Needs new archive format (directory)

Parallel COPY, index build

A small digression Three monkeys on our back All being addressed

Three monkeys on our back

All being addressed

First monkey on our back No Upgrade in place Attempted for 8.4, but many caveats Parallel pg_restore some help Two efforts underway ... Core team committed to providing less painful upgrade mechanisms Makes it harder to make improvements in certain areas – can't just change on-disk format when we want

No Upgrade in place

Attempted for 8.4, but many caveats

Parallel pg_restore some help

Two efforts underway ...

Core team committed to providing less painful upgrade mechanisms

Makes it harder to make improvements in certain areas – can't just change on-disk format when we want

Replication Often a “box to be checked” Three forms: Statement replay Data replay via triggers Needs unique key Can cause foreign key constraint problems Log shipping Uses Write Ahead Log, for a whole cluster Transparent to applications

Often a “box to be checked”

Three forms:

Statement replay

Data replay via triggers

Needs unique key

Can cause foreign key constraint problems

Log shipping

Uses Write Ahead Log, for a whole cluster

Transparent to applications

The second monkey on our back No built-in management for log shipping Need third party tools or custom scripts to manage 8.5 feature (nearly): Streaming Replication Developed by NTT in Japan New special built in WALsender daemon After initial backup (rsync) all managed via config file settings Streams log records rather than waiting for complete WAL files – much lower latency

No built-in management for log shipping

Need third party tools or custom scripts to manage

8.5 feature (nearly): Streaming Replication

Developed by NTT in Japan

New special built in WALsender daemon

After initial backup (rsync) all managed via config file settings

Streams log records rather than waiting for complete WAL files – much lower latency

Third monkey on our back Can't read from WAL based replicas Useful for failover, useless for load balancing Many users thus have a combination of log shipping and trigger based replicas 8.5 feature (nearly): Hot Standby Makes WAL based replicas available for read-only queries Ideal for a report server, decision support etc. Take load off transaction processing system

Can't read from WAL based replicas

Useful for failover, useless for load balancing

Many users thus have a combination of log shipping and trigger based replicas

8.5 feature (nearly): Hot Standby

Makes WAL based replicas available for read-only queries

Ideal for a report server, decision support etc.

Take load off transaction processing system

Significant features Standard module citext: compares text case insensitively Column level privileges grant select(colname), update(colname) on tabname to rolename; Previously only way to do this was via a view In SQL Standard

Standard module citext: compares text case insensitively

Column level privileges

grant select(colname), update(colname) on tabname to rolename;

Previously only way to do this was via a view

In SQL Standard

Locales Locale is now per-database instead of per-cluster Can be set at time of createdb instead of initdb Ultimate goal is to be able to set locale and charset / encoding for each column Can involve performance issues

Locale is now per-database instead of per-cluster

Can be set at time of createdb instead of initdb

Ultimate goal is to be able to set locale and charset / encoding for each column

Can involve performance issues

Performance improvements suppress_redundant_updates_trigger Update normally writes a new record whether or not there is a change This trigger inhibits the update if the new and old records are identical Very fast (uses memcmp() on whole data area) Break even point is at worst around 30%, i.e. you're better off using it if more than 30% of the updates are in fact redundant. Also save vacuuming etc.

suppress_redundant_updates_trigger

Update normally writes a new record whether or not there is a change

This trigger inhibits the update if the new and old records are identical

Very fast (uses memcmp() on whole data area)

Break even point is at worst around 30%, i.e. you're better off using it if more than 30% of the updates are in fact redundant.

Also save vacuuming etc.

Performance improvements continued Relation “forks” Information about a relation stored out of line “visibility map” fork Reduces vacuum cost on tables that don't change a lot “free space map” fork Now stored per table and not in shared memory No more config settings required Infrastructure exists for other such forks if required. Hash indexes now much faster Still not crash safe

Relation “forks”

Information about a relation stored out of line

“visibility map” fork

Reduces vacuum cost on tables that don't change a lot

“free space map” fork

Now stored per table and not in shared memory

No more config settings required

Infrastructure exists for other such forks if required.

Hash indexes now much faster

Still not crash safe

SQL Features - Arrays array_agg() Turn rows into array entries unnest() Turn array entries into rows Opposite of array_agg()

array_agg()

Turn rows into array entries

unnest()

Turn array entries into rows

Opposite of array_agg()

SQL Features - Views CREATE OR REPLACE VIEW can now add columns at the end

CREATE OR REPLACE VIEW can now add columns at the end

SQL Features – limit/offset No longer require a static expression, can now take a volatile expression or subquery instead SQL Standard syntax now supported: OFFSET start [ ROW | ROWS ] FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

No longer require a static expression, can now take a volatile expression or subquery instead

SQL Standard syntax now supported:

OFFSET start [ ROW | ROWS ]

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

Function features Variadic functions CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$ SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL; select mleast(1,4,9,-6,2,5); Result: -6

Variadic functions

CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$

SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);

$$ LANGUAGE SQL; select mleast(1,4,9,-6,2,5);

Result: -6

More Function features Default argument values CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3) RETURNS int LANGUAGE SQL AS $$ SELECT $1 + $2 + $3; $$; select foo(10,20,30); -- 60 select foo(10,20); -- 33 select foo(10); -- 15 Previously required overloading to get same effect.

Default argument values

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3) RETURNS int LANGUAGE SQL AS $$ SELECT $1 + $2 + $3; $$; select foo(10,20,30); -- 60 select foo(10,20); -- 33 select foo(10); -- 15

Previously required overloading to get same effect.

PL/PGSQL features Pass parameters to dynamic query EXECUTE ... USING ... CASE statement

Pass parameters to dynamic query

EXECUTE ... USING ...

CASE statement

psql features version-aware queries Now makes the right queries to suit the server it's connected to ef function_name

version-aware queries

Now makes the right queries to suit the server it's connected to

ef function_name

Questions?

Add a comment

Related pages

trilug.org

PostgreSQL 8.4 featuresAndrew Dunstanandrew@dunslane.net andrew.dunstan@pgexperts.comTopicsGeneral info and historyPostgreSQL general features (briefly ...
Read more

News

News SB 15.20 :: Burg in Grub2 erlaubt Authentication-Bypass 2015.12.15 . Eine Lücke im Linux-Bootloader GRUB2 erlaubt es einem lokalen Angreifer ...
Read more

winigd32.rom bei Systemstart

winigd32.rom bei Systemstart Hi, seit geraumer Zeit kommt bei mir bei Systemstart die Meldung "winigd32.rom wurde nicht gefunden".
Read more

Bundespolizei Trojaner

Hallo liebes Team, hier ist mal wieder der Carsten, ich hatte vor einem halben Jahr schonmal ein Problem mit meinem Laptop hier beschrieben und hoffe, auch ...
Read more