PostgreSQL talk, Database 2011 conference

PostgreSQL: So powerful, you won’t believe it’s open source Reuven M. Lerner • reuven@lerner.co.il Database 2011 January 13th, 2011

Who am I? • Web developer, software architect, consultant • Linux Journal columnist since 1996 • Mostly Ruby on Rails + PostgreSQL, but also Python, PHP, jQuery, and lots more... • PostgreSQL user since (at least) 1997

Lots of options! • Oracle • Microsoft SQL Server • IBM DB2 • MySQL • PostgreSQL

How do you choose? • Integrity (ACID compliance) • Data types • Functionality • Tools • Extensibility • Documentation • Community

PostgreSQL • Very fast, very scalable. (Just ask Skype.) • Amazingly ﬂexible, easily extensible. • Rock-solid — no crashes, corruption, security issues for years • Ridiculously easy administration • It also happens to be free (MIT/BSD)

Brief history • Ingres (Stonebreaker, Berkeley) • Postgres (Stonebreaker, Berkeley) • PostgreSQL project = Postgres + SQL • About one major release per year • Version 8.x — Windows port, recovery • Version 9.0 — hot replication, upgrades

Data types • Boolean • Numeric (integer, ﬂoat, decimal) • (var)char, text (inﬁnitely large), binary • sequences (guaranteed unique) • Date/time and time intervals • IP addresses, XML, enums, arrays

Or create your own! CREATE TYPE Person AS (first_name TEXT, last_name TEXT);

Or create your own! CREATE TYPE Person AS (first_name TEXT, last_name TEXT); CREATE TABLE Members (group_id INTEGER, member Person);

PostGIS • Some people took this all the way • Want to include geographical information? • No problem — we’ve got PostGIS! • Complete GIS solution, with data types and functions • Keeps pace with main PostgreSQL revisions

Flexible internal types • For example, text/binary storage • Inline or in a separate table (TOAST) • Compressed or not ALTER TABLE Foo ALTER COLUMN mytext SET STORAGE EXTERNAL;

Foreign keys that work CREATE TABLE DVDs (id SERIAL, title TEXT, store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('Attack of the Killer Tomatoes', 500);

Foreign keys that work CREATE TABLE DVDs (id SERIAL, title TEXT, store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('Attack of the Killer Tomatoes', 500); ERROR: insert or update on table "dvds" violates foreign key constraint "dvds_store_id_fkey"

Foreign keys that work CREATE TABLE DVDs (id SERIAL, title TEXT, store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('Attack of the Killer Tomatoes', 500); ERROR: insert or update on table "dvds" violates foreign key constraint "dvds_store_id_fkey" DETAIL: Key (store_id)=(500) is not present in table "stores".

Foreign keys that work CREATE TABLE DVDs (id SERIAL, title TEXT, store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('Attack of the Killer Tomatoes', 500); ERROR: insert or update on table "dvds" violates foreign key constraint "dvds_store_id_fkey" DETAIL: Key (store_id)=(500) is not present in table "stores". ERROR: insert or update on table "dvds" violates foreign key constraint "dvds_store_id_fkey"

Foreign keys that work CREATE TABLE DVDs (id SERIAL, title TEXT, store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('Attack of the Killer Tomatoes', 500); ERROR: insert or update on table "dvds" violates foreign key constraint "dvds_store_id_fkey" DETAIL: Key (store_id)=(500) is not present in table "stores". ERROR: insert or update on table "dvds" violates foreign key constraint "dvds_store_id_fkey" DETAIL: Key (store_id)=(500) is not present in table "stores".

Custom validity checks CREATE TABLE DVDs (id SERIAL, title TEXT check (length(title) > 3), store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('AB', 500);

Custom validity checks CREATE TABLE DVDs (id SERIAL, title TEXT check (length(title) > 3), store_id INTEGER REFERENCES Stores); INSERT INTO DVDs (title, store_id) VALUES ('AB', 500); ERROR: new row for relation "dvds" violates check constraint "dvds_title_check"

Timestamp vs. Interval testdb=# select now(); now ------------------------------- 2010-10-31 08:58:23.365792+02 (1 row) Point in time testdb=# select now() - interval '3 days'; ?column? ------------------------------- 2010-10-28 08:58:28.870011+02 Difference between (1 row) points in time

Built-in functions • Math • Text processing (including regexps) • Date/time calculations • Conditionals (CASE, COALESCE, NULLIF) for use in queries • Extensive library of geometrical functions

Or write your own! • PL/pgSQL • PL/Perl • PL/Python • PL/Ruby • PL/R • PL/Tcl

Triggers • Write a function, and use it as a trigger • The function can run before or after inserts, deletes, or updates • What do they do? • Stop certain actions from happening • Change the data before it’s really used

Windowing functions • Calculate aggregate amounts on each row, taking other rows into consideration • For example: Rank each person’s salary within their department

Smart indexing • Functional indexes CREATE UNIQUE INDEX on People(lower(email_address)); • Conditional indexes CREATE INDEX on People WHERE is_admin = true; • Background (concurrent) indexing

Transactions • In PostgreSQL from the beginning • Use transactions for just about anything: BEGIN DROP TABLE DVDs; ROLLBACK; SELECT * FROM DVDs; -- Works!

Savepoints (or, sub-transactions) BEGIN; INSERT INTO table1 VALUES (1); SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (2); ROLLBACK TO SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (3); COMMIT;

MVCC • Readers and writers don’t block each other • “Multi-version concurrency control” • xmin, xmax on each tuple; rows are those tuples with txid_current between them • Old versions stick around until vacuumed • Autovacuum removes even this issue

Full-text indexing • Built-in, Unicode-aware full-text indexing has been in PostgreSQL for a few years • It’s simply a different type of index, as well as a different search operator (@@) • Custom dictionaries for different languages, indicating stop words and stemming rules

CTEs • Adds a “WITH” statement, which deﬁnes a sorta-kinda temp table • You can then query that same temp table • Makes many queries easier to read, write, without a real temp table • Better yet: CTEs can be recursive, for everything from Fibonacci to org charts

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;

PITR • Store WALs (write-ahead logs) to disk • Normally, the server will apply all WALs when it starts up • By choosing which WALs to apply, you can bring the database up to date until a certain point in time

Streaming replication • In 8.4, you could use “log shipping” to send the WALs from one server to another • When the primary server went down, you could bring up this secondary • In 9.0, this can happen over the network • Faster, more reliable, and easier to conﬁgure than previously

Hot standby • In 8.4, the secondary server could not answer queries when it was receiving WALs from the primary • 9.0 introduced “hot standby”: Secondary server can answer read-only queries • Can help with balancing certain queries

It’s all in the tables • Databases are good at working with tables • So PostgreSQL stores info in tables • Conﬁguration parameters • Objects (tables, indexes, functions, etc.) • Statistics for the optimizer • Look through pg_catalog ... and learn!

Client libraries • libpq (in C) • Java (JDBC) • Others by 3rd • .NET (npgsql) parties: • ODBC • Python • JavaScript (!) • Ruby • Just about any language you can • Perl imagine

Want to learn more? • Mailing lists, wikis, and blogs • All at http://postgresql.org/ • http://planetpostgresql.org • PostgreSQL training, consulting, development, hand-holding, and general encouragement

Thanks! (Any questions?) reuven@lerner.co.il http://www.lerner.co.il/ 054-496-8405 “reuvenlerner” on Skype/AIM

PostgreSQL talk, Database 2011 conference

More Related Content

Viewers also liked

Similar to PostgreSQL talk, Database 2011 conference

More from Reuven Lerner

Recently uploaded

PostgreSQL talk, Database 2011 conference

Editor's Notes