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.


Data Manipulation

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

UPDATE from SELECT

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.


Indexes

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.


Schema

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.


Are you interested to learn more?

Be notified on future content. Never spam.