PostgreSQL

#database-systems, postgresql

Learn

Applications on top of PostgreSQL

Derek Sivers writes a shopping card entirely using PG functions:

Also see stored procedures page

Tools

pgcenter provides convenient interface to this statistics and allow viewing statistics changes in time interval, eg. per second. The pgcenter provides fast access for database management task, such as editing configuration files, reloading services, viewing log files and canceling or terminating database backends (by pid or using state mask). However if need execute some specific operations, pgcenter can start psql session for this purposes.

pgsqlstat: report top-level postgres stats
pgsqlslower: print details about queries taking longer than N milliseconds
pgsqlslowest: print details about N slowest queries
pgsqllat: print details about query latency distribution
pgsqltxslower: print details about transactions taking longer than N milliseconds
pglockwaits: print counts of events where Postgres blocked waiting for a lock

JSONB

Howto stuff

Pooling

Behind the scenes

A graphical overview of Postgres ecosystem; via - Data Egret: Deep dive into postgres stats: pg_stat_database

Performace tuning

High availability

Migrations

This comment by user irrational is very useful:

We’ve been working on migrating from Oracle to Postgres for a few years now. We are about 2 weeks from being finished. It is not for the faint of heart, but it is totally worth it. The documentation is much much better, performance is equivalent or better, the sql dialect is saner, etc. Other than moving the data itself (ora2pg was invaluable for this), rewriting the queries is what has taken the most amount of time. Some of our tips on differences between oracle and postgres sql:


Tutorials

Resources

Papers

This is a recollection of the UC Berkeley Postgres project, which was led by Mike Stonebraker from the mid-1980’s to the mid-1990’s. The article was solicited for Stonebraker’s Turing Award book, as one of many personal/historical recollections. As a result it focuses on Stonebraker’s design ideas and leadership. But Stonebraker was never a coder, and he stayed out of the way of his development team. The Postgres codebase was the work of a team of brilliant students and the occasional university “staff programmers” who had little more experience (and only slightly more compensation) than the students. I was lucky to join that team as a student during the latter years of the project....

Articles / discussions


See also: Indy big data meetup notes on PostgreSQL

References
Missing citation