Think of PostgreSQL as a very organized filing cabinet, but with some surprising tools built in. Besides normal folders and drawers, it can store messy notes, search by meaning instead of exact words, and even decide who is allowed to see each paper. That means you can keep many app features close to the data instead of building separate systems for everything.
The power move is not "use Postgres for everything". It is knowing when these built-in features let you simplify your stack, reduce sync bugs, and keep rules where the data lives. JSON blobs, search indexes, and access rules become part of the database itself, not extra code scattered across services.
Why These Features Matter
Power features are the parts of PostgreSQL that go beyond plain rows and columns. They matter when your application needs flexible documents, fast searching, per-user authorization, or event-driven coordination without introducing another datastore.
The big advantage is that these features are still queryable, transactional, and indexable. That means you can update a row and its derived search or security behavior in one system, with the same consistency guarantees as the rest of your data.
In interviews, the key question is usually not "what does this feature do?" but "why would you choose it, and what are the tradeoffs?" A strong answer explains how Postgres can replace separate document stores, search engines, or permission layers when the use case is moderate and data locality matters.
JSONB and Why It Is Not Just a Blob
JSONB stores JSON in a binary form that PostgreSQL can inspect, filter, and index. Unlike plain text JSON, it is optimized for querying specific keys and nested fields, so it works well for semi-structured data that changes over time.
Use it when most of a record is stable, but some attributes are dynamic. Common examples are user settings, product metadata, event payloads, or provider-specific fields.
The important idea is that JSONB is still data, not an opaque string. You can query inside it with operators like ->, ->>, @>, and extract fields for filtering, sorting, or indexing.
SELECT id
FROM products
WHERE attributes @> '{"color": "red"}';
Indexing JSONB the Right Way
JSONB becomes truly useful when you index the access pattern you actually use. The two common choices are GIN indexes for containment and key existence, and expression indexes for frequently queried fields extracted from the JSON.
A GIN index is great when you ask questions like "does this document contain these keys or values?" An expression index is better when you repeatedly filter on one path such as attributes->>'sku'.
The trap is assuming one index covers all JSON queries. PostgreSQL can be fast here, but only if the index matches the operator and the query shape.
CREATE INDEX idx_products_attributes_gin
ON products USING GIN (attributes);
CREATE INDEX idx_products_sku
ON products ((attributes->>'sku'));
Generated Columns for Stable Query Paths
Generated columns let PostgreSQL compute a value from other columns and store it as part of the table. They are useful when you want the convenience of JSONB or derived data, but you also want a normal typed column for indexing, constraints, or simpler queries.
This is especially handy for common JSONB fields. Instead of repeating attributes->>'sku' in many places, you can expose it as a generated text column and index that column directly.
The benefit is clarity plus performance: the derived value stays consistent with the source data, and your queries become easier to read and optimize.
ALTER TABLE products
ADD COLUMN sku text
GENERATED ALWAYS AS (attributes->>'sku') STORED;
CREATE INDEX ON products (sku);
Full-Text Search on Meaning, Not Exact Matches
Full-text search lets Postgres search documents by terms, ranking, and linguistic normalization instead of raw substring matching. It is useful for articles, tickets, FAQs, comments, or product descriptions where users expect "find relevant content" rather than "find this exact byte sequence".
The core flow is to convert text into a searchable representation, usually a tsvector, and then query it with tsquery. PostgreSQL can normalize words, handle stemming, and rank matches by relevance.
This is a different problem from LIKE '%word%'. Full-text search is about search quality and speed together, especially once you add a GIN index on the text vector.
SELECT id, title
FROM docs
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'postgres search');
Row-Level Security: Policies on Rows, Not Just Tables
Row-level security (RLS) lets PostgreSQL decide which rows a role can see or modify. Instead of checking permissions only in application code, you define policies that are enforced by the database itself.
This matters for multi-tenant systems, per-user data isolation, or any setup where a single table contains data from many principals. The key benefit is defense in depth: even if an application bug forgets a filter, the database still blocks disallowed rows.
Interviewers often look for understanding that RLS is not magic authorization by itself. You still need a way to tell Postgres who the current user or tenant is, and then write policies around that context.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id')::uuid);
LISTEN/NOTIFY for Lightweight Event Delivery
LISTEN/NOTIFY gives Postgres a simple publish-subscribe mechanism. A session can NOTIFY a channel when something changes, and another session that is LISTENing on that channel receives the event.
This is useful for low-latency coordination inside an app stack: cache invalidation, background worker wakeups, or pushing a signal to another service. It is not a durable message queue, so it should not be used as the only source of truth for critical delivery.
The useful mental model is "database-triggered signal," not "enterprise messaging system." Pair it with stored state so a missed notification can be recovered by re-reading the database.
Imagine a support app where each ticket has flexible metadata, searchable text, and tenant isolation. You store metadata in JSONB, extract priority into a generated column, index the search text with full-text search, and enforce tenant access with RLS.
A user opens the UI, the app sets app.tenant_id, and queries automatically return only that tenant's rows. Searching ticket bodies uses a tsvector index, while filtering by priority is fast because the generated column is a normal indexed value.
When a ticket changes, the app can NOTIFYticket_updates so workers refresh caches or update projections. The result is fewer moving parts, but each feature is still doing a narrow job well.
-- Simplified sketch
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE tickets ADD COLUMN priority text
GENERATED ALWAYS AS (metadata->>'priority') STORED;
CREATE INDEX ON tickets (priority);
CREATE INDEX tickets_body_fts ON tickets USING GIN (to_tsvector('english', body));