How to Get the Latest Row per Group in PostgreSQL with DISTINCT ON
When 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 articleArticles
Page 3 of 4.
How to Insert or Update Rows in MySQL with ON DUPLICATE KEY UPDATE
When you insert a row into a MySQL table, you may need to add it if no row has its key, or update the row that already has…
Read articleHow to Make a Database Migration Backward Compatible
During a deployment, old and new versions of an application run at the same time. Some web servers already run the new code while others still run the…
Read articleHow to Prevent Duplicate NULL Values with PostgreSQL UNIQUE NULLS NOT DISTINCT
A 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 articleHow to Query a Category Tree in PostgreSQL with a Recursive CTE
In 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 articleHow to Read PostgreSQL EXPLAIN ANALYZE Output
When 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 articleHow to Use Partial Indexes in PostgreSQL
When 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 articleMonitoring PostgreSQL Index Builds With Progress Views
When PostgreSQL creates an index on a database that is still serving queries, pgstatprogresscreateindex shows the current stage and counters that track work during that stage. A stage,…
Read articleReducing 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 articleRow Locks vs. Table Locks in MySQL InnoDB
When a query in MySQL's InnoDB storage engine makes another query wait, InnoDB usually locks entries in an index that the first query scanned, or a span of…
Read articleTransaction and Locking Differences Between PostgreSQL and MySQL
PostgreSQL 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 articleUsing PostgreSQL Advisory Locks for Application Coordination
When 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 article