Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

5 Cool Things About PostgreSQL

No description
by

rob martin

on 30 September 2011

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of 5 Cool Things About PostgreSQL

5 Cool Things About PostgreSQL 1. 2. 3. 4. 5. The History and Structure
of the PostgreSQL Project Operators and
Regular Expressions Views With Recursive PostgreSQL v9.1 a. The project has been around since 1986.

That's 25 years.

By comparison, Apache has been around since
1996. GCC, since 1988. GNU Emacs has been active
since April of 1985.

b. PostgreSQL is licensed under a BSD or MIT style license.

Here it is:

PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2010, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. c. The PostgreSQL project falls under a not-for-profit foundation called the "PostgreSQL Global Development Group".

It's made up of a federation of companies who advocate for PostgreSQL as free software. Some of the core companies
include PsotgreSQL Experts Inc. in San Francisco, F-Secure in Helsinki, Redpoll Linpro in Stockholm, Red Hat in Pittsburgh,
and Enterprise DB in Philadelphia. These companies have unmatched reputations with PostgreSQL of course.

I call this a "community open source business model".

Past contributors include Julian Assange of Wikileaks. He also has a reputation.

I think the extended PostgreSQL development team look a lot like a BarCamp crew. d. PostgreSQL is big enough for some pretty impressive applications. a. IS

expression IS NULL
expression IS NOT NULL
expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression b. Cool math operators
and functions

% modulo
|/ square root
|// cube root
! factorial
@ absolute value
round()
ceiling()
floor()
c. Cool string operators
and functions

string || string : concatenate
lower(), upper() : change case
encode(), decode() : base64 and hex
md5() : hash
translate() : like tr/
d. Pattern matching and regular expressions

LIKE and ILIKE (a.k.a. ~~ and ~~*)
NOT LIKE and NOT ILIKE (!~~ and !~~*)
_ and % match single and multiple wildcard

SIMILAR TO uses regular expressions
to match strings

~ and ~* Match regular expression (*case insensitive)
!~ and !~* Don't match regular expression

regexp_match() and regexp_replace(), greedy and non-greedy
regexp_split_to_table() and regexp_split_to_array()

[ ... ] and [^ ... ] for sets of letters create table parties (
id integer,
name text
); create table addresses (
id integer,
party_id integer,
street text,
city text,
state text,
zip text
); create table phones (
id integer,
party_id integer,
phone_number string
); create view contact as
select
p.name as name,
a.street || ', ' || a.city || ', ' || a.state || ' ' || a.zip as address,
t.phone_number as phone
from parties p, addresses a, phones t
where p.id = a.party_id and p.id=t.party_id;
copy (select * from contact) to STDOUT with CSV;
Rob Martin,"706 S 22nd St, Milwaukee, Wisconsin 53204",414-678-1822
Barbara Handley,"706 S 22nd St, Milwaukee, Wisconsin 53204",414-678-1329
Amanda F Palmer,"1 Bitter Lane, Coldness, Minnesota ",unlisted
Neil Gaiman,"1 Bitter Lane, Coldness, Minnesota ",unlisted


Please excuse the copy (select ...). Prezi doesn't handle tabular data well. } create table ia (
id integer,
parent_id integer,
page text
); insert into ia values (...);
insert into ia values (...);
...
insert into ia values (...); copy (select * from ia) to stdout with csv;
1,0,Home
2,1,Products
3,1,Services
4,1,About
5,1,Privacy Policy
6,1,Site Map
8,7,About Our Founder
7,4,About us
9,8,About Our Founder's Ego

Time: 0.361 ms create view crumbs as
with recursive page(id, long) as (
select ia.id, ia.page::text as long from ia where id = 1
union
select ia.id, page.long || ' / '::text || ia.page::text from ia, page where ia.parent_id = page.id
)
select page.id, page.long from page; copy (select * from crumbs) to stdout with csv;
1,Home
2,Home / Products
3,Home / Services
4,Home / About
5,Home / Privacy Policy
6,Home / Site Map
7,Home / About / About us
8,Home / About / About us / About Our Founder
9,Home / About / About us / About Our Founder / About Our Founder's Ego

Time: 0.755 ms
> > } a. Synchronous replication

You can require, on a per transaction basis (i.e. when you damn well feel like it)
that PostgreSQL consider a commit complete only when it's been written to the
master database plus one slave. b. Unlogged tables

When the data is ephemeral but too big for RAM, you can put it in a new "unlogged table".

create table myloggedtable (a int);
create unlogged table myunloggedtable (a int);
create index x_logged on myloggedtable(a);
create index x_unlogged on myunloggedtable(a);
insert into myloggedtable select generate_series(1,10000000);
Time: 17601.201 ms
insert into myunloggedtable select generate_series(1,10000000);
Time: 3439.982 ms
c. SE-Postgres

Full integration with SE-Linux for military-grade security.

d. Writeable Common Table Expressions

'WITH' gets more powerful:

with deleted as (
delete from mytable where words like '%yellow%' returning words
) insert into myarchivetable (select * from deleted); e. Stuff gets faster, some of it anyway

create table test1 (a int);
create table test2 (a int);
insert into test1 select generate_series(1,100000);
insert into test2 select generate_series(100,1000);
select * from test1 full outer join test2 using (a) limit 10

PostgreSQL 9.0: 330 ms
PostgreSQL 9.1: 3 ms f. Some of the more esoteric stuff

Per column collations: You can choose a linguistically-correct sort collation on a
per-column basis rather than per-database.

Serializable Snapshot Isolation: Keeps concurrent transactions consistent without blocking.

K-Nearest-Neighbor Indexing: Blazingly fast pattern and distance matching using the KNN algorithm.
This has some spillover benefit to LIKE and ILIKE also.

Foreign Data Wrappers: Attach and query other databases from PostgreSQL.
Full transcript