Search
16 results for “joins”
Search results
SQL Joins & Query Planning
Why the same JOIN can execute as a nested loop, a hash join, or a merge join depending entirely on table size and available sort order, and why "it's impossible to suppress nested loops entirely" is a real, documented planner constraint.
PostgreSQL's documentation says it's "impossible to suppress nested-loop joins entirely" even with enable_nestloop off. What does that tell you about relying on planner hints to force a specific join algorithm?
That specific guarantee is documented only for `enable_nestloop`: turning it off only discourages the planner by making nested loops look artificially expensive in cost estimation, it can't hard-disable them, because for some queries a nested loop is the only viable plan at all (for example, certain correlated subquery shapes), so the planner will still use one if it must. `enable_hashjoin` and `enable_mergejoin` don't carry that same caveat, disabling either one actually can prevent the planner from choosing that join type, since a nested loop (or the other remaining method) is always available as a fallback plan. In practice, though, all three settings are best treated as a debugging/diagnostic tool for understanding planner behavior, not a reliable production mechanism for forcing a specific join algorithm, the actual fix for a bad plan is almost always better statistics (via `ANALYZE`) or a better index, not overriding the planner's method choice.
What are the three join algorithms PostgreSQL's planner can choose between, and what does each one actually do?
A nested loop join takes each row from one table (the outer side) and scans the other table (the inner side) for matches, the simplest algorithm, and cheap specifically when the inner side is small or has a usable index so that scan is fast per outer row. A hash join builds an in-memory hash table from one input keyed on the join column, then probes it with rows from the other input, efficient for large inputs with no useful sort order or index. A merge join requires both inputs already sorted on the join key (or sorts them first) and then walks both sorted streams in lockstep, efficient specifically when that sorted order already exists or is useful for something else in the query anyway.
Why would a hash join beat a nested loop join for two large, unindexed tables, but lose to a nested loop join when one table is tiny?
A nested loop join's cost scales with (outer rows) × (cost of an inner-side lookup per row), so with two large unindexed tables, the inner-side scan is expensive and gets repeated for every single outer row, making the total cost grow multiplicatively. A hash join instead pays a roughly one-time cost to build a hash table from one side, then does a cheap lookup per row from the other side, additive rather than multiplicative, which wins decisively at scale. But when one table is tiny, the nested loop's "repeat the inner scan per outer row" cost is trivial regardless, and it avoids the hash table's build overhead entirely, so the simpler algorithm wins for small inputs specifically.
PostgreSQL Fundamentals
How tables, joins, aggregates, window functions, and transactions fit together in everyday PostgreSQL work, from the actual internals of a single index (covered in PostgreSQL Indexes) to the SQL you write day to day.
Step-by-Step Guide: Creating a Client Computer and Joining It to a Domain (Hyper-V Lab Setup)
As part of building a realistic domain based environment, this lab demonstrates how to create a client computer and join it to an existing Active Directory Domain Services (AD DS) domain hosted on Windows Server 2019 , using Hyper V. A domain environment is incomplete without client machines. Joini…
Why would you use a NAT gateway instead of just putting a resource in a public subnet?
A NAT gateway lets resources in a private subnet initiate outbound connections to the internet (to pull a package, call an external API) while remaining unreachable from the internet for inbound connections; the NAT gateway only translates and forwards traffic the private resource itself initiated. Putting a resource directly in a public subnet with a public IP makes it directly reachable from the internet in both directions, which is unnecessary exposure for anything that only needs outbound access, like an application server that doesn't need to accept direct public traffic.
Why does a real ring hash implementation give each host many positions on the ring instead of just one, and what problem would a single position per host cause?
A host thrown onto the ring at just one point can end up, purely by chance, owning a disproportionately large or small arc of the ring if the hash values happen to land unevenly, since with few points there's no averaging effect smoothing out the randomness. Assigning each host many positions on the ring, scaled by that host's intended weight, so a double-weight host gets roughly twice as many ring entries as a single-weight one, averages out that randomness across many smaller arcs per host, producing a much more even overall traffic distribution than a single coin-flip-like placement per host would.
Why does a smaller, minimal base image reduce security risk beyond just producing a smaller download?
Every package present in a base image is a package that can have a known vulnerability, and a full general-purpose distribution image bundles far more OS packages, libraries, and tools than most applications actually need at runtime. A minimal image (Alpine, a distroless image, or a multi-stage build's final stage) simply has fewer things in it that could ever show up in a vulnerability scan, which is a structural reduction in attack surface, not a mitigation that has to be maintained the way a scanner's exception list does.
What is the difference between journald's "volatile", "persistent", and "auto" storage modes, and which one is the default?
"Volatile" keeps the journal only in `/run/log/journal`, which is memory-backed and wiped on every reboot, nothing survives a restart. "Persistent" writes to `/var/log/journal` on disk, so entries survive reboots (falling back to volatile only during very early boot or if the disk is unavailable). "Auto" is the actual default, and it behaves like "persistent" only if `/var/log/journal` already exists, otherwise it behaves like "volatile", so whether logs survive a reboot on a given system depends entirely on whether that directory happens to have been created, not on any setting an administrator consciously chose.
A client sends a sudden spike of requests that exceeds the configured rate, but the API doesn't immediately reject any of them. Why not, and when does rejection actually start?
The token bucket has accumulated capacity up to the burst limit, if the client had been under the rate limit recently, unused tokens built up in the bucket, and that reserve absorbs a short spike without any request failing, exactly the point of separating burst capacity from steady-state rate. Rejection (a 429 response) only starts once the bucket is actually empty, every accumulated token has been consumed and the spike is sustained long enough that the rate of token consumption keeps exceeding the rate of token replenishment. This is why a token bucket, unlike a hard per-second cap, tolerates brief bursts gracefully while still enforcing a real steady-state ceiling.
Two transactions each update two of the same two accounts, but in opposite order, and deadlock. What's the actual fix, not just for this pair of transactions, but for the application generally?
The deadlock happens because Transaction 1 locks account A then waits for account B, while Transaction 2 locks account B then waits for account A, a circular wait. The general fix isn't retry logic alone, retries only paper over deadlocks that keep recurring, it's acquiring locks on multiple objects in the same, consistent order everywhere in the application (for example, always locking accounts in ascending id order), which makes the circular-wait pattern structurally impossible rather than merely less frequent. Retry logic is still worth having as a safety net, but consistent lock ordering is what actually eliminates this class of deadlock.
Why does reusing the same idempotency key with different request parameters return an error instead of just processing the new parameters?
An idempotency key is a promise that a specific, exact operation happened once; if the same key showed up with different parameters, honoring the new parameters would silently violate that promise; either the original operation's recorded result no longer describes what the key represents, or the client made a mistake by rIeusing a key it should have generated fresh for a genuinely different request. Rejecting the mismatched reuse as an error, rather than guessing which parameters were "correct" or silently processing the new ones, surfaces that client-side mistake immediately instead of masking it.
What does journalctl -u myservice actually show you, beyond just log lines the service itself printed?
`-u`/`--unit=` expands into a filter that captures more than the service's own stdout/stderr, it includes messages logged about the unit by systemd itself (start, stop, failure notifications) and associated coredump information if the process crashed, correlated by the unit's identity rather than by manually grepping a shared log file for its name. This is more reliable than text-searching a combined log, since it's scoped by the actual unit metadata the journal records, not by whatever string happens to appear in a log line.
During a live incident, what does journalctl -f -u myservice -p err do, and why combine those specific options?
`-f` follows the journal in real time, printing new entries as they're appended, `-u myservice` scopes that stream to just the one unit under investigation, and `-p err` filters to only entries at the "err" priority or more severe (more important), suppressing informational noise. Combined, this gives a live, scoped, severity-filtered view of exactly one service's serious problems as they happen, rather than watching an unfiltered firehose of every unit's routine log output and trying to manually spot the relevant failure.
Why does Python's documentation say a class defining mutable objects with a custom __eq__ should not implement __hash__ at all?
If an object's hash is derived from fields that can change after the object is already stored as a dict key, mutating it changes its hash value, but the object stays in whatever bucket it was originally placed in based on the old hash, so a subsequent lookup computes the new hash, looks in the new (wrong) bucket, and fails to find an object that is, in fact, still in the dictionary. Making a mutable object unhashable by default (which not defining `__hash__` effectively signals) prevents this specific class of bug entirely, at the cost of not being able to use that object as a dict key or set member at all, a deliberate, documented trade-off favoring correctness over convenience.