SShortSingh.
TopTechnologyAI & MLStartupsProgrammingScienceBusinessFinanceCrypto & Web3World
Back to feed
Programming

Why a project_id index fails 'latest 20 issues' queries on large projects in PostgreSQL

0
·1 views

A developer found that a common 'newest 20 issues per project' query scanned about 200,000 rows for their largest project even with a project_id index. Benchmarks on PostgreSQL 16 with 2 million issues showed the plain index touching 1,941 buffers on the big project versus 23 with a composite index. The problem is that the index finds the project's rows but cannot provide date order, forcing a full fetch and sort that scales with project size. A composite index on (project_id, created_at DESC, id DESC) lets Postgres read the newest rows directly and stop after 20. The author also advises building the index with CONCURRENTLY and benchmarking against the largest tenant.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Log in to join the discussion and vote.

Log in

Related stories

0
ProgrammingDEV Community ·

Zwischenzug app unlocks Lichess games only after a player's pulse returns to baseline

Zwischenzug is an open-source desktop app for macOS and Windows that adds a cooldown to online chess after a loss. It embeds Lichess and locks new games following a defeat, using the Lichess public API to detect losses. With the user's consent, the webcam measures pulse between games, and play resumes once the heart rate stays near a personal baseline, with a timer fallback if no usable signal is available. The developer cites an analysis of 11.3 million Lichess games suggesting that rating performance drops after losses and that a 30-minute break removes the effect, though the study cannot separate tilt from ordinary off days. The camera is off during play, video stays on the user's machine, and telemetry is disabled.

0 comments Read more at DEV Community
0
ProgrammingDEV Community ·

Developer shares self-deleting PHP deploy workers for a 1,000-page site with no SSH access

The author runs The DJ Calendar, roughly 1,000 PHP/MySQL pages on shared cPanel hosting without shell access, SSH, or a deployment pipeline. Their hosting caches HTML for five minutes and opcache serves stale output briefly after writes, making manual code pasting through a browser file manager risky. To address this, they built a worker pattern: a single PHP file uploaded to the web root and triggered once with a secret token, which applies the change and then deletes itself. The approach includes safeguards such as verifiers that check exactly what a patch writes, with automatic restoration on failure, and an 18-rule pre-flight checklist. The author open-sourced the project under the MIT license and invites readers to share their own techniques for working safely with no shell access during a production incident.

0 comments Read more at DEV Community
0
ProgrammingDEV Community ·

Magento 2 module lists each cart discount rule on its own line with customer-facing labels

A developer found that Magento 2 merges all applied cart discounts into a single totals row, and that the bracketed label can pull text from a rule's internal description field, which may expose internal notes to customers. Because Magento stores only a combined discount description and not per-rule amounts, the split cannot be rebuilt after an order is placed. The developer therefore built a module that records each rule's label and amount at order creation, showing one line per rule. Labels come from customer-facing store labels or coupon codes rather than rule descriptions or names. The approach was tested on a clean Mage-OS installation, with the rule description appearing in a customer email in the unmodified setup.

0 comments Read more at DEV Community
0
ProgrammingDEV Community ·

Brazil's regulators set strict rules for lawyer-bank AI partnerships

Joint go-to-market efforts between Brazilian lawyers and banks must follow rules set by the Central Bank of Brazil and the Brazilian Bar Association, with ethics, data sovereignty and client confidentiality at the center. Regulators reportedly treat such collaborations as high-risk, requiring pre-deployment validation, registration in the central bank's AI monitoring system, and lawyer responsibility for final legal conclusions. AI systems used in these settings must keep detailed logs, support traceability, and include mandatory source attribution and confidence scoring. Shared liability can apply to both the bank and the law firm if an AI tool misclassifies a clause and the error is endorsed.

0 comments Read more at DEV Community