PostgreSQL Cursors Slash Export Times by Replacing Random Reads with Sequential Scans

·1 views

A data pipeline exporting hundreds of gigabytes from PostgreSQL in 2,000-row batches was running far slower than expected, with the bottleneck traced to how the database read data from disk. Using a WHERE id > X LIMIT 2000 pattern caused Postgres to perform up to 2,000 random page lookups per batch via a B-tree index, forcing the disk head to constantly seek across different locations. Switching to a server-side cursor allowed Postgres to scan the heap sequentially, reading each 8KB page exactly once and enabling the OS to prefetch ahead predictably. This change required no schema modifications — only a different query pattern — yet dramatically reduced I/O overhead at scale. However, cursors hold an open transaction for their entire duration, which can block vacuum and inflate write-ahead logs on very large or long-running exports.

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)

Advanced Rust Tutorial: Scanning Process Memory via Windows OS APIs

A technical guide demonstrates how to scan a process's address space in Rust using Windows system calls via the windows-sys crate. The tutorial leverages Windows API functions such as GetCurrentProcess, K32GetProcessMemoryInfo, and VirtualQueryEx to inspect and retrieve memory information from a running process. Developers must enable specific feature flags in Cargo.toml to access the required Win32 modules for memory, process status, and threading. Key data structures like MEMORY_BASIC_INFORMATION and PROCESS_MEMORY_COUNTERS are used to capture details such as working set size, page fault count, and paged pool usage. The example wraps Windows API output in a custom Rust struct with Debug support for cleaner inspection of process memory statistics.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Rust's 'static Lifetime Explained: What It Means and What It Does Not

In Rust, static memory refers to program regions loaded at startup that persist for the entire duration of execution, holding binary code, static variables, and certain constants. The 'static lifetime annotation marks a reference as valid for the whole program lifetime, but does not require the referenced data to actually reside in static memory. When used as a trait bound such as T: 'static, it guarantees that a type is self-sufficient and does not borrow any non-static values, allowing it to live until the program exits. By contrast, the const keyword defines compile-time constants that are inlined at their usage sites and carry no runtime memory address. Understanding the distinction between 'static lifetimes, static variables, and constants is essential for writing correct and expressive Rust code.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How an AI Helped One Man Challenge a Housing Cooperative's Legal Error

After his father died in November, the author inherited shares in a German housing cooperative but was told the shares would be liquidated rather than transferred, due to an alleged procedural delay. Suspecting the cooperative had misapplied its own bylaws, he fed all relevant documents — including a poorly scanned 19th-century bylaw booklet — into an AI language model. The system used OCR to extract text from the unreadable PDF, then cross-referenced statutes and bylaw sections to identify a specific paragraph mix-up by the cooperative. Within 180 minutes, the author had a two-page legal letter citing four statutes and three bylaw provisions that supported his position. The case highlights how AI tools can help ordinary people navigate technically public but practically inaccessible legal documents.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Exponential Backoff With Jitter Fixes SQLite Locked-Database Errors Under Concurrent Load

A developer building a SQLite-based failure-logging library encountered persistent 'database is locked' errors when multiple agents attempted simultaneous writes. SQLite's default 5-second lock timeout proved insufficient under heavy concurrent load, causing agents to time out before locks were released. The solution combined a retry wrapper using exponential backoff with random jitter, a connection pool to limit simultaneous connections, and SQLite's Write-Ahead Logging mode to allow concurrent reads during writes. Key parameters include a 30-second connection timeout, up to five retry attempts starting at 100ms and doubling each time, and up to 50ms of random jitter to prevent thundering-herd retries. The developer notes that relying solely on SQLite's built-in timeout is insufficient and recommends pairing it with an explicit retry strategy for robust concurrent write handling.

0 comments Read more at DEV Community