Everything I asked while rewriting the ingest service

Deleting rows marks them dead but leaves index entries until vacuum reclaims the pages, and even then the pages stay allocated to that index. REINDEX INDEX CONCURRENTLY is what actually returns the space.

Start from cores, not workers. A pool of about twice the database core count is the usual ceiling, so 8 to 16 for a small instance. Beyond that throughput flattens and latency grows, because the connections queue inside postgres instead of in your pool.

Columns for anything you filter or join on, JSONB for the long tail nobody queries. The moment you write a WHERE settings->>'plan' = ... that runs often, that key has earned a column and an index.

timestamptz almost always. It stores a point in time in UTC and converts on the way out; timestamp stores a wall clock reading with no idea which wall it was on, which becomes unrecoverable once daylight saving moves.

Split it: add the column nullable, backfill in batches, add a validated check constraint, then set NOT NULL. Each step takes a brief lock instead of one long one, and the backfill can be paused if replication lag grows.

For monitoring, read reltuples from pg_class, which the planner keeps roughly current. It is off by a few percent between vacuums, which is fine for a dashboard and wrong for invoicing.

Usually one of three things: the query returns a large fraction of the table so the scan really is cheaper, the types do not match so the index is not applicable, or statistics are stale. ANALYZE the table first, then compare with SET enable_seqscan = off to see what the planner thinks the index would cost.

Almost certainly. Route reads that follow a write by the same user to the primary for a short window, or track the write LSN in the session and wait for the replica to reach it. The general fix is read-your-writes consistency, not lower lag.

Not usually, so look at what is inside. A comprehension that calls a method per item pays the attribute lookup every time; hoisting it out (append = out.append) or using a generator when you do not need the list is what actually moves the number.

Stream it. Either a pull parser like ijson, or, if the structure is a flat array of objects, read in chunks and use json.JSONDecoder().raw_decode to peel one object at a time. Memory then depends on the largest single object, not on the file.

Typos become errors instead of silent None, and the field list is documentation that cannot drift. The cost is a small allocation overhead, which matters only in the hottest loops. With slots=True even that mostly disappears.

No, except when you deliberately depend on a system build of something heavy like GTK bindings. Inheriting site-packages is the most common source of "works on my machine", because the environment is no longer described by requirements alone.

Specific enough that an unexpected failure still crashes. except Exception around an entire request handler is fine if it logs and re-raises in development; the same clause around three lines of parsing will swallow the typo that broke them.

At the boundaries: function signatures on public modules first, internals never. Run the checker in non-blocking mode for a few weeks so the noise is visible without stopping anyone, then turn on strictness one module at a time.

When the command genuinely is a shell pipeline you control end to end, and never with any value that came from outside the program. The list form avoids quoting entirely, which is both safer and easier to read.

Keep the work in functions, put argument parsing in main(), and guard with if __name__ == "__main__": sys.exit(main()). Return codes from main then work for both the shell and the tests.

Logs with structure, because they answer "what happened" for incidents you did not anticipate. Metrics come next for the handful of numbers you would page on. Traces are worth it once a request crosses more than two services.

Alert on symptoms users feel, not on causes. Disk at 85% is a cause and often harmless; requests failing is a symptom and always matters. Every alert that cannot be acted on immediately should be a dashboard instead.

That is the mechanism: every distinct label value creates a series. User ids, request ids and full URLs never belong in labels. Put them in logs and traces, where high cardinality is the point rather than the problem.

Tail sampling: buffer the spans and decide after the outcome is known, keeping everything that errored or exceeded a latency threshold plus a small random slice of the rest. Costs more memory at the collector and is almost always worth it.

Three usual causes: leftover state between tests that a fresh CI container does not have, timing assumptions that a slower machine breaks, and a shared resource like a fixed port or a real clock. Run the suite locally in a random order to reproduce the first one.

Coverage measures what ran, not what was checked. A suite at 80% with meaningful assertions is healthy; the same number reached by importing modules is decoration. Track it as a trend and never as a gate.

A fake server, when the protocol matters. Mocks encode your belief about the API, so they keep passing after the real one changes. A local server exercises serialisation, headers and timeouts, which is where the bugs actually live.

The ones that already bit you, and the boundaries: empty, one, many, malformed, and the largest input you claim to support. That list catches most regressions without turning the suite into a second implementation.