When a busy PostgreSQL table needs an index, a structure that helps PostgreSQL find rows, use CREATE INDEX CONCURRENTLY to let inserts, updates, and deletes continue while PostgreSQL…
Read articlePostgreSQL defaults to READ COMMITTED. A transaction groups statements that should succeed or fail together. Under READ COMMITTED, each SQL statement sees committed data as other requests change…
Read articleWhen a production PostgreSQL table needs a new value for existing rows, update a limited set of rows at a time and commit each batch. This update of…
Read articleOn a PostgreSQL table serving a live web application, a regular index build can cause an outage because it holds a lock while PostgreSQL scans the table and…
Read articleWhen a PostgreSQL table stores several events for each account, you may need the newest event for each account. DISTINCT ON is PostgreSQL’s concise way to choose one…
Read articleA PostgreSQL column that allows missing values is nullable. A unique constraint on that column normally allows multiple rows with NULL. If your data rules allow only one…
Read articleIn a PostgreSQL category table, each category can store its parent’s ID in the same table. To get a category and all the categories below it, PostgreSQL follows…
Read articleWhen a PostgreSQL query runs slowly, its EXPLAIN ANALYZE output shows how PostgreSQL ran it and how many rows each step processed. A query plan is a tree…
Read articleWhen a PostgreSQL table has many rows and queries often target a smaller group, a partial index helps PostgreSQL find those rows without indexing the whole table. A…
Read articleWhen you add an index to a PostgreSQL table serving production traffic, PostgreSQL blocks writes to the table until a regular CREATE INDEX finishes. CREATE INDEX CONCURRENTLY lets…
Read articlePostgreSQL uses multiversion concurrency control, or MVCC, to let transactions read consistent data while other transactions change it. When a transaction updates or deletes a row, PostgreSQL keeps…
Read article