Topic: sql
-
Full-text search in PostgreSQL with tsvector
PostgreSQL turns text into a tsvector of normalised lexemes using a language configuration, matches it against tsquery, ranks with ts_rank and indexes it with GIN; it handles stemming and stop words but not typos or synonyms out of the box.
-
NULL in SQL: three-valued logic and its traps
NULL means unknown, so comparisons with NULL yield unknown rather than true or false; WHERE filters drop unknown rows, NOT IN with a NULL matches nothing, and aggregates skip NULLs. Use IS NULL, IS DISTINCT FROM and COALESCE deliberately.
-
Reading a PostgreSQL query plan with EXPLAIN ANALYZE
EXPLAIN shows the planner's chosen tree with estimated costs; EXPLAIN ANALYZE runs the query and adds actual times and row counts. Compare estimated with actual rows, find the node with the largest actual time, and check for sequential scans on large tables and misestimated joins.
-
When SQLite is the right database
SQLite is a full SQL engine in a library file, ideal for single-host applications, embedded data, tests and moderate write loads with one writer at a time; a client-server database is preferable for many concurrent writers, network access from several hosts, or very large datasets.
Machine-readable: JSON