The Postgres Performance Trap Every Developer Falls Into

[00:00] So your Postgres database is getting slower. Your queries used to take 50 milliseconds, now they're taking 2, 3, 4, maybe even 5 seconds. And here's the thing, nothing changed in your code. You didn't write worse queries, you didn't mess up your schema,

[00:15] your table just got bigger. So you do what any good developer does, and you add an index. The performance improves, and then a few months go by, and it's slow again. So you partition the table. Again, performance improves, and a few more months, slow again.

[00:29] Then you bump up the instance size, throw more RAM at it, tune the auto vacuum, and again, this works just for a while until one day you step back and realize that you've been doing these patch fixes for over a year.

[00:41] That you're spending real engineering time, real infrastructure money, and all you're doing is keeping the same queries running at the same speed on a table that won't stop growing. If this sounds familiar to you, I've got good news and I've got bad news.

[00:55] Now the bad news is that no amount of Postgres tuning is going to fix this issue. But the good news is that there's a solution that actually works, it's open source, it runs on top of Postgres, and I'm going to show you exactly how to set it up for free

[01:08] in this video, courtesy of Tiger Data, who sponsored today's content. Okay, now before I show you the solution, I want to walk you through why this happens, because understanding the root cause is going to change how you think about this entire problem,

[01:20] so let's dive in. So let's say you've got a post-race database, and you've got a table, maybe it's called events or logs, user activity, sensor readings, whatever it is. Now it may have a timestamp column, and new rows are constantly being inserted into it.

[01:34] It could be hundreds per second, it could be thousands per second, it could be tens of thousands, and over time this table gets big. You know, we're talking about millions, maybe even tens or hundreds of millions of rows.

[01:46] Now the first thing that happens is that your query is going to start slowing down. You're doing something like giving all of the events from the last hour grouped by a device. Now that's a totally reasonable query, but it's scanning way more data than it needs to.

[01:59] So again, you do the textbook thing. Step one, you create an index, like what I'm going to put on screen. Now this index helps. Postgres can now find the relevant rows way faster using the B3 index instead of doing a full table scan.

[02:12] So your query may go from three seconds back down to 100 milliseconds simply because of the index. Well, you might think that solves the problem, but not quite. Because as your table is still growing, the index is growing with it.

[02:24] So every single insert now also has to update that index. So your write performance is going to start to degrade, and the index itself, once it gets big enough, is going to start to lose its effectiveness because Postgres still has to traverse a deeper and deeper tree.

[02:38] So a few months later, things are slow again. And now you move to step two, partitioning. So have a look at the query on screen. This is an example of something you might do to partition the table. So now with this, Postgres is only going to scan the relevant partitions when you query by time range.

[02:52] And that's a massive improvement, but you're still in the business of manually managing partitions. You have to create them ahead of time, drop the old ones, deal with edge cases where the query planner doesn't prune correctly, and as your partition count starts to grow into the hundreds, the planner itself starts to slow down

[03:08] because you have to evaluate every single partition before it can even begin executing the query. So now you back to being slow and you move to step three which is basically to throw money and configuration at the problem So you crank up the auto settings you increase the maintenance work memories you bump the shared buffers maybe you upgrade from an 8 to a 16 CPU

[03:28] maybe you go up to 32-cores, maybe you add more RAM, you get the idea. Here's an example of some of the configuration variables that you might change. And yes, this is going to buy you a few more months, but you're now spending significantly more on infrastructure,

[03:40] you're spending engineering time tuning all these configuration parameters, and you're doing this instead of building features. And the worst part is that you know what's going to happen again. The table is still growing, the workload hasn't changed,

[03:52] and you're just running faster on a treadmill now, spending more money. Now, here's the important part. None of these steps are wrong, right? Like, adding an index is correct, partitioning the table is correct, tuning these configurations is correct.

[04:04] These are all legitimate Postgres best practices, but the problem isn't what you're doing. The problem is that each one of these fixes is just treating a symptom, not the root cause of the problem. And the root cause is that Postgres' underlying architecture,

[04:18] so the way that it stores data and the way that it manages transactions, was not designed for workloads like this. Now, I promise I'm going to get into the solution, but first let me quickly describe a workload pattern, and if this relates to you, definitely keep watching.

[04:32] So number one, data arrives continuously. Not in a nightly batch job, not during a scheduled ETL window, it's constantly arising around the clock. You may have thousands or tens of thousands of inserts per second.

[04:45] Number two, almost every row has a timestamp, and almost every query filters on a given time range. So give me the last 30 minutes, right? Compare this week to last week. Show me everything between these two dates, et cetera.

[04:57] Number three, data is append-only, so once a row is inserted, it never gets updated, and you're writing things like sensor readings, log entries, transactions, user events, right? These are just facts that exist.

[05:10] Number four, you're retaining this data for months or years. It's not a temporary buffer. You need it for compliance, for analytics, for ML training, whatever it is. And number five, your queries need to be fast.

[05:22] This isn't cold storage waiting for a weekly report. You're serving dashboards, running alerts, doing real-time analysis. You know, sub-second to low-second response times is what you're looking for. Now, if even a few of these describe your system,

[05:35] then congratulations, because you don't have a Postgres optimization problem, you have a time series problem. And the reason none of your fixes stick permanently in Postgres is because you're asking a general-purpose database to do something it was never designed to do.

[05:50] So let me break down what's actually happening under the hood here when you ask Postgres to do this. So Postgres was designed for traditional application data. So things like users, orders, products, data that gets created, read, updated, and deleted.

[06:04] Now, to handle that safely, Postgres attaches a little bit of bookkeeping to every single row. Now, this is about 23 bytes of metadata that tracks things like which transaction created this row, is someone else currently reading this row, has this row been deleted yet,

[06:18] and it's basically Postgres' way of making sure that two people can read and write at the same time without stepping on top of each other. And for a user's table or an order's table, you know, that makes total sense. Rows get updated all the time, and you need that bookkeeping to make sure

[06:33] you're not writing and reading at the same time. But things like your events, your logs, you know this data is append only You write it once and you never touch it again So every single row is now carrying 23 bytes of metadata that exist to manage updates and delete that will never happen

[06:49] Now, 23 bytes sounds tiny, but when you're inserting tens of thousands of rows per second, that overhead adds up to gigabytes per day of storage that's just doing effectively nothing. And it gets worse because Postgres has this background process

[07:02] called auto-vacuum, and its job is to go through your tables and clean up old versions of rows that have been updated or deleted. It's like a janitor walking through your building looking for trash. Now on a normal table, that's great,

[07:14] there's actual cleanup that you need to do, but on your append-only events table, there is no trash. So nothing was ever updated, nothing was ever deleted, but AutoVacuum doesn't know that. So it's still walking through your entire table,

[07:26] which at this point is maybe hundreds of millions of rows, and looking for work that doesn't exist. And while it's doing that, it's competing with actual queries and using up CPU and IOP. And then the last piece is indexing. So Btree indexes, the default kind that are used in Postgres, work by building with essentially a sorted lookup structure. Again, something like a binary tree.

[07:47] Now these are amazing for traditional queries where you're looking up a user by an ID, or looking up an order number or something, but for this workload, almost every query is asking give me everything from the last hour, or show me data between these two days.

[08:01] And a B-tree doesn't have any concept of time. It doesn't know that yesterday's date is cold and the last five minutes are hot. It treats row number one and row number 500 million exactly the same. So as your table grows, the index grows linearly with it, and your time range queries have to do more and more work.

[08:19] So to bring it all together, posters are spending storage on bookkeeping that you don't need, running cleanup jobs on data that was never modified, and indexing everything uniformly when your access pattern is almost entirely giving you the recent stuff.

[08:32] Now this is the architectural mismatch, and that's why tuning the configs, adding more hardware, creating the index, is only giving you a few months of optimization, and then as soon as the table grows larger, everything falls apart.

[08:45] You're effectively optimizing around a design that fundamentally doesn't match your workload. So with that all said, what do you actually do about this? And this is the part that I'm kind of excited to get into, because the answer is not to just abandon Postgres and move to a completely different database.

[08:59] The answer is that you extend Postgres. Now there's actually an open source Postgres extension called TimescaleDB. And what it does is it changes how data is stored, partitioned, compressed, and queried under the hood.

[09:12] Now it keeps everything else the same, so you still use SQL, you still use your existing extensions, you still use your existing connection libraries, your ORM, your monitoring tools, whatever. Everything you know about Postgres stays the same, but what changes is the engine underneath.

[09:27] So I'm going to quickly walk you through the key features before I show you the demo, just to understand why you would want to use something like this. Again, it is called TimeScaleDB. So the first feature of this extension is hyper tables.

[09:40] Now when you convert a regular Postgres table into a hyper table, TimescaleDB automatically partitions your data by tying into what it calls chunks. So you don't have to manually create partitions, you don't have to manage them, you don't have to drop old ones, it just handles it for you.

[09:54] And the query planner knows how to skip irrelevant chunks, so your time range queries only ever touch the data that they need Then the second feature is column compression TimescaleDB can compress your time series data by 90 or more and it not just worth saving storage the compressed data is actually faster to query for analytical workloads because it

[10:13] reads less data from the disk. The third feature is continuous aggregates. These are like materialized views, but just way better. Materialized view in Postgres rebuilds from scratch every time you refresh it, whereas a continuous aggregate updates

[10:27] incrementally. So it only processes the new data since the last refresh, so if you need hourly or daily roll-ups of your data, they can stay current without the cost of reprocessing your entire table.

[10:39] And then fourth, retention policies and data tiering. So you can set automatic policies to drop old chunks after a certain period, or tier them to cheap object storage like F3, while keeping them queryable. Now those are the four core benefits, and while it might sound a

[10:53] little bit complicated, I promise you it's very easy to set up, and I'm going to show you a demo in just one second. Now, the company that's behind TimescaleDB is called Tiger Data. Now, you might have known them previously as Timescale, but they just recently re-granted to Tiger Data.

[11:07] Now, they're the ones who built an open source TimescaleDB, and they also run Tiger Cloud, which is their managed Postgres platform. And that's what I'm going to use in this demo because they have a 30-day free trial that doesn't require a credit card, and you can spin up a database in

[11:21] about 60 seconds. I'm going to leave a link to the description so you can follow along, but what I'm going to do is hop over to the computer now and show you a demo of how to use this feature. Okay, so I'm on the computer now and I'm going to run you through a live demo so you can see what the

[11:33] real speed difference is in a real use case. Now keep in mind this is a free open source extension that you can enable anywhere. So you can see the repo right here. Again, you know, this quick start guide walks you through exactly how to do it. In this video, I am going to use Tiger Data just

[11:48] because it's much easier to set up. And I have a 30-day free trial which you can access from the link in the description. You don't need a credit card, and if you wanted to play with it yourself, this is the fastest, easiest way to get it up and running. Not to mention, their databases obviously

[12:01] are great and designed for this kind of workload, so it's kind of a no-brainer to use that if you have this type of workload. Anyways, what we're going to do here is just create a new account. I already have one, so I'm just going to sign in, and once you do that, it should prompt you to

[12:14] create a free service. So I've signed into my account here, and you can see I'm just in this new project, which is free, and for services, what I can do is just press create service here with the default option, which will give me a post-risk with timescale DB and vector

[12:27] extensions database. So let's go ahead and create this, and while it does that, it'll just take one second, and I can explain what we're going to do next. So the next thing that we're going to do is we're going to configure the NCP server and

[12:39] directly work with this database inside of an AI tool. In my case, I'm going to use cursor. Now you can use anything that you want, but I just find it a lot easier to execute the queries when you can just tell an AI agent to do so, and we'll set up a

[12:52] live example of a bunch of events. We'll insert like a few million events into a database and compare the speed and planning of the various queries when we're using vanilla Postgres versus when we are using the hyper tables and when we're

[13:05] using the timescale DB extension. So what we can do actually is we can just go skip this step for now and we can go directly to where it says integrate with AI tools and start running through the setup steps. So what I'm going to do is open up

[13:17] cursor. You can see I've got it open right here and what we'll need to do is just run a commands to install the Tiger CLI and then once that's