Upcoming PostgreSQL Features - Bruce Momjian

offbeatlossΔιαχείριση Δεδομένων

22 Νοε 2012 (πριν από 4 χρόνια και 7 μήνες)

321 εμφανίσεις

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