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 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.
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.
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.
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.
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.
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.