Poor database indexing caused 'not found' API queries to take up to 30 seconds

·93 views

A maritime tracking API was found to be dramatically slower when returning empty results than when returning actual data, a problem traced to misconfigured TimescaleDB compression settings. On September 9, 2026, queries for a vessel's latest port event took 28 milliseconds when data existed but 2 to 2.5 seconds when it did not, while ETA lookups for unmapped vessels could stall for 30 seconds or more. Over a single night, 'not found' requests consumed 71% of all database time spent on the ETA endpoint. The root cause was that compressed data chunks lacked usable indexes on the columns being queried, forcing the database to perform a sequential scan across hundreds of compressed chunks for every miss. Grouping API metrics by route template rather than raw path revealed the pattern, showing that slow responses were consistently tied to empty results rather than specific vessels or endpoints.

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)

The Single Router in Southern Arkansas That Ground Air Travel to a Halt

Article URL: https://www.wsj.com/business/airlines/the-single-router-in-southern-arkansas-that-ground-air-travel-to-a-halt-eedc326c Comments URL: https://news.ycombinator.com/item?id=50503588 Points: 6 # Comments: 0

0 comments Read more at Hacker News

ProgrammingDEV Community ·

GitHub Actions OIDC lets cloud deploys drop stored keys, but trust conditions decide safety

GitHub Actions can replace a stored cloud access key with OIDC, where GitHub signs a short-lived token for each job that AWS, Google Cloud or Azure exchange for temporary credentials. The security depends entirely on the trust conditions written on the cloud side, since every GitHub repository can obtain a token from the same issuer. Conditions should bind to stable identifiers such as repository ID, a protected environment with required reviewers, or a reusable workflow, rather than a wildcard organization name. The default subject claim format has a weakness: recycled repository or organization names could let a new owner mint matching tokens, so newer repositories use an immutable format that includes numeric IDs. Older repositories must opt into that format through their OIDC settings or the REST API.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Moving Jenkins Pipelines to GitHub Actions: What Maps Directly and What Needs Rewriting

The article explains how to migrate Declarative Jenkinsfile pipelines to GitHub Actions by translating directives one by one, such as agent becoming runs-on and when becoming if. It notes that plugin steps, shared libraries and Groovy script blocks cannot be converted automatically and must be rebuilt by hand. It recommends moving simple build-and-test pipelines first and deploy pipelines last, keeping the Jenkins job running until the new workflow produces matching results. A key warning is that each GitHub Actions job runs on its own fresh machine without a shared workspace, which changes behavior more than the syntax does. The piece presents a mapping table of sixteen constructs, flagging which convert cleanly and which change how the pipeline behaves.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Private Originals and Purchase-Gated Downloads for Creator Portfolios

The article recommends keeping every original image in private storage and serving only compressed, resized, or watermarked derivatives to public portfolio visitors. Each completed purchase should be checked against a durable purchase-to-asset record before the application issues an expiring URL for the original. The design relies on random internal asset identifiers rather than filenames or client-supplied keys to determine entitlement. It also specifies that transformation failures must fail closed instead of falling back to the original file. The author suggests a single REST-based service could cover both image processing and private object access, though the evaluation rests on the author's own judgment.

0 comments Read more at DEV Community