PostgreSQL Partial and Expression Indexes Can Cut Index Size by 90%, Boost Query Speed

·1 views

PostgreSQL supports partial indexes with WHERE clauses and expression indexes on computed values, two features that most ORM-generated schemas overlook entirely. Partial indexes restrict which rows are stored in an index, dramatically reducing size — one example showed a drop from 2.1 GB to 42 MB by filtering only active users. Expression indexes store the result of a function, enabling fast lookups for operations like case-insensitive email searches or JSONB field queries, with one benchmark showing query time falling from 3,100 ms to 1.1 ms. Four production patterns are highlighted: soft-delete filtering, multi-tenant row isolation, case-insensitive search, and JSONB field indexing. Developers are advised to run ANALYZE manually after creating these indexes in production, as the query planner requires up-to-date statistics to make correct execution decisions.

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)

Three Ways to Edit Files Directly in AWS CloudShell

AWS CloudShell offers multiple options for editing files without leaving the browser-based terminal environment. Users comfortable with terminal editors can use Vim for full navigation features or Nano for a simpler interface with on-screen shortcut prompts. A third option, the built-in 'edit' command, opens files in a graphical editor similar to VS Code, supporting familiar shortcuts like CTRL+S to save. Each method suits a different comfort level, from seasoned Vim users to those who prefer point-and-click editing. The choice ultimately depends on which tool helps the user resolve their task most quickly.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Why SKILL.md Files Cannot Enforce Coding Rules in AI Tools Like Cursor

AI coding assistants like Cursor often ignore rules written in SKILL.md files because model attention is limited and recent tool outputs outweigh instructions seen earlier in a session. The model tends to prioritize completing the user's latest request over adhering to a rule file loaded at the start of a conversation. A more reliable approach pairs the skill file with a hard command layer — such as running tsc --noEmit or a test suite — that must pass before any output is declared production-ready. Unlike text instructions, a failing compiler or test command produces a concrete, undeniable signal that forces the agent to make corrections. The author recommends reserving hooks for deterministic tools like formatters, resetting chat threads between features, and always requesting that typecheck and test output be pasted at the end of each session.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

One line of code adds tab completion to Python CLIs via shtab and jsonargparse

A developer building a Python command-line tool found that users had no way to discover valid argument values mid-command, such as the four accepted strings for an order_by parameter. Rather than writing separate shell completion scripts for bash, zsh, fish, and tcsh — each in a different syntax — the author turned to shtab, an optional dependency of jsonargparse. By adding shtab to the project's dependencies and calling set_parsing_settings(add_print_completion_argument=True) in one line, the CLI automatically gains a --print_completion flag. The completion scripts are generated by inspecting the existing parser, which already knows all valid choices from the client's type hints, meaning no manual maintenance is required. The approach keeps tab completion in sync with the interface automatically, since both derive from the same source code.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Why Browser Games That Seem Offline Often Still Require an Internet Connection

Many browser games give the impression of being offline-capable due to fast load times and simple gameplay, but they frequently depend on internet access for assets, leaderboards, authentication, and analytics. Developers distinguish between three scenarios: truly offline-capable games with deliberate caching architecture, partly cached games that may break unpredictably, and online games that merely mimic an offline aesthetic. Standard browser caching is not a reliable substitute for offline support, as the browser can revalidate, remove, or update files independently, meaning a single missing resource can prevent a game from loading. Service workers offer a more intentional solution by intercepting network requests and serving cached content, though they introduce their own versioning challenges if not carefully managed. Reliable offline functionality must be explicitly designed and tested as a complete user experience rather than assumed from partial caching.

0 comments Read more at DEV Community