Postgres In-Depth

Postgres might be the most popular relational database system in the world, and for good reason. It's insanely powerful, flexible, and reliable. It's also open-source, actively maintained, and has a vibrant community. You can use it for small projects, but it scales well to handle massive amounts of data. It's a great choice for building scalable and maintainable systems. And with extensions for storing vectors for semantic search, JSON for unstructured data, and even full-text search, Postgres is a versatile tool that can handle a wide range of use cases. I've built massive distributed systems relying on Postgres for the heavylifting, and it has never let me down.

Offloading Execution Flow to the Database

This post is the second part of a series on exploring the benefits and drawbacks of engineering for the application and database layer. If you haven’t done so, check out the first post on reducing database roundtrips....Systems Architecture

Building Sortable Relations with PostgreSQL

Relational databases have been at the foundation of the internet for the past four decades. What was built for business workflows in the past is now used for building dynamic applications powered by user-generated data....Systems Architecture, PostgreSQL

Reducing Database Round-Trips with Boring Queries

Note: This post is the first part of a series on exploring the benefits and drawbacks of engineering for the application and database layer....Systems Architecture

Modeling Polymorphic Relations in Postgres

Imagine you’re about to build a site builder with multiple content blocks. Users can add paragraphs, callouts, links, and whatever else you can imagine. You wonder how you could model this in your database, as sites should be linked to all connected blocks, but you do not want to create one wide table for your content blocks, instead, you would like distinct tables for each block kind, making it easy to evolve content blocks separately....Systems Architecture, PostgreSQL

Understanding Deferred Foreign Key Constraints in PostgreSQL

You might have experienced a scenario where a traditional foreign key constraint would be violated, for example, when creating circular references or having multiple entities depend on a resource where all rows are dropped at once. Sometimes, rows referencing deleted or updated entities should stay around, sometimes, the deletion process should cascade and include all referencing rows, too....PostgreSQL

Offloading Program Flow into PostgreSQL with Temporary Tables

Querying databases often happens in multiple network roundtrips. Especially when you have multiple operations that depend on the outcome of earlier queries, you’re dealing with state....Systems Architecture, PostgreSQL

Paginating Large, Ordered Data Sets with Cursor-Based Pagination

In my guide on designing scalable APIs, I emphasized implementing strict limits to how many entries are sent back for endpoints that return multiple items. The idea was that as long as you’re not completely sure that the data to be returned is limited to a small number of items, always use pagination....Systems Architecture, GraphQL, PostgreSQL

Utilizing the data layer: Deadlock Relations with Deferred Constraints

When designing your application's data layer, you might want to enforce separation of concerns and split tables by the data they hold, for example, moving all optional user data to a profile table and keeping track of the most important details like their name, email address and phone number in the primary account table....PostgreSQL

Utilizing the data layer: Complex UPSERTs in Postgres

In addition to the classic INSERT and UPDATE operations, there's another powerful data-modification technique, called upserting. Combining INSERTs for non-existing records and UPDATEs for existing ones, upserts enable various use cases to ensure a document exists and is modified accordingly, if it was created earlier, all while maintaining a concurrency-safe and atomic behavior....PostgreSQL

Utilizing the data layer: Conditional INSERTs in Postgres

In most applications, we create and assert guarantees in our business layer: We check that an account meets certain conditions before running another related query in the database. This leaves us with two problems: If we're querying the same data source, we've made an unnecessary roundtrip which sacrifices time we could spend on more important tasks, and by the time we're about to run the following query the state might have already changed....PostgreSQL

When (not) to use pg_dump

When working with PostgreSQL databases, pg\_dump is a fantastic tool for exporting the current state of a database and restoring it, either using pg\_restore or any other database client. You can specify specific schemas and tables to export, include or exclude content, and configure a lot of other settings as well. And, theoretically, it should work on live systems, as it doesn't block any ongoing read/write operations....PostgreSQL