Writing archive

A partial index of the 2% active users took 138 pages instead of 9,096 and answered as fast. But SQLite uses it only when the query says the condition in the same way, or binds the same value, and an index on json_extract() is ignored by the ->> operator, which returns the same value.

My client and my server closed 500 connections, and the kernel kept 500 sockets for 60 more seconds. I measured which side keeps TIME_WAIT, why tcp_fin_timeout does not change it, how the client side runs out of local ports, and why a restarted server can fail to bind.

Without statistics, SQLite picked the wrong index and took 97 ms instead of 0.9 ms. ANALYZE fixed it. Then I changed the data, and the old statistics kept a slow plan that PRAGMA optimize did not see. Statistics are derived data too, and they can also live in an open connection.

close() returns 0 and the program learns nothing more. I made five small experiments to see what the other side gets: a clean end of file, a reset, a half-closed connection, a broken pipe that kills a C program, and CLOSE-WAIT sockets that never go away.

An index exists, and the plan still says SCAN. I tested six cases on SQLite: a function on the column, LIKE, a leading wildcard, OR, a join between a TEXT and an INTEGER column, and a value that matches 93% of the rows. In the last one, ignoring the index is the right choice.