PostgreSQL Superpowers You (Probably) Didn't Know About

🚀 PostgreSQL Superpowers You (Probably) Didn't Know About

PostgreSQL isn’t just "a relational database." It’s a Swiss Army knife of data handling, loaded with features that most developers never touch, but, which can completely change how you design queries, optimize performance, and manage data.

Below is a collection of practical, lesser-known PostgreSQL features, with examples that every developer should have in their toolkit.

Query & Data Modeling Features

1. Partial Indexes

Save space and speed up targeted queries by indexing only rows matching a condition:

CREATE INDEX idx_active_users
  ON users (last_login)
  WHERE is_active = true;

2. Expression Indexes

Index the result of an expression, not just the raw column—perfect for case-insensitive lookups.

CREATE INDEX idx_lower_email
  ON users (LOWER(email));

3. Covering Indexes (INCLUDE)

Prevent extra lookups by storing additional columns inside an index.

CREATE INDEX idx_orders_customer
  ON orders (customer_id)
  INCLUDE (order_date, total_cost);

4. Table Inheritance & Partitioning

Split huge tables into partitions for performance:

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  order_date date NOT NULL
) PARTITION BY RANGE (order_date);

Advanced Column Features

5. Generated Columns

Have PostgreSQL auto-compute derived values for you—useful for denormalization.

CREATE TABLE users (
  full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

6. Domains (Custom Data Types with Rules)

Reusable column constraints for safer schemas.

CREATE DOMAIN positive_int AS integer
  CHECK (VALUE > 0);

CREATE TABLE items (
  quantity positive_int
);

7. JSONB + GIN Indexes

Efficient semi-structured data with rich querying.

CREATE INDEX idx_users_profile
  ON users USING gin (profile jsonb_path_ops);

8. Array Columns

Native array support with efficient operators.

SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

9. HSTORE (Lightweight Key-Value Data)

If you don’t need full JSONB, hstore offers simpler key-value storage.

CREATE EXTENSION hstore;

CREATE TABLE books (
  id serial PRIMARY KEY,
  metadata hstore
);

INSERT INTO books (metadata)
VALUES ('author => "Homer", genre => "Epic"');

Querying & Functions

10. Window Functions

Running totals, rankings, moving averages—all built-in.

SELECT user_id, order_date,
       SUM(total_cost) OVER (
         PARTITION BY user_id ORDER BY order_date
       ) AS running_total
FROM orders;

11. Common Table Expressions (CTEs) + RECURSIVE

Model hierarchical/graph style queries easily.

WITH RECURSIVE subordinates AS (
  SELECT id, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

12. Materialized Views

Persist query results for later use.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', order_date) AS month, SUM(total_cost) AS total
FROM orders
GROUP BY 1;

Refresh them with

REFRESH MATERIALIZED VIEW monthly_sales;

13. Table Functions (RETURNS TABLE)

Custom reusable functions that act like a virtual table:

CREATE FUNCTION top_customers(n int)
RETURNS TABLE (customer_id int, spent numeric) AS $$
  SELECT customer_id, SUM(total_cost)
  FROM orders
  GROUP BY customer_id
  ORDER BY SUM(total_cost) DESC
  LIMIT n;
$$ LANGUAGE sql;

14. Full-Text Search (FTS)

Postgres has its own search engine hidden inside.

SELECT *
FROM articles
WHERE to_tsvector('english', content)
      @@ to_tsquery('postgres & indexing');

15. FILTER in Aggregates

Aggregate with conditions inline:

SELECT
  COUNT(*) FILTER (WHERE status = 'active') AS active_users,
  COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_users
FROM users;

Reliability & Safety

16. Row-Level Security (RLS)

Fine-grained per-row access control:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy
  ON orders
  FOR SELECT
  USING (customer_id = current_user_id());

17. ON CONFLICT DO UPDATE (Upserts)

A single query to insert or update.

INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name;

18. Foreign Data Wrappers (FDW)

Query external data sources like they’re local tables.

CREATE EXTENSION postgres_fdw;

CREATE SERVER remotepg FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote_host', dbname 'mydb');

19. Exclusion Constraints

Prevent overlapping ranges, which is great for calendaring or scheduling.

CREATE TABLE reservations (
  room_id int,
  during tstzrange,
  EXCLUDE USING gist (
    room_id WITH =,
    during WITH &&
  )
);

Prevents two reservations from overlapping for the same room.

20. Advisory Locks (User-Defined Locks)

Application-level locks for distributed coordination.

-- Acquire lock
SELECT pg_advisory_lock(12345);

-- Release lock
SELECT pg_advisory_unlock(12345);