Upcoming PostgreSQL Features - Bruce Momjian

offbeatlossData Management

Nov 22, 2012 (4 years and 10 months ago)

334 views

Upcoming PostgreSQL Features
BRUCE MOMJIAN
January,2012
This outlines the upcoming features of PostgreSQL 8.4.
Creative Commons Attribution License http://momjian.us/presentations
Upcoming PostgreSQL Features 1/17
PostgreSQL Evolution
1996
1998
2001
2009
1986
13 Years
Crash EnterpriseSQL Standards
Enterprise features include:

Improved Performance

Simplified Maintenance and Administration

Efficient 24/7 Operation
Upcoming PostgreSQL Features 2/17
Column-Level Permissions
GRANT SELECT (
col1
),INSERT (
col2
),UPDATE (
col3
)
ON sales_activity TO salesteam;
Upcoming PostgreSQL Features 3/17
Windowing Functions:Sum
SELECT depname,empno,salary,
sum(salary) OVER (PARTITION BY depname)
FROM empsalary
ORDER BY depname,salary;
depname | empno | salary | sum
-----------+-------+--------+-------
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
develop | 11 | 5200 | 25100
develop | 10 | 5200 | 25100
develop | 8 | 6000 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
sales | 3 | 4800 | 14600
sales | 4 | 4800 | 14600
sales | 1 | 5000 | 14600
(10 rows)
Upcoming PostgreSQL Features 4/17
Windowing Functions:Rank
SELECT depname,empno,salary,
rank() OVER (PARTITION BY depname ORDER BY salary)
FROM empsalary;
depname | empno | salary | rank
-----------+-------+--------+------
develop | 7 | 4200 | 1
develop | 9 | 4500 | 2
develop | 11 | 5200 | 3
develop | 10 | 5200 | 3
develop | 8 | 6000 | 5
personnel | 5 | 3500 | 1
personnel | 2 | 3900 | 2
sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
sales | 1 | 5000 | 3
(10 rows)
Upcoming PostgreSQL Features 5/17
WITHQueries:Simple
WITH tab1(x) AS (SELECT 23 UNION SELECT 55)
SELECT * FROM tab1;
x
----
23
55
(2 rows)
Upcoming PostgreSQL Features 6/17
WITHQueries:Recursive
WITH RECURSIVE tab1(x) AS
(SELECT 1 UNION ALL SELECT x + 1 FROM tab1 WHERE x < 10)
SELECT * FROM tab1;
x
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
Upcoming PostgreSQL Features 7/17
Parallel Restore of Dumps
$ pg_restore
--multi-thread=4
db.dump
Upcoming PostgreSQL Features 8/17
Visibility Maps Reduce Vacuum Overhead
AA
D
T
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
A
T
A
D
Pre−8.4
8.4+
Upcoming PostgreSQL Features 9/17
No Need for Free-Space Map Configuration
Removed from postgresql.conf:
#max_fsm_pages = 204800#min max_fsm_relations*16,6 bytes each
#(change requires restart)
#max_fsm_relations = 1000#min 100,˜70 bytes each
#(change requires restart)
Upcoming PostgreSQL Features 10/17
Prefetch I/O Using posix_fadvise()
Disk 1 Disk 2 Disk 3
SELECT ...
RAID
Upcoming PostgreSQL Features 11/17
Per-Database Locales
CREATE DATABASE newdb
WITH ENCODING ’UTF8’
COLLATE ’en_GB.UTF8’
CTYPE ’en_GB.UTF8’;
Upcoming PostgreSQL Features 12/17
Default Values for Function Arguments
CREATE FUNCTION magical(int,int
DEFAULT 42
)...
Upcoming PostgreSQL Features 13/17
Other Improvements

Safe termination of individual sessions

Full text search (GIN) performance improvements
Upcoming PostgreSQL Features 14/17
Possible 8.4 Features

In-Place upgrades with pg_migrator
Upcoming PostgreSQL Features 15/17
Possible 8.5 Features

SE-Linux and SQL row-level permissions

Read-Only queries on continuous archiving slaves (hot
standby)

Streaming continuous archiving (log streaming)

Index-only scans (covering index)

Simplified table partitioning

Automatically updatable views

Views with CHECK OPTION

Autotuning wizard
Upcoming PostgreSQL Features 16/17
Questions?
http://momjian.us/presentations
Upcoming PostgreSQL Features 17/17