Webtagr - Technology News Summarizer

20x Faster PostgreSQL Hash Partitioning: Bypassing Catalog Lookups

2025-08-27

PostgreSQL's hash partitioning incurs catalog lookup overhead in high-throughput applications. This article presents an optimization technique that bypasses PostgreSQL's catalog lookups by pre-calculating partition indices in the application layer. Using the Ruby gem `pg_hash_func` or directly calling PostgreSQL's hash functions can speed up queries by more than 20 times, significantly reducing latency. This approach is suitable for performance-critical scenarios and offers more choices in balancing simplicity and performance.

(www.shayon.dev)

Development Hash Partitioning

AI Code Assistants: Blessing or Curse?

2025-06-17

AI coding assistants are becoming increasingly sophisticated, generating clean and efficient code. However, this can lead to 'premature closure,' where developers are seduced by seemingly perfect solutions and overlook deeper issues. The article uses a medical analogy, comparing AI to experienced doctors who might miss a rare condition due to their experience. The author advises developers to critically evaluate AI suggestions, actively explore multiple solutions, and avoid falling into the trap of quick fixes to improve code quality and prevent accumulating technical debt.

(www.shayon.dev)

Development

PostgreSQL's Surprise: CTEs, DELETE, and LIMIT's Unexpected Dance

2025-05-04

A surprising PostgreSQL behavior emerged when using a Common Table Expression (CTE) with DELETE ... RETURNING and LIMIT to process a batch of items. The intention was to delete only one row, but multiple rows were deleted. `EXPLAIN ANALYZE` revealed a nested loop semi-join optimization, causing the LIMIT 1 clause to be executed multiple times. The solution was to restructure the query, avoiding the CTE and using a subselect directly in the DELETE's WHERE clause. This highlights that CTEs don't always prevent query plan optimizations, and careful plan examination is crucial for critical operations.

(www.shayon.dev)

Development Query Optimization

PostgreSQL's Synchronous Commit: A Performance vs. Durability Trade-off

2025-03-19

This post explores the performance implications of PostgreSQL's `synchronous_commit` setting. Turning it off significantly boosts transaction throughput, reducing I/O and CPU load, but introduces a small risk window for data loss. The author demonstrates a 30% increase in TPS on I/O-constrained systems. The article advocates for a nuanced approach, suggesting adjusting `synchronous_commit` per session, transaction, or operation, with a Ruby on Rails example provided. The author also notes that on Aurora PostgreSQL, setting `synchronous_commit` to `off` yields the greatest performance gains.

(www.shayon.dev)

Development synchronous commit

Scaling PostgreSQL: Weird Issues and Solutions for High-Growth Startups

2025-02-09

This post tackles common PostgreSQL scaling challenges faced by high-growth startups. It covers issues like lock contention, index bloat, TOAST storage inefficiencies, and the complexities of vertical vs. horizontal scaling, append-only vs. update-heavy tables, and multi-tenancy. For each problem, practical solutions are offered, ranging from database parameter adjustments and concurrency tools to rethinking data access patterns and utilizing features like advisory locks. The author also explores advanced topics such as schema migrations under load, zero-downtime upgrades, and efficient COUNT query strategies. This is a valuable resource for engineers striving to optimize PostgreSQL performance in demanding environments.

(www.shayon.dev)

Development Database Scaling

Database Mocks: More Trouble Than They're Worth?

2024-12-30

While tempting for their speed and simplicity, the author argues that using database mocks for testing ultimately causes more problems than they solve. A user creation example highlights the differences between mocking and testing against a real database. Real database testing reveals potential issues early on, such as unique constraint violations, default value handling, and performance bottlenecks, saving debugging time and reducing production risks. As applications evolve and schemas change, real database tests better handle new validations, data type modifications, and timestamp precision changes, ensuring code aligns with the actual database. The author suggests prioritizing real database testing for data access layers, while using it at the service layer to expose business logic interactions with data. Controllers, however, can mock service calls effectively. Balancing real database tests and mocks is key to building robust applications.

(www.shayon.dev)

Development database testing mocking software testing