PostgreSQL
Indexes and Primary Keys on Partitioned Tables in PostgreSQL
Indexes and primary keys on partitioned tables in PostgreSQL: the partition key rule, local indexes, ON ONLY builds, foreign keys and lookup patterns.
7 min read
Topic
Indexing
The right index turns minutes into milliseconds; the wrong one taxes every write. Learn which is which, and when to drop them.
PostgreSQL
Tuning PostgreSQL for SSDs With random_page_cost and More
Tune PostgreSQL for SSDs: lower random_page_cost, set effective_io_concurrency, check changed plans, use per tablespace values and keep scans that win.
7 min read
PostgreSQL
Seq Scan Versus Index Scan Versus Bitmap Scan Explained
Seq scan versus index scan versus bitmap scan in PostgreSQL: how each works, when the planner picks it, index only scans and why a seq scan can be right.
7 min read
JSON
Indexing JSON Fields in SQLite With Expression Indexes
Index a field inside a SQLite JSON document with an expression index or a generated column, and see which query spellings use it and what it costs.
7 min read
JSON
Indexing a MySQL JSON Field With a Generated Column
MySQL cannot index a JSON column directly. Add a generated column, index it, and learn the collation, NULL and cast traps that change query results.
8 min read
PostgreSQL
Prefix Search and Autocomplete in PostgreSQL Without Pain
Build prefix search and autocomplete in PostgreSQL with text_pattern_ops, tsquery prefix matching, trigram indexes, ranking by popularity and debouncing.
6 min read
PostgreSQL
Typo Tolerant Search With pg_trgm in PostgreSQL Queries
Build typo tolerant search with pg_trgm in PostgreSQL: trigrams, similarity thresholds, GIN and GiST indexes, word similarity and full text fallback.
7 min read
JSON
Indexing JSONB With GIN in PostgreSQL and When It Helps
Build GIN indexes on JSONB, choose between jsonb_ops and jsonb_path_ops, and see the real size, build, insert and query costs on a 200,000-row table.
8 min read
PostgreSQL
Indexing Full Text Search With GIN in PostgreSQL Tables
Index PostgreSQL full text search with GIN: how it works, GIN versus GiST, fastupdate and the pending list, build time, size and when the planner skips it.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.