I replaced my entire tech stack with Postgres

EnglishالعربيةDeutschEspañolFrançaisहिन्दीBahasa Indonesia日本語한국어PortuguêsРусский中文
BBetter Stack
Computing/SoftwareInternet Technology

Transcript

00:00:00Building modern web apps can get pretty expensive.
00:00:03Just getting a simple service up
00:00:04means relying on 20 different tools or services,
00:00:07and unfortunately, all my money gets spent
00:00:09on five Claude Mac subscriptions,
00:00:11so I need the poor man's web stack to help me out.
00:00:13Luckily, a 30-year-old technology
00:00:15somehow universally loved by all developers
00:00:18can replace every single one of these expensive tools.
00:00:21Postgres has decades of development
00:00:23and a rich plugin ecosystem,
00:00:25making it one of the most versatile tools
00:00:27in software engineering.
00:00:28So today, not because we should,
00:00:30but because we can, damn it,
00:00:31we're gonna replace our entire tech stack with just Postgres.
00:00:34We'll look at six areas.
00:00:36Caches, vector databases, full-text search,
00:00:39geospatial, cron and scheduling, and no SQL.
00:00:48One of the first places we turn
00:00:49when our database is being hammered
00:00:51is a distributed cache like Redis or Memcached,
00:00:54but that's a separate dependency
00:00:55and our app with five users probably doesn't need it.
00:00:58For a simple distributed cache,
00:00:59we can use Postgres's native feature,
00:01:02Unlogged Tables.
00:01:03These are just regular Postgres tables
00:01:05marked with the Unlogged keyword
00:01:07and they provide drastically faster writes
00:01:09and they're automatically emptied
00:01:11if the server crashes.
00:01:13Read speeds are about the same,
00:01:14but Postgres reads are incredibly fast anyway.
00:01:17When you write data to a normal Postgres table,
00:01:19the change is written to the write-ahead log,
00:01:21a safety file that records all database changes
00:01:24before they're written to the main storage.
00:01:26If the server loses power or crashes,
00:01:28Postgres replays the WAL to restore lost work
00:01:31and keep data safe.
00:01:32For Unlogged Tables, this step is skipped.
00:01:35The trade is that Unlogged Tables are wiped
00:01:37whenever the server crashes,
00:01:38but for a cache, that's the behavior we want anyway.
00:01:41Combine this with a simple wrapper
00:01:42and we have a fast, easy-to-use cache
00:01:45with no extra infrastructure.
00:01:47That's Redis and Memcached out of the stack.
00:01:49Next up is Vector Search.
00:01:51Since every man and his dog are building AR wrappers,
00:01:53Vector databases have become extremely popular.
00:01:56But again, it's adding more dependencies to our stack
00:01:58like Pinecone or Quadrant,
00:02:00but we can already handle this perfectly inside Postgres
00:02:03with the PG Vector extension.
00:02:05With this installed,
00:02:06we can store both our embeddings
00:02:07and our data for full context-aware search.
00:02:11Let's say we want to learn everything
00:02:12about the Dutch Eurodance music group Vengaboys.
00:02:14We start by creating a table with a Vector column,
00:02:17then a chunk of their Wikipedia article,
00:02:19convert each chunk into an embedding,
00:02:21store them both into Postgres with a simple insert.
00:02:24Now I can ask a question like,
00:02:26when did Vengaboys form?
00:02:27Convert the question to an embedding,
00:02:29then run a vector search.
00:02:31Next up is Full Text Search.
00:02:32Postgres has powerful full text search already built in,
00:02:35so relying on external tools like Elastic Search
00:02:38can actually be overkill.
00:02:39Postgres provides a column type called TS Vector.
00:02:42So say we want to store some posts.
00:02:45Let's create a simple table for them.
00:02:47Then to make them searchable,
00:02:48we can add a TS Vector column,
00:02:49which is generated from the body column.
00:02:52We've called this TSV.
00:02:54To keep this fast,
00:02:55we also create a GIN index.
00:02:57Now we can just start to store data in our table.
00:03:00What this does is split each sentence
00:03:01out into searchable chunks.
00:03:03So with the example,
00:03:04the quick brown foxes were jumping over the lazy dogs.
00:03:08This would become brown dog fox jump,
00:03:10lazy and quick with numbers assigned
00:03:12based on their positions.
00:03:14The were and over are gone completely.
00:03:16They're stop words and they carry no meaning.
00:03:18Jumping has been cut down to jump
00:03:20and foxes down to fox.
00:03:22That's called stemming.
00:03:23Every word reduced to its root.
00:03:25So the search for jump still matches jumping and jumped.
00:03:29So back to our posts example.
00:03:31Using web search to TS Query against the TSV column,
00:03:34we can now search for our posts.
00:03:36And of course, we get the results we expect.
00:03:38And because of the index we applied,
00:03:40this happens really quickly.
00:03:42Next up is geospatial.
00:03:43Postgres has powerful extensions
00:03:45for geographic and spatial data
00:03:47through an extension called PostGIS.
00:03:49With this, you can plot points on a map,
00:03:52capture large areas like flood zones
00:03:54and search data within a given radius.
00:03:56PostGIS gives you a new column type
00:03:58called geography to store your locations in.
00:04:004326 is just the coordinate system
00:04:02that most GPS systems use, such as your phone.
00:04:06So plain latitude and longitude.
00:04:09Then we add a GIST index.
00:04:11It's a spatial version of indexes
00:04:13we already use every day.
00:04:14Now we can just store some places.
00:04:16Longitude actually goes first here,
00:04:18which catches everyone out.
00:04:19I actually spent 30 minutes debugging this
00:04:21the first time I did it.
00:04:22And now I can query everything
00:04:23within 500 meters of the given lat long.
00:04:26And because the column is a geography,
00:04:28that 500 is 500 real meters
00:04:31across the surface of the earth.
00:04:33There's no trigonometry or heavy math we need to do.
00:04:35PostGIS just handles it like a bus.
00:04:37And if you want them in order of how close they are,
00:04:39PostGIS gives you a distance operator as well.
00:04:42But beyond the single points,
00:04:43you can also store shapes that we call polygons.
00:04:46And then you can ask which shape a point falls inside.
00:04:49So delivery areas, flood zones, or a congestion charge boundary
00:04:52are all just rows in a table that you can query.
00:04:55There's so much more in this single extension
00:04:57than I could fit in just one video,
00:04:59such as projections, buffers, rasters, data geocoding.
00:05:02This extension is absolutely massive.
00:05:05So next up is cron and scheduling.
00:05:07Nearly every app ends up reading something
00:05:08to run on a schedule,
00:05:10whether that's a nightly cleanup
00:05:11or a report that goes out at 6 a.m.
00:05:13Normally that means configuring cron jobs
00:05:15through an external service,
00:05:17but it's already possible straight from Postgres
00:05:19with PG cron.
00:05:21Here, I'm deleting log rows older than 30 days
00:05:24every night at 3 a.m.
00:05:25You give it a name, a normal cron expression,
00:05:28and the SQL you want to run.
00:05:29Every job lives in a table
00:05:31so you can see exactly what's scheduled.
00:05:33Every run gets recorded too,
00:05:34so that job that dies at 3 a.m.
00:05:36leaves a trace behind it.
00:05:38Deleting a job is simple too,
00:05:39just call the unscheduled function.
00:05:41And now you have a simple cron
00:05:42with direct access to your database,
00:05:44no wiring together multiple services.
00:05:46The last one is document storage.
00:05:48You could reach for no SQL options like MongoDB,
00:05:51but Postgres has actually had a way
00:05:53to store unstructured JSON for years
00:05:55called JSONB.
00:05:57First, we create a table with a JSONB column,
00:06:00then add an index to keep things fast.
00:06:02Then we can store whatever unstructured JSON we like.
00:06:04Then we can use things like the containment operator
00:06:07to check if a document on the left
00:06:09contains the one on the right.
00:06:10So this means we can search with JSON itself.
00:06:13To search on keys,
00:06:14the question mark operator tells you whether one exists.
00:06:17And if you want to go even further,
00:06:18there's DocumentDB,
00:06:20which is a MongoDB compatible document database
00:06:22built on top of Postgres,
00:06:25which takes the idea even further.
00:06:27You can also handle durable crash proof workflows
00:06:29inside Postgres,
00:06:30which I filmed a dedicated video on
00:06:32that you can watch here.
00:06:33So check that out if you want a deeper dive.
00:06:36But look,
00:06:36if you're building a service
00:06:37that doesn't need to target
00:06:38hundreds of thousands of users,
00:06:40many of these extensions are genuinely powerful
00:06:42and can be viable options
00:06:44compared to a lot of third-party tools.
00:06:46But hopefully you enjoyed that one, guys.
00:06:47Thank you so much for watching.
00:06:49And of course,
00:06:49I'll see you in the next one.

Key Takeaway

Postgres replaces external single-purpose services like Redis, Pinecone, Elasticsearch, PostGIS, cron tools, and MongoDB through its robust built-in features and extension ecosystem.

Highlights

  • Postgres unlogged tables skip the write-ahead log to deliver drastically faster write speeds for caching.

  • The PG Vector extension stores both data embeddings and original content for context-aware searches within Postgres.

  • Postgres includes built-in full-text search capabilities using TS Vector columns combined with GIN indexes.

  • PostGIS extensions enable accurate real-world meter distance calculations, polygon containment queries, and spatial indexing.

  • The PG cron extension runs scheduled SQL jobs and records execution history directly inside the database.

  • JSONB column types and DocumentDB compatibility layers allow Postgres to function as a NoSQL document store.

Timeline

Replacing Distributed Caches with Unlogged Tables

  • Unlogged tables skip the write-ahead log to provide faster write speeds.
  • Server crashes automatically empty unlogged tables while leaving normal data safe.

Standard Postgres tables write changes to a safety write-ahead log file before updating main storage to survive crashes. Unlogged tables bypass this step entirely, making them ideal drop-in replacements for distributed caches like Redis and Memcached without requiring extra infrastructure.

Handling Vector Search with PG Vector

  • PG Vector stores both vector embeddings and original text chunks in a single table.
  • Embeddings convert natural language questions into searchable vectors for context-aware retrieval.

Building AI features typically forces developers to add external dependencies like Pinecone or Quadrant. Installing the PG Vector extension allows databases to handle embedding storage and similarity searches natively for applications requiring context-aware information retrieval.

Executing Full-Text Search via TS Vector

  • TS Vector column types parse sentences into searchable chunks while discarding stop words.
  • Stemming reduces words to their root forms to match variations during queries.

External tools like Elasticsearch often duplicate infrastructure overhead for standard text search tasks. Postgres provides native text processing that stems words, removes meaningless modifiers, and uses GIN indexes to execute rapid queries against stored text columns.

Managing Geospatial Data with PostGIS

  • PostGIS geography columns calculate exact real-world meters across the earth's surface.
  • Spatial shapes like polygons define boundaries for delivery zones and flood areas.

Geographic extensions bring advanced mapping capabilities directly into standard queries. Systems can store latitude and longitude points, apply GIST spatial indexes, calculate distances without complex trigonometry, and check whether specific coordinates fall inside defined polygonal boundaries.

Scheduling Tasks with PG cron

  • PG cron executes routine database maintenance tasks using standard cron expressions.
  • Execution logs persist inside database tables to track scheduled job failures.

Applications frequently require background tasks like nightly cleanups or automated reports. Configuring jobs directly inside Postgres through cron extensions eliminates external scheduling services and records execution traces for error debugging.

Storing Unstructured Documents with JSONB

  • JSONB columns store unstructured document data natively inside relational tables.
  • Containment operators and DocumentDB compatibility layers enable MongoDB-style querying.

Relational constraints no longer prevent developers from storing unstructured payloads. Postgres handles JSON documents efficiently using specialized operators, containment checks, and specialized compatibility extensions to replace dedicated NoSQL databases for small to medium workloads.

Community Posts

No posts yet. Be the first to write about this video!

Write about this video