Four PostgreSQL arguments that came out even

Some advice gets repeated in code review because it sounds like it should matter. I picked four I have seen argued about and measured them. I expected at least two clear winners. I got three ties and one result that went the opposite way from the advice.

Setup

PostgreSQL 18.6, default settings. 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM. A 2,000,000-row table u with an id, an email, a status and a timestamp. Times are execution times from EXPLAIN (ANALYZE, TIMING OFF), warm cache, first run discarded, median of the rest.

IN, ANY or a VALUES join for 1,000 ids

	median	runs	buffers
`id IN (1999, 3998, ...)`	26 ms	15 to 54 ms	3,001
`id = ANY('{...}'::bigint[])`	19 ms	15 to 20 ms	3,001
`JOIN (VALUES ...) v(i) ON id = i`	20 ms	16 to 21 ms	3,001

All three read exactly the same pages. The medians differ, but one IN run took 54 ms and another 15 ms, so I would not read anything into the gap. Use whichever form your driver makes easy. ANY has one practical advantage when the array is passed as a single parameter: the statement text stays the same whatever the number of ids, which is kinder to prepared statements and to pg_stat_statements.

text, smallint or enum for a status column

Three values, two million rows, each stored three ways in its own one-column table.

	table	index
text	72.6 MB	13.9 MB
smallint	73.0 MB	13.9 MB
enum	72.7 MB	13.9 MB

Every row has a 24-byte header and is padded to 8 bytes, so a 2-byte smallint and a 7-byte 'active' end up the same size. The indexes are identical because B-tree deduplication stores each of the three values once with a list of row pointers. In a wider table the column order can change the padding, but the type of a short status column is not where the space goes. Choose it for what it lets you check.

Columns that are usually empty

One million rows with an id and ten more integer columns. In one table the ten columns are all NULL, in the other all filled.

	table	per row
ten NULL columns	44 MB	36 bytes
ten filled columns	77 MB	68 bytes

A NULL takes no space in the row. It costs one bit in the row's null bitmap. Optional columns that are mostly empty are close to free, which is worth knowing before splitting a table to save space.

count(DISTINCT) against GROUP BY

The advice I have seen most often: count(DISTINCT x) is slow, rewrite it as a count over a GROUP BY subquery. On two million unique emails:

	median
`count(DISTINCT email)`	1.95 s
`count(*) FROM (SELECT email FROM u GROUP BY email)`	3.12 s

The rewrite was slower. I am not claiming it always is. Which one wins depends on the plan the GROUP BY gets and on work_mem, and both change between versions and settings. That is the point: this is advice to check with EXPLAIN on your own data, not to apply.

What I did not test

IN lists with tens of thousands of values.
Status columns inside a wider row, where alignment can differ.
count(DISTINCT) with more work_mem, or on columns with few distinct values.
Other hardware. Compare the ratios, not the seconds.