Search
30 results for “postgresql”
Search results
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.
PostgreSQL Indexes
How B-Tree, Hash, GIN, BRIN, and covering indexes work in PostgreSQL, when to create them, how to inspect usage, and the write-overhead trade-offs at scale.
PostgreSQL detects a deadlock between two transactions. What does it actually do, and can you predict which transaction survives?
PostgreSQL automatically detects the circular wait (a deadlock) and resolves it by aborting one of the involved transactions, letting the other(s) proceed. The documentation is explicit that which transaction gets aborted is difficult to predict and shouldn't be relied upon, there is no guarantee it's the "smaller" transaction, the one that started the wait, or any other predictable rule. Applications need to handle a deadlock-abort the same way they'd handle a serialization failure: catch it and retry the aborted transaction, rather than assuming a specific transaction will always be the one sacrificed.
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.
PostgreSQL vs MySQL
How PostgreSQL and MySQL actually differ in data types, concurrency, indexing, and replication, and which one fits which workload.
A PostgreSQL primary crashes right after committing a transaction, under asynchronous replication. Is that transaction guaranteed to exist on the standby?
No. Asynchronous replication (the default) confirms a commit on the primary without waiting for the standby to receive or apply the corresponding WAL records, there's typically a small delay, often under a second, between a commit and its visibility on the standby. If the primary crashes in that window, before the WAL records reached the standby, that transaction is lost even though the client was already told it committed successfully. This is the specific, documented risk asynchronous replication accepts in exchange for not adding network round-trip latency to every commit.
What is PostgreSQL's default transaction isolation level, and what specific anomaly does it still allow that a stricter level would prevent?
PostgreSQL defaults to Read Committed, where each individual statement within a transaction sees a fresh snapshot of everything committed as of that statement's start, not the transaction's start. This prevents dirty reads (seeing another transaction's uncommitted changes) but still allows non-repeatable reads, running the same SELECT twice in one transaction can return different results if another transaction committed a change in between, because each statement gets its own snapshot rather than the transaction using one snapshot throughout. Repeatable Read fixes this specific anomaly by taking one snapshot at the start of the transaction and using it for every statement within it.
What's the practical difference between Repeatable Read and Serializable, given that PostgreSQL's Repeatable Read already prevents phantom reads?
PostgreSQL's Repeatable Read goes beyond the SQL standard's minimum and already prevents phantom reads via snapshot isolation, but it can still allow a specific class of anomaly called a serialization anomaly, where the combined effect of several concurrently-committed transactions is not equivalent to any possible serial (one-at-a-time) ordering of them, even though each transaction individually looks consistent. Serializable adds predicate locking on top of snapshot isolation specifically to detect and prevent that remaining anomaly, guaranteeing that the outcome is always equivalent to transactions having run one at a time in some order. The cost is the same as Repeatable Read's, more serialization failures the application must retry, in exchange for the strongest correctness guarantee available.
How does PostgreSQL decide whether to use an index?
The query planner estimates the cost of each available access path using table statistics (row count, column cardinality, correlation) gathered by ANALYZE. It compares sequential-scan cost against index-scan cost for the specific query. For small tables, or queries that touch a large fraction of rows, a sequential scan often wins even when an index exists, an index is not free to use if it does not narrow the result much. Run EXPLAIN ANALYZE to see the chosen plan, and re-run ANALYZE after large data changes so statistics stay accurate.
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.
Database Connection Pooling
Why transaction pooling gives far better connection reuse than session pooling, and why that same efficiency silently breaks SQL PREPARE, SET, LISTEN, and session-level advisory locks, even though PgBouncer can support protocol-level named prepared statements under transaction pooling when max_prepared_statements is enabled.
Database Locking & Deadlocks
How PostgreSQL's row-level lock modes actually differ in strength, why it can't predict which transaction a deadlock will abort, and why acquiring locks in a consistent order is the real fix, not a "nice to have".
Database Partitioning
How range, list, and hash partitioning each split one logical table into physical pieces, and why partition pruning depends entirely on the WHERE clause matching partition bounds directly, not on any index.
Database Replication
Why asynchronous replication can silently lose the most recent commits if the primary crashes, what synchronous_commit's three levels actually each guarantee, and how to measure replication lag instead of assuming it's small.
Database Transactions & Isolation Levels
Why PostgreSQL's default Read Committed isolation still allows non-repeatable reads and phantom reads, and why Repeatable Read and Serializable trade that risk for transactions your application has to be ready to retry.
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.
What is the practical difference between session pooling and transaction pooling, and why does transaction pooling scale better?
Session pooling assigns one server connection to a client for their entire session, released back to the pool only when the client disconnects, which supports every PostgreSQL feature but means a mostly-idle client still occupies a real server connection the whole time it's connected. Transaction pooling instead assigns a server connection only for the duration of a single transaction, returning it to the pool the moment the transaction ends, so many more clients can share a small, fixed pool of real connections, since a client that isn't actively mid-transaction isn't holding one at all. This is why transaction pooling is the standard choice for applications with many short-lived connections (like a web app's connection-per-request pattern) against a database with a hard connection limit.
An application uses PREPARE to create a reusable prepared statement, then relies on it across multiple requests. What happens if it's deployed behind a transaction-pooled PgBouncer, and why?
It breaks. A SQL PREPARE statement is a session-level feature, it lives on whatever specific server connection issued the PREPARE, but transaction pooling reassigns the underlying server connection to a different client (or the same client's next transaction) as soon as each transaction ends, so there's no guarantee a later request lands on that same server connection where the prepared statement actually exists. This is explicitly documented as one of the session-based features transaction pooling breaks, along with SET/RESET, LISTEN, WITH HOLD cursors, and session-level advisory locks, all of which depend on state tied to one specific, persistent server connection. This is distinct from protocol-level named prepared statements issued via the extended query protocol (what most driver-level "prepared statements" actually are), which PgBouncer can support under transaction pooling when `max_prepared_statements` is set to a non-zero value.
Why would you choose session pooling over transaction pooling even though it scales to fewer concurrent clients per server connection?
Session pooling is the only mode of the two that supports every PostgreSQL feature without exception, prepared statements, session variables set via SET, LISTEN/NOTIFY, session-level advisory locks, because the server connection genuinely stays with the client for as long as their session lasts. If an application depends on any of those session-level features and can't be refactored around them, session pooling is the correct choice despite its lower connection-reuse efficiency, trading raw scalability for full feature compatibility rather than working around broken session state.
What is the practical difference between SELECT ... FOR UPDATE and SELECT ... FOR SHARE?
FOR UPDATE takes an exclusive row lock, it blocks other transactions from updating, deleting, or taking any competing lock (including another FOR UPDATE or FOR SHARE) on the same rows, appropriate when you're about to modify the row and need to ensure nothing else changes or locks it first. FOR SHARE takes a shared lock, it still blocks updates and deletes, but permits other transactions to also take FOR SHARE or FOR KEY SHARE locks on the same rows concurrently, appropriate when you only need to ensure a row doesn't change or get deleted while you read it, without needing exclusive access.
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.
What is the difference between range, list, and hash partitioning, and when would you choose each?
Range partitioning divides rows by a value falling within a bounded, non-overlapping range (inclusive lower bound, exclusive upper bound), the natural fit for time-series data like logs partitioned by month. List partitioning explicitly assigns specific key values to specific partitions, a good fit when data naturally groups into a known, finite set of categories, like a specific list of counties or regions. Hash partitioning distributes rows by the hash of the partition key modulo a chosen number of partitions, useful specifically when there is no natural range or category to split on and you just need to spread rows roughly evenly across a fixed number of partitions.
A query filters WHERE logdate >= 2008-01-01 against a table range-partitioned by logdate across dozens of monthly partitions. What does partition pruning actually do, and what does it depend on?
Partition pruning lets the planner prove, from the query's WHERE clause and each partition's declared bounds, that some partitions cannot possibly contain a matching row, and it excludes them from the plan entirely rather than scanning and filtering every partition. In this example, `logdate >= 2008-01-01` has no upper bound, so it prunes only the partitions entirely before 2008-01, decades of older monthly partitions are eliminated before execution, while every partition from 2008-01 onward, including all of them up to the present, is still considered and scanned. Pruning down to a single partition would need a bounded predicate on both ends, for example `logdate >= 2008-01-01 AND logdate < 2008-02-01`. This depends entirely on the partition bounds themselves, not on any index, a partitioned table with no indexes at all still benefits from pruning, and pruning specifically requires the WHERE clause to reference the partition key directly with values (or parameters) the planner can actually compare against those bounds.
Why can't partition pruning work with a WHERE clause like WHERE logdate >= CURRENT_TIMESTAMP, and what does that tell you about writing partition-friendly queries?
Partition pruning at plan time requires the comparison value to be known and fixed when the plan is built, and `CURRENT_TIMESTAMP` is not immutable, its value depends on when the query actually executes, not when it's planned, so the planner can't statically prove which partitions it will or won't match. (Pruning can still happen at execution time for genuinely parameterized values, like a join parameter from an outer query, just not for volatile functions like this one.) This means writing partition-friendly queries means filtering on the partition key with values the planner can actually reason about, a literal, a bound parameter, or an immutable expression, not a function whose result varies by when the query runs.
What is the actual difference between synchronous_commit = on, remote_write, and remote_apply?
`on` (the standard synchronous setting) waits for the standby to write the commit record to its own disk, "2-safe" durability, data is lost only if both primary and standby crash simultaneously. `remote_write` only waits for the standby to receive the record and hand it to its operating system, not for a disk flush, weaker durability (a standby OS crash before its own flush could still lose it) in exchange for a faster commit. `remote_apply` is the strongest of the three, it waits until the standby has actually replayed the transaction and made it visible to queries, which is what allows read queries against the standby to see a transaction immediately after the primary reports it committed.
How do you actually measure replication lag, rather than assuming it's negligible?
Replication lag is the gap between WAL records generated on the primary and WAL records actually applied on the standby, and it's measured concretely by comparing the primary's current WAL write position, from `pg_current_wal_lsn()`, against the standby's last replayed position, from `pg_last_wal_replay_lsn()`, via `pg_wal_lsn_diff()`. A large or growing byte gap is a real health signal, it points at the primary generating WAL faster than the network or standby can keep up, or the standby itself being under heavy load, not something to infer from "it's usually under a second" folklore. Monitoring this value directly, not assuming a small delay, is what actually catches a standby falling dangerously behind before a failover makes that lag visible as lost data.
A transaction under Repeatable Read isolation fails with "could not serialize access due to concurrent update." What actually happened, and what is the application expected to do?
Repeatable Read uses snapshot isolation, the transaction sees a consistent snapshot from its own start, but if it then tries to update a row that another, concurrently-committed transaction already modified, PostgreSQL detects the conflict and aborts the transaction with a serialization failure rather than silently applying an update based on stale data. This is not an application bug, it is Repeatable Read (and Serializable) working as designed, both isolation levels explicitly require the application to catch this specific error and retry the transaction from the beginning, trading the guarantee of not overwriting concurrent changes for the operational cost of occasional automatic retries.
What is the difference between `INNER JOIN` and `LEFT JOIN`?
`INNER JOIN` returns only rows where the join condition matches in both tables, a row from either side with no match is dropped entirely from the result. `LEFT JOIN` returns every row from the left table regardless of whether a match exists on the right, filling in `NULL` for the right table's columns when there isn't one. Reaching for `INNER JOIN` when you actually need "every user, even ones with zero orders" silently drops exactly the rows you wanted to see, which is why an unexpectedly short result set is one of the most common join bugs.
What is the difference between GROUP BY aggregation and a window function like `ROW_NUMBER() OVER (...)`?
`GROUP BY` collapses every row in a group into a single output row per group, you lose access to the individual rows once you've aggregated. A window function computes its result (a rank, a running total, a row number) per row while keeping every individual row in the output, `PARTITION BY` defines the grouping for that calculation, but nothing is collapsed. This is why "give me each order plus that customer's running total" needs a window function, while "give me one row per customer with their total" is a plain `GROUP BY`.
Why does wrapping several statements in `BEGIN`/`COMMIT` matter, even for a multi-step operation that's logically one action?
Without an explicit transaction, PostgreSQL commits each statement independently the moment it succeeds; if a multi-step operation (debit one account, credit another) fails halfway through, the database is left in an inconsistent, partially-applied state with no way to undo the completed step. Wrapping the statements in `BEGIN`/`COMMIT` groups them so they can be committed or rolled back together, but that alone isn't automatic protection against every failure mode: PostgreSQL only aborts a transaction on its own when a statement raises an actual SQL error, an `UPDATE` that runs successfully but matches zero rows (a mistyped account id, for instance) is not an error at all, and would otherwise be committed as if the transfer had actually happened. Making the transaction genuinely safe means checking that each `UPDATE` affected exactly the one row expected and issuing an explicit `ROLLBACK` if either check fails, only issuing `COMMIT` once both checks pass.