DEV Community: Nishant Gaurav

Longest Common Subsequence: A New Pattern, the Same Thinking

Nishant Gaurav — Sun, 01 Nov 2026 15:30:00 +0000

This chapter covers the first two steps: recursion and memoization. Tabulation comes next.

What Is a Subsequence?

Before anything else, this needs to be clear.

A subsequence is formed by deleting zero or more characters from a string without changing the order of the remaining characters. The selected characters don't have to be adjacent.

String: A B C D E

Valid subsequences:
  ACE    (positions 1, 3, 5)
  ABD    (positions 1, 2, 4)
  BDE    (positions 2, 4, 5)
  ABCDE  (the whole string)
  A      (just the first character)
  ""     (empty — valid)

Not a valid subsequence:
  CA     (order is reversed)

The key point: gaps are allowed, but order is not.

What Is the Longest Common Subsequence?

Given two strings, find the longest subsequence that appears in both.

X = ABCDGH
Y = ABEDFW

Some common subsequences: A, AB, ABD, ABDH

The longest one is ABDH with length 4. That's the LCS.

The characters must appear in the same relative order in both strings, but they don't need to be adjacent in either.

Why This Is a DP Problem

Look at the last characters of both strings.

X = A B C D G H   → last character: H
Y = A B E D F W   → last character: W

Two cases arise.

Case 1: Last characters match.

If X[m-1] == Y[n-1], that character is part of the LCS. Include it and reduce both strings by one.

LCS(X, Y, m, n) = 1 + LCS(X, Y, m-1, n-1)

Case 2: Last characters don't match.

If X[m-1] != Y[n-1], you don't know which one to drop. So you try both and take the better result.

LCS(X, Y, m, n) = max(
    LCS(X, Y, m-1, n),    # drop last char of X
    LCS(X, Y, m, n-1)     # drop last char of Y
)

These two cases completely define the recursive structure.

Base Case

The smallest valid input is when either string runs out of characters.

X = ""         or        Y = ""

An empty string has no characters to share. The LCS is zero.

if m == 0 or n == 0:
    return 0

Always design the base case first. Ask: what is the smallest input where the answer is obvious?

The Recursive Solution

Base case plus choice diagram gives you the full recursion directly.

class Solution:

    def lcs(self, X, Y, m, n):

        # Base case: one string is empty, no common subsequence
        if m == 0 or n == 0:
            return 0

        # Last characters match — include and shrink both
        if X[m - 1] == Y[n - 1]:
            return 1 + self.lcs(X, Y, m - 1, n - 1)

        # Last characters don't match — try dropping from either side
        else:
            return max(
                self.lcs(X, Y, m - 1, n),
                self.lcs(X, Y, m, n - 1)
            )

    def longestCommonSubsequence(self, X, Y):
        m = len(X)
        n = len(Y)
        return self.lcs(X, Y, m, n)

The code is a direct translation of the choice diagram. Nothing is added beyond base case and two cases.

Why the Recursive Solution Gets Slow

When characters don't match, two recursive calls are made. Each of those makes two more. The tree grows exponentially.

Worse, the same state appears multiple times through different paths.

LCS(m, n)
├── LCS(m-1, n)
│   ├── LCS(m-2, n)
│   └── LCS(m-1, n-1)  ← appears here
└── LCS(m, n-1)
    ├── LCS(m-1, n-1)  ← and here again
    └── LCS(m, n-2)

LCS(m-1, n-1) is calculated twice from the top level alone. Deeper in the tree, the overlap gets worse. For long strings, this becomes too slow.

The fix is the same as it was in the Knapsack series: store the answer the first time, reuse it every time after.

Memoization

The state is defined by two values: m and n. So the cache is a 2D table of size (m+1) × (n+1).

The +1 on each dimension is because m and n range from 0 to their respective lengths. Zero is a valid state (it's the base case), so it needs a slot in the table.

Why initialise with `-1` and not `0`

0 is a valid LCS answer. For example:

X = ABC
Y = XYZ

These strings share no characters. LCS = 0. If the table is initialised to zero, you can't tell whether a cell holds a computed answer of zero or an unvisited state. So we use -1 as the sentinel.

-1 → not computed yet
 0 → computed, answer is 0
>0 → computed, answer is this value

This is the same reasoning as using None in Count of Subsets (Chapter 4), where False was a valid answer and couldn't serve as the placeholder.

Common mistake: initialising with 0 and then checking if T[m][n] != 0. This skips recomputing incorrectly when the real answer is zero.

# WRONG
if T[m][n] != 0:
    return T[m][n]

# CORRECT
if T[m][n] != -1:
    return T[m][n]

The Memoized Solution

class Solution:

    def lcs(self, X, Y, m, n):

        # Base case
        if m == 0 or n == 0:
            return 0

        # Return stored answer if already computed
        if self.T[m][n] != -1:
            return self.T[m][n]

        # Match: include character, shrink both strings
        if X[m - 1] == Y[n - 1]:
            self.T[m][n] = 1 + self.lcs(X, Y, m - 1, n - 1)

        # No match: try both options, take the better one
        else:
            self.T[m][n] = max(
                self.lcs(X, Y, m - 1, n),
                self.lcs(X, Y, m, n - 1)
            )

        return self.T[m][n]

    def longestCommonSubsequence(self, X, Y):
        m = len(X)
        n = len(Y)

        # -1 means not yet computed
        self.T = [[-1] * (n + 1) for _ in range(m + 1)]

        return self.lcs(X, Y, m, n)

The recursive logic is unchanged. Two lines added: the cache check at the top, the store before returning. That's memoization.

The LCS Pattern to Remember

Base case:
  m == 0 or n == 0 → return 0

Choice:
  X[m-1] == Y[n-1] → 1 + LCS(m-1, n-1)
  X[m-1] != Y[n-1] → max(LCS(m-1, n), LCS(m, n-1))

Memoization:
  Table size: (m+1) × (n+1)
  Initialise with -1
  Check before computing, store after

What You Now Understand

LCS is not Knapsack. The inputs are two strings, not an array and a capacity. The table is indexed by string lengths, not items and weight. The choice is match-or-no-match, not take-or-skip.

But the process is the same. Base case first. Choices second. Recursion from those two. Repeated states observed. Memoization added with two lines.

The next chapter converts this recursion into a bottom-up tabulation. Once you see how m-1 and n-1 in the recursion map to i-1 and j-1 in the table, the entire LCS family of problems becomes much easier to build.

GROUP BY In PostgreSQL

Nishant Gaurav — Thu, 22 Oct 2026 17:30:00 +0000

For a while, GROUP BY was basically the ceiling of what I knew how to do with aggregation. Total per city, count per venue, that kind of thing. Then I ran into a task — rank venues within their own city by latitude, but keep every single venue row in the result, not collapse them into one row per city — and GROUP BY just couldn't do it. That's the gap window functions fill, and once it clicked, a bunch of queries that used to feel awkward got a lot simpler.

What GROUP BY can't do

GROUP BY collapses rows. If you group venues by city and count them, you get one row per city — the individual venues are gone from the result, folded into that count. That's exactly what you want sometimes. It's exactly the problem other times, like when you want to know each venue's rank within its city, but still want every venue listed individually, not just a per-city summary.

Window functions keep every row

SELECT
    name,
    city,
    RANK() OVER (PARTITION BY city ORDER BY latitude DESC) AS rank_in_city
FROM venues;

This returns one row per venue — nothing gets collapsed. Each row also gets a computed value attached to it, rank_in_city, based on how that venue compares to others in the same city. PARTITION BY city is doing something similar to what GROUP BY does conceptually, splitting the data into per-city buckets, but instead of collapsing each bucket into a single summary row, the window function computes something within each bucket and attaches it back onto every original row.

That's really the core difference worth holding onto: GROUP BY reduces row count, window functions don't.

The ones that come up most

ROW_NUMBER(), RANK(), and DENSE_RANK() all number rows within a partition, but they handle ties differently. ROW_NUMBER() gives every row a distinct number regardless of ties, arbitrarily breaking them. RANK() gives tied rows the same number, then skips ahead — two venues tied for rank 1 means the next one is rank 3, not 2. DENSE_RANK() also gives tied rows the same number, but doesn't skip — the next distinct value after a tie at 1 is 2, not 3.

LAG() and LEAD() compare a row to the row before or after it, within the same ordering:

SELECT venue_id, closed_on,
       LAG(closed_on) OVER (PARTITION BY venue_id ORDER BY closed_on) AS previous_closure
FROM closures;

This pulls, for each closure record, the date of that same venue's previous closure, sitting right there in the same row. Useful for things like "days since last closure" style calculations, where you need to compare a row to the one before it without writing a self join to do it.

SUM() OVER (...) gives you running totals — the same idea as a regular SUM, but computed cumulatively per row instead of collapsed into a single number.

Where this actually gets used

A common real request is "top N per category" — for this project, maybe "the two highest-latitude venues in each city." GROUP BY genuinely can't answer this on its own, because it would need to keep multiple rows per city, which defeats the whole point of grouping. The window function version:

SELECT name, city, latitude
FROM (
    SELECT name, city, latitude,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY latitude DESC) AS rn
    FROM venues
) ranked
WHERE rn <= 2;

The inner query attaches a row number to every venue, restarting at 1 for each new city, ordered by latitude descending. The outer query then just filters down to rn <= 2. This pattern — window function inside, filter outside — is worth recognizing on sight, because "top N per group" comes up often enough that it's basically a template once you've seen it once.

Where this leaves you

Window functions solve a specific gap that GROUP BY leaves open: computing something per group while still keeping every individual row in the result, rather than collapsing down to one row per group. If a query needs a ranking, a comparison to a neighboring row, or a running total, and you catch yourself reaching for GROUP BY and getting frustrated that it's throwing away rows you actually needed, that's usually the signal a window function is what you actually want.

Next up: JSONB in practice — querying and indexing semi-structured data, which comes up constantly once you're syncing data from external sources.

Views vs Materialized Views In PostgreSQL

Nishant Gaurav — Wed, 21 Oct 2026 17:30:00 +0000

This one's short compared to some of the earlier topics in this series, but it's also one of those things that's easy to get backwards if you only skim the definitions once. A view and a materialized view sound like variations on the same idea, and syntactically they're not far apart, but they behave in genuinely different ways once you actually use them.

Where the confusion comes from

Say you've got a dashboard summary showing total venues per city, and it's built on a GROUP BY query that's a bit expensive — maybe it's joining against closures too, computing a few aggregates at once. Someone suggests "just make it a view," and that's reasonable advice, except it doesn't actually solve the performance problem people usually mean when they say that. A regular view doesn't cache anything. It just saves you from retyping the query.

The two, side by side

CREATE VIEW active_venues AS
SELECT * FROM venues WHERE is_closed = FALSE;

CREATE MATERIALIZED VIEW venue_stats AS
SELECT city, COUNT(*) AS total FROM venues GROUP BY city;

REFRESH MATERIALIZED VIEW venue_stats;

A regular VIEW is really just a stored query, nothing more. Every time you SELECT * FROM active_venues, Postgres runs the underlying query against venues again, live, right then. It's always current, because it's not storing anything — it's just a name attached to a query definition that gets substituted in and executed fresh each time.

A MATERIALIZED VIEW actually stores the result physically, like a real table. Reading from venue_stats is fast, because it's just reading stored rows, not recomputing anything. The trade-off is that it goes stale the moment the underlying data changes, and it stays stale until you explicitly run REFRESH MATERIALIZED VIEW to recompute and re-store the result.

Picking one

Use a regular VIEW when the underlying query is cheap enough that running it live isn't a real cost, and when the data behind it absolutely needs to be current every time someone reads it. active_venues above is a good fit — filtering on a boolean is trivial for Postgres, there's no real performance reason to cache it, and you'd generally want to know immediately if a venue's status changed.

Use a MATERIALIZED VIEW when the query is genuinely expensive — heavy aggregation, several joins, something that takes real time to compute — and when the consumers of that data can tolerate it being slightly stale in exchange for speed. A dashboard's summary stats are the classic case: nobody's checking venue_stats expecting it to reflect a venue that closed thirty seconds ago, and recomputing that aggregation on every single page load for every user would be wasteful when refreshing it once every few minutes, or on some schedule, is perfectly acceptable.

Where this leaves you

The names sound similar, but the behavior isn't — a VIEW is a saved query that always runs live, a MATERIALIZED VIEW is a saved result that needs an explicit REFRESH to stay current. Picking between them comes down to one question: does this need to be live every time, or is a periodically refreshed snapshot good enough for what it's being used for.

Next up: window functions — a step past GROUP BY that lets you compute things like running totals and per-group rankings without collapsing your rows down to one per group.

Transactions, ACID, and MVCC In PostgreSQL

Nishant Gaurav — Tue, 20 Oct 2026 17:30:00 +0000

This is the chapter that finally connected a bunch of things I'd been treating as separate facts — why UPDATE doesn't edit rows in place, why table files keep growing even after you delete data, why VACUUM exists at all. It turns out all of it comes back to one mechanism, MVCC, and once that clicked, a handful of previously mysterious Postgres behaviors stopped being mysterious.

What transactions actually give you

BEGIN;
UPDATE venues SET is_closed = TRUE WHERE id = 5;
UPDATE closures SET closed_on = NOW() WHERE venue_id = 5;
COMMIT;   -- or ROLLBACK;

Everything between BEGIN and COMMIT either all happens, or none of it does. If something goes wrong partway through — an error, a constraint violation, or you just decide to ROLLBACK yourself — neither update takes effect. This matters here specifically because marking a venue closed and logging when it closed are really two halves of one action. Without a transaction wrapping both, you could end up with a venue marked closed but no closure record, or a closure record for a venue that's still marked open, if something failed between the two statements.

ACID, without the textbook phrasing

Atomicity — the whole transaction is one unit. No partial, half-applied state where some of it went through and some didn't.
Consistency — the database moves from one valid state to another. Constraints never get violated in the committed result, even if intermediate steps inside the transaction technically would have violated them on their own.
Isolation — concurrent transactions don't see each other's uncommitted changes. What "see" actually means here depends on the isolation level, which is worth its own section.
Durability — once committed, it survives a crash. It's written to disk, specifically to something called the write-ahead log (WAL), before Postgres considers the commit successful.

These four letters get recited a lot without much explanation of what they're actually protecting against. Atomicity is what stops the venue/closure scenario above from going half-done. Durability is what means a COMMIT isn't a lie — if the server crashes one second after it returns success, that data is still there when it comes back up.

Isolation levels

Level	Prevents	Postgres default?
Read Uncommitted	(Postgres treats this the same as Read Committed — it doesn't actually implement dirty reads)	no
Read Committed	dirty reads	yes, this is the default
Repeatable Read	dirty + non-repeatable reads	no
Serializable	all anomalies, including phantom reads	no

Read Committed being the default means each individual statement inside a transaction sees whatever was committed at the moment that specific statement started — not the moment the whole transaction began. That distinction matters more than it sounds like it should: two statements in the same transaction, run a few seconds apart, could see different data if something else committed in between, because each statement takes its own fresh look at what's currently committed.

Serializable is the strongest guarantee — it behaves as though transactions ran one at a time, in some order, with no interleaving at all. The trade-off is that Postgres has to detect conflicts between concurrent Serializable transactions and abort one of them if it can't guarantee that "as if run one at a time" property, which means application code using Serializable transactions generally needs retry logic built in for when that happens.

For anything genuinely financial-grade, Serializable is the safer default despite that extra complexity. For most everyday application logic, Read Committed is fine, and it's what you get unless you explicitly ask for something stronger.

MVCC — the actual mechanism underneath all of this

Here's the part that reframed a bunch of things for me. Postgres doesn't lock rows for reads the way you might picture from a simpler mental model of "one person edits, everyone else waits." Instead, it keeps multiple versions of a row.

When you run an UPDATE, Postgres doesn't modify the existing row in place. It writes an entirely new row version with the updated values, and marks the old version as expired — not deleted yet, just no longer the current version. Readers who started their query before the update still see the old version, consistently, without being blocked by the write happening concurrently. Writers, likewise, aren't blocked by ongoing reads. This is why Postgres handles a mix of concurrent reads and writes well without needing to lock everything constantly.

This also connects directly back to something from the CRUD article earlier in this series — every INSERT creates a new row version, and now the reason that phrasing matters is clearer: Postgres genuinely never edits a row's storage in place. It's always writing new versions and marking old ones as no longer current.

Why VACUUM exists

Those old, expired row versions don't just disappear the moment they stop being current — they sit there, taking up space, until something cleans them up. That's what VACUUM does.

VACUUM ANALYZE venues;

If you've ever deleted a bunch of rows from a table and noticed the file on disk didn't shrink the way you'd expect, this is the explanation — the dead row versions are still physically there until VACUUM reclaims that space. AUTOVACUUM runs by default in the background specifically so you don't normally have to think about this, but it's worth knowing it exists and understanding why, rather than just trusting it silently.

This is a genuinely good question to know the answer to, mostly because a lot of people who've used Postgres casually have never actually encountered it — they've never had a table grow large enough, or gone deep enough into how updates work, to run into it. Knowing why VACUUM exists is less about the command itself and more a decent signal that you've thought about what's actually happening underneath an UPDATE, not just what syntax makes one run.

Putting it together with the venue/closure example

Back to the transaction from the start of this article:

BEGIN;
UPDATE venues SET is_closed = TRUE WHERE id = 5;
UPDATE closures SET closed_on = NOW() WHERE venue_id = 5;
COMMIT;

Under the hood, both UPDATEs create new row versions rather than editing in place. Until COMMIT runs, another transaction reading venues at Read Committed isolation still sees the old, pre-update version of that row — the change genuinely isn't visible yet. Once COMMIT happens, the new versions become the current ones, and the old versions become eligible for cleanup, eventually, by VACUUM. If something failed between the two UPDATE statements and the transaction rolled back instead, both new row versions would just be discarded, and the original rows would remain the current version, as if none of this had happened at all.

Where this leaves you

MVCC is the reason Postgres handles concurrent reads and writes gracefully without heavy locking, and it's also the reason a handful of things that might otherwise seem unrelated — why updates don't happen in place, why deleted data doesn't immediately shrink a table, why VACUUM exists — actually connect to one underlying idea. Transactions and isolation levels sit on top of this, controlling exactly what a given piece of code is allowed to see while other things are happening concurrently.

Next up: views and materialized views — the difference between a query that re-runs live every time versus one that's actually stored and needs to be refreshed.

Indexes in PostgreSQL

Nishant Gaurav — Mon, 19 Oct 2026 17:30:00 +0000

I added my first index thinking it was basically a free speedup — like flipping a switch. Nobody had told me indexes cost anything, so it didn't occur to me they might not always be worth it. It took looking at a genuinely slow write path, and eventually tracing it back to a table with five indexes on columns nobody actually queried by, to understand that an index is a trade-off, not a strict upgrade.

The problem indexes actually solve

Without an index, finding rows that match a WHERE condition means Postgres reads every single row in the table and checks each one — a sequential scan. On a table with a few hundred rows, that's basically instant, not worth worrying about. On a table with millions of rows, it's the difference between a query that returns in milliseconds and one that takes seconds, every single time it runs.

That's the entire motivation. An index gives Postgres a shortcut — a separate structure it can search instead of scanning the whole table — for columns you filter, join, or sort by often enough that the sequential scan actually becomes a problem.

Creating one

CREATE INDEX idx_venues_city ON venues(city);
CREATE UNIQUE INDEX idx_venues_name ON venues(name);

Nothing exotic in the syntax itself. The UNIQUE version does double duty — it speeds up lookups on name and enforces that no two rows can share the same value, functioning as a constraint as well as a performance tool.

The columns worth indexing are the ones that actually show up in your WHERE clauses, your join conditions, or your ORDER BY — not every column "just in case it's useful someday." That instinct, indexing defensively, is exactly what leads to the situation I mentioned above.

When an index doesn't actually help

This is the part that gets skipped in a lot of explanations, and it's arguably more useful to know than when to index, since the "when to" case is fairly intuitive once you understand sequential scans.

Small tables. A sequential scan on a few hundred rows is genuinely faster than using an index, once you account for the overhead of looking the index up and then jumping back to the table to fetch the actual row. The index doesn't help; it's just extra bookkeeping.

Columns that are rarely queried. An index that never gets used in a WHERE or join condition is pure cost with no corresponding benefit — it still has to be maintained on every write, for nothing.

Columns updated extremely often. Every INSERT or UPDATE that touches an indexed column also has to update the index itself, not just the table row. On a write-heavy table, having several indexes on frequently updated columns adds up to real overhead, and it's the kind of thing that's easy to not notice until write latency creeps up over time.

Low-cardinality columns. Something like a boolean is_closed column only has two possible values. A plain B-tree index on it doesn't narrow things down much, since roughly half the table matches either value — the index isn't selective enough to be worth using over a sequential scan in most cases.

The index types worth being able to name

Type	Best for
B-tree (default)	equality and range queries — the default 90% of the time
GIN	JSONB, arrays, full-text search
GiST	geospatial data (what PostGIS relies on), also full-text
BRIN	huge tables with naturally sequential data, like timestamps in an append-only log
Hash	pure equality lookups only, rarely used over B-tree in practice

You'll mostly use B-tree without ever thinking about it, since it's the default CREATE INDEX gives you. The others matter once you're dealing with specific data shapes. For a geospatial project, this is worth knowing specifically: PostGIS relies on GiST indexes, which support things like "nearest neighbor" and bounding-box style queries efficiently. A plain B-tree fundamentally can't do that kind of spatial lookup — it's built for ordering along a single dimension, not for two-dimensional proximity.

Actually checking if your query is using an index

EXPLAIN ANALYZE SELECT * FROM venues WHERE city = 'Bhopal';

EXPLAIN on its own shows you the planned execution — what Postgres intends to do, without actually running the query. EXPLAIN ANALYZE actually executes it and shows real timing alongside the plan. The thing to look for in the output is whether it says Seq Scan or Index Scan. A sequential scan on a large, filtered table is usually a sign something's missing — either the index doesn't exist yet, or it exists but isn't being used for some reason, which is its own separate rabbit hole worth investigating if it happens.

This is genuinely one of the highest-value habits to build early. Before assuming a slow query is "just how Postgres is" or reaching for some other fix, checking EXPLAIN ANALYZE first tells you whether the problem is actually about indexing or something else entirely — a bad join order, an unnecessary sort, or something else the plan reveals if you know what to look for.

Why OFFSET gets slow, and what to use instead

SELECT * FROM venues ORDER BY id LIMIT 20 OFFSET 10000;

This looks harmless — you're only asking for 20 rows. But Postgres still has to scan through and discard the first 10,000 rows before it can return the 20 you actually wanted. As the offset grows, the query gets slower, even though the result set size never changes. On a table that only ever has a few pages, nobody notices. On a table with deep pagination — page 500 of search results, say — it becomes a real problem.

The alternative is keyset pagination:

SELECT * FROM venues WHERE id > 10000 ORDER BY id LIMIT 20;

Instead of counting through rows to skip, this jumps directly to where the last page left off, using the last seen id as the starting point. With an index on id — which a primary key already has by default — this is fast regardless of how deep into the results you are, because it's not scanning and discarding anything.

Deciding what to index on a real table

Say the venues table from earlier in this series is now getting queried in a few different ways: filtering by city, checking is_closed, and occasionally sorting by created_at.

city is a solid candidate — it's a genuine filter condition, likely used often, and has enough distinct values to actually be selective. is_closed is the low-cardinality case from above — a boolean with two values, and indexing it alone probably doesn't help much on its own. created_at, if the table grows into millions of rows and queries often sort by recency, is a reasonable candidate too, potentially even a BRIN index specifically, since timestamps in a table like this tend to be naturally close to sequential as new rows get inserted.

The way I'd actually verify any of this, rather than guessing, is running the real queries through EXPLAIN ANALYZE before and after adding a candidate index, and comparing the plan and the timing directly instead of assuming.

Where this leaves you

An index isn't a free performance upgrade — it's a genuine trade: faster reads for slower writes and more storage, and it's only worth making on columns that are actually queried often enough for the sequential scan to be a real cost. EXPLAIN ANALYZE is the tool that turns this from guesswork into something you can actually verify, and it's worth reaching for before adding or removing any index, not just when something's already obviously slow.

Next up: transactions, ACID, and MVCC — the part of Postgres that explains why concurrent reads and writes mostly don't get in each other's way, and why VACUUM exists at all.

GROUP BY, HAVING, and Subqueries in PostgreSQL

Nishant Gaurav — Sun, 18 Oct 2026 17:30:00 +0000

There's a point in almost every project where you stop just retrieving rows and start actually asking questions — how many venues per city, which cities have more than a handful, which venues are above average latitude. That's the shift this article is about. The syntax for aggregation isn't complicated on its own. What takes a bit longer to get comfortable with is the order things actually happen in, especially once WHERE and HAVING are both in the same query and doing different jobs.

Where it gets confusing

Say you want cities with more than three venues. The instinct, especially early on, is to write:

SELECT city, COUNT(*) AS venue_count
FROM venues
WHERE COUNT(*) > 3
GROUP BY city;

This fails. Not silently, at least — Postgres will give you an error about aggregate functions not being allowed in WHERE. But the reason it fails is worth actually understanding rather than just memorizing "use HAVING instead." WHERE filters individual rows, and it runs before any grouping happens. At the point WHERE is being evaluated, there's no such thing as "the count for this city" yet, because the rows haven't been grouped into cities at all — they're still just individual venue rows. You can't filter on something that doesn't exist yet.

Aggregate functions and GROUP BY

SELECT COUNT(*) FROM venues;
SELECT AVG(latitude) FROM venues;
SELECT city, COUNT(*) AS venue_count
FROM venues
GROUP BY city;

GROUP BY city collapses all the rows sharing the same city value into a single row per city, and the aggregate functions — COUNT, AVG, and similar — operate on each group rather than the whole table. Fairly intuitive once you've written a few of these.

HAVING, and why it exists at all

SELECT city, COUNT(*) AS venue_count
FROM venues
GROUP BY city
HAVING COUNT(*) > 3;

HAVING is what WHERE would be if it ran after grouping instead of before. It filters groups, not individual rows, and by the time it runs, the counts and averages actually exist as real values you can compare against. That's the whole reason it's a separate keyword rather than Postgres just letting you use aggregates inside WHERE — the two clauses run at genuinely different stages of query execution, filtering different things.

A decent way to remember which is which: WHERE happens before grouping, so it can't see aggregate results yet. HAVING happens after, so it can.

Subqueries — a query inside a query

-- scalar subquery
SELECT name FROM venues
WHERE latitude > (SELECT AVG(latitude) FROM venues);

Here the inner query runs first, computes a single number — the average latitude across all venues — and the outer query uses that number as a comparison value. This is called a scalar subquery because it returns exactly one value.

-- subquery in FROM (a derived table)
SELECT city, avg_lat FROM (
    SELECT city, AVG(latitude) AS avg_lat
    FROM venues GROUP BY city
) AS city_stats
WHERE avg_lat > 23;

This one's a bit different — the subquery in the FROM clause produces an entire table-like result (city-level averages), which the outer query then filters and selects from, as though it were a real table. It needs an alias (city_stats here) because Postgres requires every table-like thing in a FROM clause to have a name, even a temporary computed one.

-- correlated subquery — references the outer query, runs per row
SELECT v.name FROM venues v
WHERE EXISTS (
    SELECT 1 FROM closures c WHERE c.venue_id = v.id
);

This last one is genuinely different from the first two. It's called "correlated" because the inner query references v.id from the outer query — it's not a standalone query you could run on its own, it depends on which outer row is currently being evaluated. Conceptually, Postgres runs this subquery once per row of the outer query, checking "does at least one closure exist for this specific venue." Worth being careful with these on large tables, since running a subquery per row can get expensive if it's not written efficiently.

EXISTS vs IN

EXISTS stops as soon as it finds a single matching row — it doesn't need to know how many matches there are, just whether at least one exists, which also means it handles nulls in the subquery result more predictably. IN builds out the entire subquery result first and then checks membership against it. For "does at least one related row exist" type questions, EXISTS is usually the better fit, both for performance and for correctness around nulls.

CTEs: naming your intermediate steps

WITH closed_venues AS (
    SELECT venue_id FROM closures
)
SELECT * FROM venues
WHERE id IN (SELECT venue_id FROM closed_venues);

A CTE — the WITH clause — lets you name a piece of a query and reference it later in the same statement, instead of nesting subqueries inside each other until the query becomes hard to read top to bottom. This particular example is simple enough that it wouldn't really need a CTE, but the value becomes obvious once a query has three or four logical steps chained together — each one gets a name, and the final query reads almost like a short paragraph instead of a wall of nested parentheses.

CTEs are also how you handle recursive queries, using WITH RECURSIVE, which is the standard way to deal with hierarchical data — something like a category tree, where categories can have subcategories that have their own subcategories. That's a bigger topic on its own and probably deserves its own explanation somewhere down the line rather than a paragraph here.

One historical detail worth knowing if it comes up: older versions of Postgres treated CTEs as what's called an "optimization fence" — meaning the planner would essentially compute the whole CTE first, in isolation, before using it in the rest of the query, even if that wasn't the most efficient approach. Since Postgres 12, CTEs are generally optimized more like subqueries, folded into the overall query plan rather than treated as a hard boundary. If someone asks why you'd use a CTE instead of a subquery, readability and reusability within the same query are the main reasons now — the performance difference that used to matter mostly doesn't anymore, on current versions.

Putting a few of these together

Say you want cities where the average venue latitude is unusually high, but only among cities that actually have a meaningful number of venues — filtering out cities with just one or two entries that could skew the average:

WITH city_stats AS (
    SELECT city, AVG(latitude) AS avg_lat, COUNT(*) AS venue_count
    FROM venues
    GROUP BY city
)
SELECT city, avg_lat, venue_count
FROM city_stats
WHERE venue_count >= 5
ORDER BY avg_lat DESC;

The CTE computes both the average and the count per city in one pass, and the outer query then filters on venue_count, which at this point is just a regular column value, not an aggregate anymore — so it goes in WHERE, not HAVING, since the grouping already happened inside the CTE. This is a good example of how naming an intermediate result makes the logic of a query clearer than trying to cram the same thing into one query with HAVING doing double duty.

Where this leaves you

WHERE filters rows, HAVING filters groups, and the reason for the split comes down to when each one actually runs relative to GROUP BY. Subqueries let you use the result of one query inside another, and CTEs let you name those intermediate steps instead of nesting them — mostly for readability at this point, less so for performance on modern Postgres versions.

Next up: indexes — how to actually make these queries fast, and just as importantly, when adding an index doesn't help at all.

Joins In PostgreSQL

Nishant Gaurav — Sat, 17 Oct 2026 17:30:00 +0000

Joins are one of those topics where everyone nods along when you explain them, then freezes up the moment they have to write one on the spot, in front of someone, without being able to run it first and check. I think that's mostly because the four main types genuinely blur together if you've only half-memorized their definitions instead of actually understanding what each one is doing to the result set. This article is my attempt at explaining them the way I eventually understood them, not the way they're usually first presented.

Why this is the part people freeze on

The syntax for a join isn't hard. SELECT ... FROM a JOIN b ON a.x = b.y — that's most of it. What actually trips people up is reasoning about what rows come back, especially when the two tables don't match up perfectly. A venue with no closures. A closure somehow pointing at a venue that doesn't exist. These edge cases are exactly what separates the four join types, and if you've only memorized "LEFT JOIN keeps everything on the left," without really internalizing why, you'll get it backwards the first time you're asked to write one under any pressure.

Working through all four, using the same two tables

Assume venues and closures, where closures.venue_id references venues.id.

-- INNER JOIN: only rows that match in both tables
SELECT v.name, c.closed_on
FROM venues v
INNER JOIN closures c ON v.id = c.venue_id;

This gives you venues that actually have at least one closure recorded. A venue with zero closures just doesn't show up at all — not as a row with nulls, it's simply absent from the result.

-- LEFT JOIN: all venues, even ones with no closures
SELECT v.name, c.closed_on
FROM venues v
LEFT JOIN closures c ON v.id = c.venue_id;

Every venue shows up here, whether or not it has closures. For venues with no matching closure row, c.closed_on just comes back as null. This is probably the join you'll reach for most often in practice, since "show me all of X, along with whatever related Y exists" is an extremely common shape of question.

-- RIGHT JOIN: all closures, even orphaned ones
SELECT v.name, c.closed_on
FROM venues v
RIGHT JOIN closures c ON v.id = c.venue_id;

The mirror image of LEFT JOIN — every closure shows up, even if somehow its venue_id doesn't match any real venue. In practice this is rare to actually use, since you can just swap the table order and use LEFT JOIN instead, which is why most people default to always writing LEFT JOIN and almost never reach for RIGHT JOIN even though it exists.

-- FULL OUTER JOIN: everything from both sides
SELECT v.name, c.closed_on
FROM venues v
FULL OUTER JOIN closures c ON v.id = c.venue_id;

Everything from both tables, matched where possible, with nulls filling in on whichever side doesn't have a match. Less common day to day, but genuinely useful when you need to audit both directions at once — venues with no closures, and closures pointing at venues that no longer exist.

A simpler way I ended up explaining these to myself: INNER only wants pairs that actually match. LEFT wants everything from the left table no matter what, filling in gaps with null. RIGHT is the same idea flipped. FULL OUTER wants everything from both, period.

Self joins, and the thing that goes wrong without an ON clause

-- venues in the same city, paired against each other
SELECT a.name, b.name
FROM venues a
JOIN venues b ON a.city = b.city AND a.id < b.id;

This is a self join — the same table joined against itself, using two aliases (a and b) so Postgres can tell which "copy" of the table a given column reference belongs to. The classic real-world case is an employee-manager table, where each row references another row in the same table. Here it's a more contrived example — pairing up venues that share a city — but the mechanics are identical. The a.id < b.id part matters too: without it, you'd get every pair twice, once in each order, plus every venue paired with itself.

The mistake that catches almost everyone at least once: forgetting the ON condition entirely, or getting it wrong in a way that matches everything. That gives you a CROSS JOIN by accident — every row from one table matched with every row from the other, a full Cartesian product. If a join query comes back with a suspiciously huge row count, way more than you'd expect, this is almost always why. It's usually pretty obvious once you see it, but it's an easy thing to miss for a minute if you're staring at a query that looks syntactically fine.

A real CROSS JOIN does have legitimate uses — generating every combination of two sets, like all dates crossed with all venues for a reporting calendar that needs a row for every date-venue pair regardless of whether anything happened. It's rare, but it's worth knowing it's an intentional tool sometimes, not just a mistake that happens to have a name.

Working through a real query

Say you want a report: every venue, and how many times it's been closed, including venues that have never been closed at all, showing zero rather than being left out.

SELECT v.name, COUNT(c.id) AS closure_count
FROM venues v
LEFT JOIN closures c ON v.id = c.venue_id
GROUP BY v.name
ORDER BY closure_count DESC;

The LEFT JOIN is doing the actual work here — it's what makes sure venues with zero closures still show up, instead of silently vanishing the way they would with an INNER JOIN. COUNT(c.id) matters more than it looks like it should: for a venue with no closures, the joined c.id value is null, and COUNT() doesn't count nulls, so it correctly comes back as zero. If you'd written COUNT(*) instead, every row would count as one regardless of whether c.id was null, and a venue with no closures would incorrectly show a count of one instead of zero, because the LEFT JOIN still produces exactly one row for it, just with null values in it.

That's a small detail, but it's exactly the kind of thing that produces a report with numbers that are subtly wrong in a way that's easy to miss during a quick review.

Where this leaves you

The four join types aren't really four different things to memorize — they're one idea (match rows between two tables based on a condition) with different rules about what happens when a match doesn't exist on one side or the other. If you can explain, out loud, what happens to an unmatched row in each of the four cases, you understand joins. If you're just recalling which keyword goes with which definition, that's worth practicing out loud a few times before it comes up somewhere that actually matters.

Next up: aggregations — GROUP BY, HAVING, and the subquery patterns that come up constantly once you start asking more complex questions of your data.

Where Clause In PostgreSQL

Nishant Gaurav — Fri, 16 Oct 2026 17:30:00 +0000

WHERE is one of the first things you learn in SQL, and it's also one of the things people stop paying attention to too early. It feels solved — you know the syntax, you've written a hundred of these, why would there be more to say about it. But there are a few Postgres-specific details in here that genuinely surprised me, and a habit around combining conditions that I only picked up after seeing it go wrong once.

Where this quietly causes bugs

Say you've got a query filtering venues that are open, and either in Bhopal or have no city set yet:

SELECT * FROM venues
WHERE is_closed = FALSE
  AND city = 'Bhopal' OR city IS NULL;

This looks reasonable at a glance. It's wrong. Without parentheses, AND binds tighter than OR, so this actually reads as "give me rows where (closed is false and city is Bhopal), or (city is null) — regardless of whether they're closed." You'd get back closed venues with no city set, which almost certainly isn't what you meant.

This is the kind of bug that doesn't announce itself. The query runs, it returns rows, nothing errors — it's just quietly including data it shouldn't. You usually catch it because a number looks off in a report, not because anything crashed.

The basics, and the Postgres-specific one

SELECT * FROM venues WHERE latitude > 23.2;
SELECT * FROM venues WHERE name IN ('DB Mall', 'Upper Lake');
SELECT * FROM venues WHERE name LIKE 'Van%';        -- case-sensitive pattern match
SELECT * FROM venues WHERE name ILIKE 'van%';       -- case-insensitive
SELECT * FROM venues WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';
SELECT * FROM venues WHERE city IS NULL;

Most of this is standard SQL and probably familiar already. ILIKE is the one worth calling out specifically, because it's Postgres-specific — MySQL's LIKE happens to behave case-insensitively by default depending on the collation setting, which is its own small gotcha if you're used to that behavior and then move to Postgres and suddenly your pattern matches stop working the way you expect. In Postgres, LIKE is case-sensitive, full stop, and ILIKE is the explicit case-insensitive version. If someone asks about this in an interview, it's usually less about the syntax and more about whether you've actually worked with Postgres specifically rather than SQL in the abstract.

IN versus a chain of OR conditions is mostly a readability choice at this scale — WHERE name IN ('DB Mall', 'Upper Lake') says the same thing as WHERE name = 'DB Mall' OR name = 'Upper Lake', just more compactly once you're checking against more than two or three values.

Combining conditions without shooting yourself

SELECT * FROM venues
WHERE is_closed = FALSE
  AND (city = 'Bhopal' OR city IS NULL);

The fix for the earlier bug is just parentheses, grouping the OR explicitly. The bigger point isn't the specific bug — it's that relying on memorized operator precedence rules, and getting them right every time under pressure, isn't a great strategy. Parenthesizing mixed AND/OR conditions costs nothing and means the query reads correctly on its own, without anyone needing to recall precedence rules to verify it during a code review.

I started doing this by default after the bug above, not because someone told me to as a rule. It's one of those habits that feels unnecessary right up until it isn't.

Sorting and limiting

SELECT * FROM venues ORDER BY created_at DESC LIMIT 5;

Not much complexity here on its own — ORDER BY sorts, LIMIT caps how many rows come back. The thing worth knowing now, even though it's really a topic for the indexing article later in this series, is that ORDER BY on a large table without a supporting index means Postgres has to sort the entire result set before it can hand back even a LIMIT 5. It works fine on a few thousand rows. It gets noticeably slower as a table grows, which is part of why indexing decisions and query patterns end up tied together more than they look at first.

Putting a few of these together

Say you're building the "nearby open venues in my city, or venues where we don't know the city yet" view for the dashboard — a genuinely realistic filter, since city data synced from an external API is exactly the kind of field that sometimes comes back empty:

SELECT name, latitude, longitude, city
FROM venues
WHERE is_closed = FALSE
  AND (city ILIKE 'bhopal' OR city IS NULL)
ORDER BY name ASC
LIMIT 20;

This uses ILIKE instead of = for the city comparison, since city names synced from an external source aren't always consistently capitalized — Bhopal versus bhopal versus BHOPAL depending on which upstream feed the data came from. The parentheses around the OR keep the null-handling correct. And LIMIT caps it to a reasonable page size instead of pulling back everything at once. None of these individually is complicated, but stacking them correctly, in the right order and with the right grouping, is really what filtering well in practice comes down to.

Where this leaves you

WHERE clauses feel basic enough that it's easy to stop thinking carefully about them once you know the syntax. The two things actually worth carrying forward: parenthesize mixed AND/OR conditions every time, even when you're fairly sure you have precedence right, and remember that ILIKE is a Postgres-specific tool, not something you can assume exists everywhere.

Next up: joins — which is where a lot of the real anxiety around SQL interviews tends to live, and honestly, for good reason, since it's easy to get subtly wrong in a way that still returns results.

Constraints in PostgreSQL

Nishant Gaurav — Thu, 15 Oct 2026 17:30:00 +0000

There's a difference between "we validate this in the application" and "the database won't let this happen." I used to assume application-level validation was enough — check the input in Flask, reject anything weird, move on. Then I had a bug where a background job inserted a closure record for a venue that had already been deleted, because the validation logic lived in one code path and the background job used a different one that skipped it. The database happily accepted it, because nothing at the database level said it couldn't.

Constraints are how you stop trusting your application code to always remember the rules, and instead make the rules structurally true no matter what code is writing to the table.

What happens without them

Picture a closures table that references venues by ID, but with no foreign key set up — just a plain integer column that's supposed to match an existing venue. For a while, everything looks fine, because your application code is careful about it. Then someone writes a one-off script to backfill some historical data, or a bug in some unrelated feature deletes a venue without cleaning up its closures first, and now you've got closure records pointing at venues that don't exist. Nothing errors. Nothing crashes. The data is just quietly wrong, and you don't find out until a report starts showing null venue names where there shouldn't be any.

This is the actual argument for constraints — not that they're a best practice you're supposed to follow, but that they close off entire categories of bugs from ever happening in the first place, instead of relying on every single piece of code that touches the table to remember the rules correctly.

The constraints worth knowing

Constraint	What it enforces	Example
`PRIMARY KEY`	uniqueness + not null, one per table	`id SERIAL PRIMARY KEY`
`FOREIGN KEY`	referential integrity between tables	`venue_id INTEGER REFERENCES venues(id)`
`UNIQUE`	no duplicate values in a column (or column combo)	`UNIQUE(name, city)`
`NOT NULL`	column can't be empty	`name VARCHAR(100) NOT NULL`
`CHECK`	custom rule on a value	`CHECK (latitude BETWEEN -90 AND 90)`
`DEFAULT`	value used when none is provided	`DEFAULT NOW()`

CREATE TABLE closures (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER REFERENCES venues(id) ON DELETE CASCADE,
    closed_on DATE NOT NULL,
    risk_score NUMERIC CHECK (risk_score BETWEEN 0 AND 1)
);

A few of these are pretty self-explanatory — NOT NULL and DEFAULT don't need much unpacking. CHECK is worth pointing out specifically because it's underused. Most people know about primary and foreign keys, fewer people think to add something like CHECK (latitude BETWEEN -90 AND 90) even when the column obviously has a valid range. It's a small thing, but it means a typo that puts in 230 instead of 23.0 gets rejected immediately instead of silently breaking a map rendering somewhere downstream.

Deciding what happens when a parent row disappears

This is the part of foreign keys that actually requires some thought, rather than just copying REFERENCES table(column) and moving on.

venue_id INTEGER REFERENCES venues(id) ON DELETE CASCADE

CASCADE — deleting a venue deletes its closures too. Makes sense when the child data genuinely has no meaning without the parent. A closure record for a venue that no longer exists isn't useful information, it's just orphaned data.

RESTRICT (the default-ish behavior if you don't specify anything) — blocks deleting a venue if closures still reference it. This forces whoever's doing the deleting to think about what should happen to the related data first, instead of it disappearing silently.

SET NULL — keeps the closure row, but sets venue_id to null. Makes sense when the historical record matters even after the venue itself is gone — say, for reporting purposes, where you still want to count "there was a closure event" even if you can no longer say which venue it was.

None of these is universally correct. It depends on whether the child data is meaningless without the parent, needs explicit cleanup, or should survive on its own. I've seen people default to CASCADE everywhere because it's the path of least resistance, and then lose data they actually needed later because a parent row got deleted for an unrelated reason.

Where constraints get uncomfortable in practice

Constraints are great until you're trying to add one to a table that already has bad data in it. Adding NOT NULL to an existing column, or a CHECK constraint, fails outright if even one existing row violates it — Postgres won't just apply it going forward, it validates the whole table. On a small table that's a non-issue. On a large one, you sometimes need to clean up the offending rows first, or add the constraint as NOT VALID and validate it separately later, so you're not holding a lock on the table while every existing row gets checked.

The other thing that catches people off guard: constraints add real overhead on writes, not just reads. Every INSERT or UPDATE against a table with a foreign key means Postgres also has to check that the referenced row actually exists. It's usually not noticeable, but on a very high-throughput table, it's worth being aware that constraints aren't free — they're a trade, correctness for a small amount of write cost, and it's almost always worth it, but it's not nothing.

Designing the venues/closures relationship from scratch

Say you're setting this schema up for the first time, deciding what constraints actually belong on it, not just what the syntax looks like.

venues.id is the primary key — straightforward, every table needs a way to uniquely identify a row. venues.name gets NOT NULL, because a venue with no name isn't meaningful data, it's a placeholder that shouldn't exist. If venues need to be unique per city — no duplicate "DB Mall" entries within the same city — that's UNIQUE(name, city), which catches accidental duplicate entries from a sync job without blocking two venues with the same name in different cities.

closures.venue_id references venues.id. The decision that actually takes thought is the ON DELETE behavior. If venues in this system are only ever marked is_closed = TRUE rather than actually deleted — which is the more common pattern in practice, since you usually want the history — then this decision barely matters, because venues rarely get hard-deleted in the first place. But if hard deletes are a real possibility, RESTRICT is probably the safer default here, since it forces a conscious decision about what happens to the closure history instead of losing it silently through a CASCADE.

closures.risk_score, if it's meant to represent a probability, gets CHECK (risk_score BETWEEN 0 AND 1). This is exactly the kind of constraint that's easy to skip because "the application already validates this," and exactly the kind that saves you when some other code path — a script, a migration, an admin tool — doesn't.

Where this leaves you

Constraints move the responsibility for correctness out of application code, where it has to be remembered and re-implemented correctly everywhere data gets written, and into the database itself, where it's enforced no matter what's doing the writing. The ON DELETE decision in particular is worth actually thinking through per relationship instead of defaulting to whatever's fastest to type.

A good exercise, and one that comes up constantly in interviews, is being asked to design a small schema with two or three tables and their constraints out loud. It's worth practicing that out loud, not just reading through examples like this one, because explaining why you chose CASCADE over RESTRICT is a different skill than recognizing the syntax.

Next up: filtering, sorting, and pattern matching — the WHERE clause in more depth, and a few things that are genuinely Postgres-specific.

INSERT, SELECT, UPDATE, DELETE: PostgreSQL

Nishant Gaurav — Wed, 14 Oct 2026 17:30:00 +0000

At some point you stop thinking about SQL as "a language" and it just becomes muscle memory — you know what an UPDATE without a WHERE clause does before you even finish typing it, because you've either done it once by accident or heard someone else's horror story about it. That's basically what this article is about. Not anything exotic, just the four commands that make up most of what you actually do day to day, and the handful of ways each one bites people early on.

Why this is worth slowing down for

It's tempting to skim past CRUD because it feels like the "easy" part of SQL — everyone's written a SELECT before. But most of the actual production incidents I've read about, and a couple I've caused myself in test databases on purpose just to see what happens, come from these four commands specifically. Not from window functions or CTEs. From a missing WHERE clause, or a SELECT * that broke when someone renamed a column six months later.

The syntax is simple. The discipline around using it correctly is the part that takes longer to build.

INSERT: getting data in

INSERT INTO venues (name, latitude, longitude)
VALUES ('Van Vihar', 23.2237, 77.3910);

-- multiple rows in one call — fewer round trips, faster
INSERT INTO venues (name, latitude, longitude) VALUES
    ('Upper Lake', 23.2415, 77.3550),
    ('DB Mall', 23.2280, 77.4340);

The multi-row version is worth using whenever you're inserting more than one row at a time, even for something small. It's not just a style preference — one round trip to the database instead of several actually adds up, especially if you're populating a table with anything more than a handful of rows.

Here's a detail that connects back to how Postgres works internally: every INSERT creates a brand new row version. Postgres doesn't have a concept of "editing in place" the way you might picture it. That's tied directly to MVCC, which is a much bigger topic I'll get into properly later in this series — for now it's enough to know that even INSERT isn't as simple under the hood as "write these bytes to a table."

SELECT: the one you'll use more than everything else combined

SELECT name, latitude, longitude
FROM venues
WHERE is_closed = FALSE
ORDER BY name ASC
LIMIT 10 OFFSET 0;

WHERE filters rows before anything gets grouped or aggregated. ORDER BY sorts whatever's left. LIMIT/OFFSET handle pagination, though OFFSET gets slow once a table grows large — that's a topic for the indexing article later on, but it's worth knowing it exists as a limitation now.

The thing that trips people up early, and that I did plenty myself when I was starting out, is reaching for SELECT * out of habit. It's fine while you're exploring data in psql. It's a problem in application code, for a few reasons that aren't obvious until they bite you: it breaks silently if someone adds or renames a column later, it pulls back data you don't actually need, and it can block certain optimizations — like an index-only scan, where Postgres could answer a query using just the index without touching the table at all, but only if it knows exactly which columns you need.

UPDATE: changing what's already there

UPDATE venues
SET is_closed = TRUE
WHERE id = 5;

The one rule here that actually matters: never run an UPDATE without a WHERE clause unless you genuinely mean to update every row in the table. This is one of those things that sounds obvious written down, and then someone runs a query against production instead of the staging environment they meant to be in, and every row silently gets the same value. It happens more than you'd think, usually from copy-pasting a query and forgetting to re-add the WHERE at the end.

DELETE: same warning, different verb

DELETE FROM venues WHERE is_closed = TRUE;

Same logic as UPDATE — no WHERE means "delete everything," and Postgres will do exactly that without complaining, because as far as it's concerned that's a perfectly valid instruction. If you're ever testing a DELETE on something you're not 100% sure about, wrapping it in a transaction first (BEGIN, run the delete, check the result, then COMMIT or ROLLBACK) is a cheap habit that saves you from this exact situation.

Where it actually gets interesting: UPSERT

INSERT INTO venues (id, name, latitude, longitude)
VALUES (5, 'Van Vihar National Park', 23.2237, 77.3910)
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;

This is Postgres's answer to "insert this row, but if it already exists, update it instead" — a pattern that comes up constantly in real projects. Syncing data from an external API is the most common case: you're pulling venue data from some third-party source on a schedule, and you don't know ahead of time whether a given venue is already in your table or not. Instead of writing a SELECT to check first and then branching into either an INSERT or an UPDATE, ON CONFLICT handles both in one statement.

EXCLUDED refers to the row that would have been inserted if there hadn't been a conflict — so EXCLUDED.name means "the name value from the row I was trying to insert." If you just want to skip duplicates entirely without updating anything, ON CONFLICT DO NOTHING does that.

If you get asked how to handle duplicate key inserts gracefully, this is the expected answer, and it's worth mentioning DO NOTHING alongside DO UPDATE since they solve slightly different problems — one is deduplication, the other is a real upsert.

Putting it together: syncing venue data from an external source

Say your dashboard pulls venue data from a city API once a day, and venues can be added, renamed, or marked closed on their end. Here's roughly how that sync would actually look, combining a few of these commands:

INSERT INTO venues (id, name, latitude, longitude, is_closed)
VALUES (12, 'Central Park Cafe', 23.2510, 77.4020, FALSE)
ON CONFLICT (id)
DO UPDATE SET
    name = EXCLUDED.name,
    is_closed = EXCLUDED.is_closed;

Run once per venue returned from the API, this single statement handles both new venues and updates to existing ones, without you needing to check which case you're in first. Compare that to the naive version — a SELECT to check existence, then an if/else in application code choosing between INSERT and UPDATE — and it's not just shorter, it also avoids a race condition where two sync jobs running close together could both decide the row doesn't exist yet and both try to insert it.

Where this leaves you

These four commands, plus ON CONFLICT, cover most of what you'll actually type against a database in a normal week. The syntax isn't the hard part — the discipline of always including WHERE on anything destructive, and reaching for ON CONFLICT instead of a manual check-then-branch pattern, is what actually separates code that survives contact with real data from code that doesn't.

Next up: constraints — how to make bad data structurally impossible to insert in the first place, instead of relying on catching it after the fact.

PostgreSQL Data Types:

Nishant Gaurav — Tue, 13 Oct 2026 17:30:00 +0000

There's a specific kind of bug that doesn't show up right away. A latitude column stored as FLOAT, and months later your distance calculations are just slightly off in a way that's hard to pin down. Or a price column using the same type, and a customer notices their total is a cent wrong, in a way that somehow compounds across enough transactions to matter. Nobody wrote a bug in the usual sense. The data type was the bug, and it had been sitting there quietly since the table was created.

Choosing a column's type feels like the most mechanical part of designing a schema — pick something that looks roughly right and move on. I used to treat it that way too. It's actually one of the few decisions that's genuinely annoying to undo later, because by the time you notice it's wrong, there's real data sitting in that column.

A table that works fine until it doesn't

Say you're building a venues table for a dashboard that tracks locations and closures. It's tempting to just reach for whatever feels familiar — VARCHAR for text, FLOAT for anything with a decimal, INTEGER for IDs without thinking twice.

That table inserts rows fine. It passes whatever tests you write in the first week. Then a risk score computed from FLOAT values starts drifting in a way that doesn't reproduce consistently, or the ID column runs into its ceiling because nobody considered BIGINT, or a TIMESTAMP column with no timezone awareness makes an event look like it happened in a different hour depending on which server handled it. None of these are exotic problems. They're just what happens when types don't get real thought up front.

Going through the types one at a time

The table itself

CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    latitude DOUBLE PRECISION NOT NULL,
    longitude DOUBLE PRECISION NOT NULL,
    is_closed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

This is a fairly realistic starting point for a venue-tracking dashboard. Every type choice here is worth understanding on its own, not just copying because it looked right in a tutorial.

INTEGER / BIGINT — good for counts and IDs. The distinction matters once you think about scale: INTEGER tops out around 2.1 billion. If there's any real chance a table could grow past that — a high-volume events table, say — starting with BIGINT costs nothing now and avoids a painful migration later.

SERIAL / BIGSERIAL — the usual auto-incrementing ID setup. It's worth knowing this isn't actually a separate type. It's an INTEGER (or BIGINT) column backed by a sequence that hands out the next value automatically. That distinction matters later if you ever need to work with the sequence directly.

VARCHAR(n) vs TEXT — this one surprised me coming from MySQL. In Postgres these perform almost identically. TEXT is usually the better default unless you actually need to enforce a maximum length — VARCHAR(n) is really just TEXT with a length constraint bolted on, not a different storage format underneath.

NUMERIC(p,s) vs DOUBLE PRECISION — this is the one that actually causes real incidents. NUMERIC stores exact decimal values. DOUBLE PRECISION is a floating-point approximation. Don't use FLOAT/DOUBLE PRECISION for money — the rounding errors aren't a hypothetical, they show up given enough transactions. DOUBLE PRECISION is fine for something like geospatial coordinates, where the imprecision at that scale doesn't really change the result.

BOOLEAN — flags. Not much to say here.

TIMESTAMP vs TIMESTAMPTZ — TIMESTAMPTZ stores the value normalized to UTC and converts it on display based on the client's timezone. TIMESTAMP just stores whatever you gave it, with no timezone awareness at all. For pretty much any real app — anything with users spread across timezones, or a backend and frontend that might not agree on local time — TIMESTAMPTZ is the safer default, even though TIMESTAMP looks simpler at first glance.

JSONB — binary-stored, indexable, faster to query than JSON, which is just stored as raw text. Anything semi-structured, like a payload synced from a third-party API, belongs in JSONB by default.

UUID — useful when IDs need to be generated somewhere other than the database itself, or across multiple systems, without collision risk. Needs either gen_random_uuid() or an extension like pgcrypto or uuid-ossp.

The two questions that always come up

"Difference between VARCHAR and TEXT?" — functionally almost identical in Postgres, unlike MySQL. VARCHAR(n) just adds a length constraint on top of the same underlying storage.

"Difference between JSON and JSONB?" — JSON stores the exact text, preserving whitespace and key order, but it's slower to query and doesn't support much indexing. JSONB stores a parsed binary format, which loses the exact original formatting but is faster and indexable through GIN indexes. Default to JSONB most of the time, and only reach for plain JSON if you specifically need to preserve the text exactly as it came in.

Altering a table isn't always as harmless as it looks

ALTER TABLE venues ADD COLUMN city VARCHAR(50);
ALTER TABLE venues DROP COLUMN city;
ALTER TABLE venues ALTER COLUMN name TYPE TEXT;
ALTER TABLE venues RENAME COLUMN name TO venue_name;

These look simple, and on a small table they are. The problem shows up at scale — altering a table with millions of rows during peak traffic can lock it, blocking reads and writes for however long the operation takes. ADD COLUMN with a default value used to force a full table rewrite in older Postgres versions. That got a lot cheaper starting in Postgres 11 for constant defaults, but it's worth knowing that history exists, since not every production system runs the latest version, and assuming modern behavior on an older cluster is a reasonable way to cause an outage.

DROP vs TRUNCATE vs DELETE

DROP TABLE venues;          -- removes the table and its structure entirely
TRUNCATE TABLE venues;      -- empties all rows, keeps the structure
DELETE FROM venues;         -- removes rows, can be filtered, slower, undoable within a transaction

DELETE works row by row, gets logged, supports WHERE, and is fully transaction-safe — you can roll it back. TRUNCATE is close to instant because it deallocates whole pages instead of scanning rows one at a time. It can't use WHERE, but it's still transactional in Postgres, so it can be rolled back too, as long as you're inside a transaction that hasn't committed. DROP removes the entire table, structure included, not just what's inside it.

Fixing a type before it becomes a real migration

Say the venues table above shipped with risk_score DOUBLE PRECISION, because someone figured "it's just a number between 0 and 1, a float is fine." A few weeks later, a report comparing risk scores across venues starts showing inconsistent sort order for values that should be identical — classic floating-point comparison drift.

Caught early, the fix is a simple alteration:

ALTER TABLE venues ALTER COLUMN risk_score TYPE NUMERIC(3,2);

Caught after the table has millions of rows, this becomes a much more careful operation — possibly needing a maintenance window, or a safer pattern like adding a new column, backfilling it, and swapping it in. The gap between fixing this on day one and fixing it six months later isn't small. It's the difference between a one-line change and a planned migration.

Where this leaves you

Types in Postgres aren't just about what values a column technically accepts — they carry real assumptions about precision, timezone handling, and scale. Those assumptions are cheap to get right when you're creating the table and expensive to fix once there's data in it. Next time you write a CREATE TABLE, it's worth treating each type as a real decision, especially NUMERIC vs DOUBLE PRECISION for anything money-related, and TIMESTAMPTZ vs TIMESTAMP for anything tied to a user.

Next up: actually working with the data — INSERT, SELECT, UPDATE, DELETE, and the upsert pattern for handling "insert or update" cleanly.

Getting Into PostgreSQL: psql, Connections, and the First Commands You Actually Need

Nishant Gaurav — Mon, 12 Oct 2026 17:30:00 +0000

Most tutorials rush past this part. "Install Postgres, connect, start querying" — as if opening the database is the boring bit you skip through to get to the real content. When I was starting, that gap was exactly where I felt lost. You're connected, technically, but you have no idea what's actually in front of you.

This article covers those first few minutes: getting into psql, figuring out what you're connected to, and a handful of commands that eventually become reflexes if you use Postgres regularly.

Why not knowing your way around costs you time

Say you inherit a project. Someone hands you a connection string, you open psql, and you're in. Now what? You don't know what databases exist on the server. You don't know what tables are in the one you're connected to. Someone mentioned a table in a Slack message three months ago and you have no idea what columns it has.

Without a small set of orientation commands, every one of those questions turns into either opening a GUI (if one's set up) or trying to remember how to query Postgres's internal system catalogs directly, which honestly nobody has memorized. There's a small handful of psql shortcuts that answer all of this in a couple of seconds, and once they're second nature, this stops being a problem.

Connecting, and the commands that orient you

Connecting

psql -U username -d database_name -h host -p 5432

Each flag does one job:

-U — the user you're connecting as
-d — the database to connect to
-h — the host, a remote server or localhost
-p — the port, and 5432 is Postgres's default. It's a small detail, but it comes up as a quick sanity-check question fairly often.

A few commands worth knowing by heart

Once you're inside psql, there's a set of meta-commands — not SQL, just shortcuts specific to psql itself, which is why they start with a backslash and don't need a semicolon:

Command	What it does
`\l`	list databases
`\c dbname`	connect to a database
`\dt`	list tables in the current schema
`\d tablename`	describe a table — columns, types, constraints, indexes
`\du`	list roles/users
`\q`	quit

\d tablename is the one I use the most. It's the fastest way to see what a table actually looks like without leaving the terminal, and it shows constraints and indexes too, not just column names — something a plain SELECT * LIMIT 1 won't tell you.

Why these are separate from SQL

psql splits things into two categories on purpose. SQL is for talking to the database engine about your data — it gets parsed, planned, and executed on the server. Meta-commands are for talking to the client about your environment, so \dt doesn't need a semicolon and responds more or less instantly, because it's not going through the same query pipeline.

If you get asked how to check a table's structure without a GUI, \d tablename is basically the expected answer. It's less about the command itself and more a quick check on whether you've actually spent time in a terminal with Postgres, versus only clicking around in a visual tool.

Where the confusion usually starts: databases, clusters, templates

Creating and dropping a database

CREATE DATABASE project_db;
DROP DATABASE project_db;

One convention worth knowing early: one database per logical application is the norm. A geospatial dashboard would get its own database, separate from anything unrelated, even if they're sitting on the same server.

In multi-tenant setups, people sometimes reach for "one database per tenant" because it feels like clean isolation. It usually isn't the right call — hundreds of databases becomes a real management headache. A tenant_id column, or separate schemas per tenant within one database, tends to hold up much better as things grow.

What CREATE DATABASE is actually doing

Here's a detail that surprised me the first time I read about it: CREATE DATABASE doesn't build an empty database from scratch. It clones a template database — template1 by default. That explains a specific category of confusing bugs. If template1 has an unexpected encoding or locale setting, every database you create afterward quietly inherits it, and the symptoms show up much later as garbled text or sorting that doesn't behave the way you'd expect.

Setting up a project database, step by step

Say you're starting the geospatial dashboard from nothing. Here's roughly how that goes in practice:

psql -U postgres -h localhost -p 5432

You're connected to the default postgres database — not your project's yet, just the entry point.

\l

Confirms nothing named venue_dashboard already exists, so you're not about to collide with something.

CREATE DATABASE venue_dashboard;
\c venue_dashboard

Now you're connected to your project's own database, kept separate from anything else on the server.

\dt

Nothing comes back — no tables yet, which is expected on a fresh database. It also confirms you're actually in the right place before running any CREATE TABLE statements.

Connect, list, create, switch, verify. It's a small sequence, but it's one you end up running at the start of most new projects, and doing it deliberately once means you're not fumbling through it later, especially in something like a live technical interview.

Where this leaves you

The few minutes before your first CREATE TABLE matter more than they seem to. Knowing \dt, \d, and \l well enough that you don't have to think about them, and understanding that CREATE DATABASE clones a template rather than starting from nothing, makes navigating Postgres a lot less stressful day to day.

Next time you sit down in psql, even in a database you know well, try running the orientation commands first anyway. It's a small habit, but it holds up well as the number of databases and tables you're dealing with grows.

Next up: actually shaping data — tables, data types, and constraints that make bad data hard to insert in the first place.

DEV Community: Nishant Gaurav

Longest Common Subsequence: A New Pattern, the Same Thinking

What Is a Subsequence?

What Is the Longest Common Subsequence?

Why This Is a DP Problem

Base Case

The Recursive Solution

Why the Recursive Solution Gets Slow

Memoization

Why initialise with -1 and not 0

The Memoized Solution

The LCS Pattern to Remember

What You Now Understand

GROUP BY In PostgreSQL

What GROUP BY can't do

Window functions keep every row

The ones that come up most

Where this actually gets used

Where this leaves you

Views vs Materialized Views In PostgreSQL

Where the confusion comes from

The two, side by side

Picking one

Where this leaves you

Transactions, ACID, and MVCC In PostgreSQL

What transactions actually give you

ACID, without the textbook phrasing

Isolation levels

MVCC — the actual mechanism underneath all of this

Why VACUUM exists

Putting it together with the venue/closure example

Where this leaves you

Indexes in PostgreSQL

The problem indexes actually solve

Creating one

When an index doesn't actually help

The index types worth being able to name

Actually checking if your query is using an index

Why OFFSET gets slow, and what to use instead

Deciding what to index on a real table

Where this leaves you

GROUP BY, HAVING, and Subqueries in PostgreSQL

Where it gets confusing

Aggregate functions and GROUP BY

HAVING, and why it exists at all

Subqueries — a query inside a query

EXISTS vs IN

CTEs: naming your intermediate steps

Putting a few of these together

Where this leaves you

Joins In PostgreSQL

Why this is the part people freeze on

Working through all four, using the same two tables

Self joins, and the thing that goes wrong without an ON clause

Working through a real query

Where this leaves you

Where Clause In PostgreSQL

Where this quietly causes bugs

The basics, and the Postgres-specific one

Combining conditions without shooting yourself

Sorting and limiting

Putting a few of these together

Where this leaves you

Constraints in PostgreSQL

What happens without them

The constraints worth knowing

Deciding what happens when a parent row disappears

Where constraints get uncomfortable in practice

Designing the venues/closures relationship from scratch

Where this leaves you

INSERT, SELECT, UPDATE, DELETE: PostgreSQL

Why this is worth slowing down for

INSERT: getting data in

SELECT: the one you'll use more than everything else combined

UPDATE: changing what's already there

DELETE: same warning, different verb

Where it actually gets interesting: UPSERT

Putting it together: syncing venue data from an external source

Where this leaves you

PostgreSQL Data Types:

Why initialise with `-1` and not `0`