Database Optimization Archives - ITFROMZERO

Development tutorial - IT technology blog

A Practical Guide to pgvector on PostgreSQL: Vector Embeddings and Semantic Search for Node.js

By IT From Zero October 19, 2026

Practical production lessons deploying pgvector on PostgreSQL: Node.js integration, HNSW index tuning, and memory optimization techniques to slash latency from 8s to 20ms.

MySQL

Optimizing MySQL Queries with Covering Indexes: The Secret to Eliminating Table Lookups

By IT From Zero October 18, 2026

Table Lookups are the primary cause of I/O bottlenecks on large datasets, even when queries use an index. Master Covering Indexes and Deferred Joins to slash query latency from seconds down to milliseconds.

MySQL

Configuring ProxySQL for MySQL: Read/Write Splitting, Connection Pooling, and Query Caching

By IT From Zero October 16, 2026

ProxySQL serves as an intelligent bridge between your application and MySQL database clusters. Here is a practical guide to configuring transparent read/write splitting, optimizing connection pooling, and enabling in-memory query caching without modifying application code.

Python

Optimizing PostgreSQL Performance in Python with asyncpg: Resolving I/O Bottlenecks and Ingesting 100k Records/s

By IT From Zero October 9, 2026

Real-world lessons from a production I/O bottleneck: A complete guide to migrating to asyncpg, configuring connection pools properly, and bulk-ingesting 100k records in 0.8s via PostgreSQL binary protocol.

MySQL

Optimizing MySQL InnoDB Table Fragmentation: Using OPTIMIZE TABLE to Reclaim Disk Space

By IT From Zero October 7, 2026

Why does deleting millions of records fail to free up MySQL disk space? Here is how to detect InnoDB table fragmentation and safely use OPTIMIZE TABLE to reclaim disk space on production.

Database

Bringing SQLite to Production: Optimizing WAL Mode and Synchronous for Real-World Loads

By IT From Zero September 16, 2026

SQLite is far from weak if you know how to configure it. Learn how to optimize WAL Mode, Synchronous, and Cache to handle thousands of transactions per second in production environments.

MySQL

InnoDB Page Compression: A Pro Tip to Reduce Storage by 50% and Extend SSD Lifespan for MySQL

By IT From Zero September 5, 2026

Discover how to use InnoDB Page Compression to reduce MySQL storage by 50%. Detailed guide on configuration, checking actual disk usage, and CPU performance considerations.

MySQL

Mastering OPTIMIZER_TRACE: The Pro Technique for Dissecting MySQL Index Selection Logic

By IT From Zero September 1, 2026

A guide to using OPTIMIZER_TRACE for deep analysis of MySQL index selection. Resolve SQL performance issues effectively based on actual execution costs.

MySQL

Is MySQL Sluggish After a Restart? How to Keep Your Buffer Pool “Hot”

By IT From Zero August 26, 2026

Don't let MySQL fall into a 'Cold Cache' state after every restart. Detailed guide on how to configure InnoDB Buffer Pool Dump/Load to keep database performance at its peak.

MySQL

Mastering InnoDB Tablespace: Tips for Handling Bloated ibdata1 Files and Optimizing I/O

By IT From Zero August 25, 2026

Running out of disk space due to ibdata1? Discover how to configure File-Per-Table, General Tablespace, and SSD/HDD tiering to optimize I/O and manage MySQL storage effectively.