</>

#postgresql

9 snippets tagged with #postgresql

sqlintermediate

Keyset Pagination vs Offset

Efficient keyset (cursor) pagination pattern compared to traditional OFFSET for large datasets in PostgreSQL.

#pagination#performance
sqlintermediate

UPSERT with ON CONFLICT

Insert or update rows atomically using PostgreSQL ON CONFLICT clause with partial indexes and conditions.

#upsert#postgresql
sqladvanced

Full-Text Search with Ranking

PostgreSQL full-text search using tsvector, tsquery, and ts_rank with trigram similarity for fuzzy matching.

#full-text-search#postgresql
sqlintermediate

Window Functions with RANK and LAG

Use window functions to rank rows, calculate running totals, and compare with previous rows without self-joins.

#window-functions#rank
sqlintermediate

JSON Aggregation and Querying

Aggregate related rows into JSON arrays and query JSONB columns with PostgreSQL native JSON operators.

#json#jsonb
sqlintermediate

Soft Delete with Filtered Indexes

Implement soft deletes using a deleted_at column with partial indexes and views for transparent querying.

#soft-delete#pattern
sqladvanced

Audit Log Trigger Function

Automatically record all INSERT, UPDATE, and DELETE operations into an audit log table via PostgreSQL triggers.

#audit#trigger
sqladvanced

Materialized View with Auto-Refresh

Create and maintain materialized views for expensive aggregate queries with concurrent refresh support.

#materialized-view#performance
sqladvanced

LATERAL Join for Top-N Per Group

Use LATERAL joins to efficiently fetch the top N related rows per group without window function subqueries.

#lateral-join#top-n

Related Topics

#pagination#performance#cursor#upsert#conflict#atomic