- The Internals of PostgreSQL : Introduction
- Release discussions: 9.6
- PostgreSQL 10 New Features With Examples
- Mastering PostgreSQL in Application Development launches!
Full Text search
- Postgres full-text search is Good Enough! by Rachid Belaid, Jul 2015. this one progressively illustrate some of the full-text search features in Postgres .
- Fast Full-Text Search in PostgreSQL by Austin G. Walters, Aug 2018.
- another example using FTS with ecto (elixir) Jan 2018.
Applications on top of PostgreSQL
Derek Sivers writes a shopping card entirely using PG functions:
- Simplify: move code into database functions | Derek Sivers
- PostgreSQL example of self-contained stored procedures | Derek Sivers
Also see stored procedures page
- Why Upgrade PostgreSQL? tool tells you what you’ll gain by upgrading between any two version. Excellent!
PgTune “Configuration calculator for PostgreSQL PGTune calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration. It isn’t a silver bullet for the optimization settings of PostgreSQL. Many settings depend not only on the hardware configuration, but also on the size of the database, the number of clients and the complexity of queries, so that optimally configure the database can only be given all these parameters.”
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
- Multicorn – a PostgreSQL 9.1+ extension meant to make Foreign Data Wrapper development easy, by allowing the programmer to use the Python programming language.
- rach/pome A Postgres Metrics Dashboard written in Go
- JSONB query example as a SQLFiddle.
- How-To Initialize PostgreSQL in OS X? | Kevin Deldycke [Oct 2016]
- Tuning a brand new pgsql server
- Importing CSV into PostgreSQL
- About Pool Sizing · brettwooldridge/HikariCP Wiki; HN
- How to Manage Connections Efficiently in Postgres, or Any Database — Brandur Leach
Behind the scenes
- Following a Select Statement Through Postgres Internals - Pat Shaughnessy
- EXPLAIN explained
- Postgresql locking revealed
- Introduction to PostgreSQL physical storage
- Debugging PostgreSQL performance, the hard way · JustWatch Tech Blog
- How Twitch uses PostgreSQL; details of using with AWS, HAProxy, Failover, multi-available zones, ELB, health checks, user roles, expensive query protection, PGBouncer, Routing, Problems – (MVCC, connections, Major upgrades).
- Is PostgreSQL good enough?; using PG where you typically might reach for redis, ES, InfluxDB, S3, Celery, Kafka, Mongo, SQLite, Neo4J.
- Understanding caching in Postgres - An in-depth guide | Madusudanan
; via - Data Egret: Deep dive into postgres stats: pg_stat_database
- (1) Tuning PostgreSQL for High Write Workloads - YouTube
- The Internals of PostgreSQL : Introduction
- postgres cluster management system in GoCardless · Hironobu SUZUKI @ InterDB
- gocardless/our-postgresql-setup: PostgreSQL clustering with corosync/pacemaker test environment
- Zero-downtime Postgres Upgrades - YouTube
- Patroni “is a template for you to create your own customized, high-availability solution using Python and - for maximum accessibility - a distributed configuration store like ZooKeeper, etcd, Consul or Kubernetes. Database engineers, DBAs, DevOps engineers, and SREs who are looking to quickly deploy HA PostgreSQL in the datacenter-or anywhere else-will hopefully find it useful.”
Archival and restoration
pgloader loads data into PostgreSQL and allows you to implement Continuous Migration from your current database to PostgreSQL. pgLoader has two modes of operation. It can either load data from files, such as CSV or Fixed-File Format; or migrate a whole database to PostgreSQL. pgLoader supports several RDBMS solutions as a migration source, and fetches information from the catalog tables over a connection to then create an equivalent schema in PostgreSQL. This means that you can migrate to PostgreSQL in a single command-line! White Paper. “The pgloader tool is meant to allow one to implement the Continuous Migration project methodology when migrating to PostgreSQL. This methodology is meant to reduce risks inherent to such complex projects.”
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:
- replace nvl with coalesce
- replace rownum <= 1 with LIMIT 1
- replace listagg with string_agg
- replace recursive hierarchy (start with/connect by/prior) with recursive
- replace minus with except
- replace SYSDATE with CURRENT_TIMESTAMP
- replace trunc(sysdate) with CURRENT_DATE
- replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date
- replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with ‘-infinity’::date
- remove dual table references (not needed in postgres)
- replace decode with case statements
- replace unique with distinct
- replace to_number with ::integer
- replace mod with % operator
- replace merge into with INSERT … ON CONFLICT… DO UPDATE/NOTHING
- change the default of any table using sys_guid() as a default to gen_random_uuid()
- oracle pivot and unpivot do not work in postgres - use unnest
- ORDER BY NLSSORT(english, ‘NLS_SORT=generic_m’) becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops)
- Oracle: uses IS NULL to check for empty string; in postgres, empty string and null are different
- If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column.
- PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A
- any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))
- Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including ‘null’ - don’t bother trying to use null=”” it won’t work). Concatenating a NULL with a NOT NULL will result in a NULL.
- Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null.
- For sequences, instead of .nextval use nextval(‘’)
- Awesome PostgreSQL – list of things related to PG.
- Postgres Weekly is a weekly newsletter about PostgresSQL.
- [1901.01973] Looking Back at Postgres by Joseph M. Hellerstein
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
- RethinkDB versus PostgreSQL: my personal experience › SageMathCloud Blog this is a good battle-ground story!
- What are some bad things about PostgreSQL? : /r/postgresql
- What features does PG have that MySQL does not - HN - Sep 2016.
- the ultimate postgres vs mysql blog post | dian m fay
- transactional DDL
- hstore/jsonb/array/composite types
- partial index
- index on expression
- integrated full text search
- table inheritance for partionning
- text columns
Migrating from RethinkDB to Postgres — An Experience Report by Phil Freeman of Purescript; HN [Sep 2017]
- 5 Novelties in PostgreSQL 10 That Will Make You a Happier Developer - 10Clouds
- Standard SQL Features Where PostgreSQL Beats its Competitors [Feb 2018]
- Updating a 50 terabyte PostgreSQL database : programming [Mar 2018]
See also: Indy big data meetup notes on PostgreSQL