Reducing Lock Contention With PostgreSQL Batch Updates
When you backfill a large PostgreSQL table, you update values in existing rows. Change a limited number of rows that meet the update condition in each transaction, then…
Read articlePage 5 of 6.
When you backfill a large PostgreSQL table, you update values in existing rows. Change a limited number of rows that meet the update condition in each transaction, then…
Read articlePostgreSQL and MySQL with InnoDB both support ACID transactions: transactions that preserve data integrity while grouping changes so they commit together or roll back together. They differ in…
Read articleWhen a database transaction reads or changes rows, locks determine whether other transactions must wait before they can make conflicting changes. MySQL’s InnoDB engine and PostgreSQL both use…
Read articleWhen two or more workers that use PostgreSQL need to run the same job or update the same named resource, an advisory lock lets them coordinate. The application…
Read articleWhen you change the structure of a PostgreSQL database while application instances are running, the expand and contract pattern lets old and new application versions work at the…
Read articleTo check whether PostgreSQL uses an index for a query, run EXPLAIN (ANALYZE, BUFFERS) and read the plan for the table that index covers. Confirm that PostgreSQL chose…
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 articleIf PostgreSQL stores your application data and you want to run reports or dashboards on it, you may not need a separate data warehouse. A data warehouse is…
Read articleWhen a PostgreSQL query searches inside a JSON document, array, or full-text document, a standard B-tree index often does not fit the search. B-trees work well with single…
Read articleFor most PostgreSQL queries that look for an exact value, check whether values fall within a span, or return rows in order, start with a B-tree index. A…
Read articleWhen a PostgreSQL query searches inside an array or JSONB document, or looks for terms in full-text search data, a GIN index can find rows that match the…
Read article