When an API or database returns a large list, pagination lets a client fetch it in smaller pages. With offset pagination, the client requests a page by specifying…
Read articleWhen 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 articleWhen you add a composite index to a MySQL table, the order of its columns determines which queries can use it efficiently. A composite index is one index…
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 MySQL database responds slowly, the SQL statements taking the most time are a useful place to investigate. The slow query log records statements that exceed a…
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 articleWhen 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 articleDuring 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 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 article