Database Tips

You can use these bite-sized database tips in your application in minutes. They are all easy to replicate with a simple example and a more detailed explanation if you want to understand them more.

Querying Data

The SELECT statement has countless unknown features which make loading data more effective.

ORDER BY with nullable columns

When ordering a nullable column the NULL values are sorted in a database-specific order. But the sorting can be changed to an application-specific ordering.

For each loops with LATERAL Joins

A join can normally only link two static result sets together, but with LATERAL joins an efficient for-each loop with dependent subqueries can be used.

Cycle Detection for Recursive Search in Hierarchical Trees

Recursive queries searching within a hierarchical tree need to be protected for not failing on accidental loops in the data's references.

Fill Gaps in Statistical Time Series Results

Statistical calculations on a timeframe may have gaps due to missing that can be filled easily directly in the query.

Transactional Locking to Prevent Race Conditions

Race conditions are a common problem when updating rows but databases provide an easy-to-use locking solution.

Multiple Aggregates in One Query

One query can scan the data once and calculate multiple aggregates instead of executing multiple queries to do the same.

First Row of Many Similar Ones

Many similar rows (e.g. customer orders) can be filtered to a very specific one (e.g. the highest price) specific for every group (customer).

Data Manipulation

Modify existing data or insert new records with techniques going beyond standard CRUD operation.


Update rows based on values of another table or the result of another query by combining an update with a join.

Delete Duplicate Rows

Duplicate rows can be easily found and deleted with a single query including a selection of which ones to keep.

RETURNING Modified Rows

Workflows fetching some rows, processing and updating them can be optimized by merging those multiple SQL statements into one.

Table Maintenance after Bulk Modifications

Some maintenance operations are required after large bulk modifications to a table to ensure the best index will still be used.

Fill Tables With Large Amounts Of Test Data

For performance tests your tables need to be big. With a single query you can fill any table with a ton of random data.

Prevent Locking Issues For Updates On Counters

Updates to table rows are always executed one after another. To get better performance you can distribute those counters for higher concurrency.


Resolve bottlenecks and improve the performance of your application with some indexing magic not shared in tutorials.

Function-Based Indexes

Create specialized indexes for expressions like lower(email), because an index on email will not be used when the column in wrapped in a function call.

Descending Indexes

Changing the sorting direction for multi-column mixed-ordering indexes increases a query's performance by skipping the sorting operation.

Unique Indexes With Some Rows Excluded

Use partial indexes to ignore duplicated values of e.g. soft-deleted rows to create a unique index without duplicate value errors.

Ghost Conditions For Unindexed Columns

Artificial indexes added to queries can help the database to choose better indexes when a perfect one does not exist.

Make Indexes Invisible Before Deleting Them

Deleting an index is a high-risk operation. By changing its visibility first, you can limit the performance impact.


Simplify your database model with uncommon but very effective schema modeling approaches not taught in database classes.

JSON columns

The power of schema-full and schema-free data can be combined with JSON columns by copying ideas of NoSQL databases.

JSON schema validation

Schema-free JSON documents can be checked to comply with a defined to get a schema-full NoSQL JSON column.

Sorted Tables for Faster Range-Scans

Storing rows pre-sorted in tables instead of by insertion order will boost the performance for large databases significantly.

UUIDs to prevent Enumeration Attacks

Most applications are vulnerable to scrapers because of incrementing IDs. Retrofitting a UUID columns will solve the issue.

Store Trees As Materialized Paths

Storing trees in a database is complicated. But just storing the path instead of relationships simplifies this problem a lot.

Time Ranges without Overlapping

Guaranteeing non-overlapping time ranges (for e.g. a hotel room reservation) is not easy because of race conditions.

Efficiently Delete Old Rows with Partitions

Deleting many old rows is slow and doesn't free disk space. When you use time partitioning you can drop old time ranges instantly and reclaim the disk space.

Are you interested to learn more?

Be notified on future content. Never spam.