Speaker 1: Welcome to the deep dive. This is where we take
Speaker 1: a whole stack of complex information, research notes articles and
Speaker 1: really boil it down to the essentials for you. And
Speaker 1: today we're plunging into a topic that's just well critical
Speaker 1: for any serious developer, high performance Postgres School, specifically within
Speaker 1: Ruby on Rail's applications. Our mission really is to give
Speaker 1: you those key insights that will make your apps faster,
Speaker 1: more reliable, definitely more resilient. What's kind of cool is
Speaker 1: that our main source here is actually a beta book.
Speaker 1: It's still being developed, so you're getting some really cutting edge,
Speaker 1: very practical info straight from the trenches.
Speaker 2: You might say, yeah, and that's exactly why this is
Speaker 2: so relevant right now. Getting really good at postgreschool and rails.
Speaker 2: That's not just helpful for your career, it's massively in demand,
Speaker 2: right Just think about it. A Hired survey from twenty
Speaker 2: twenty three it found Ruby on rails was actually the
Speaker 2: most sought after skill. We're talking one point six y
Speaker 2: four times more interview requests if you know your stuff, wow.
Speaker 1: One point six four times Yeah.
Speaker 2: And Postgres School it's consistently winning wards. It was number
Speaker 2: one in the twenty twenty two stack Overflow survey for
Speaker 2: most used database among pros and it's topped the dB
Speaker 2: engine's ranking three times. So yeah, the time spent learning this,
Speaker 2: it's it's definitely high impact knowledge.
Speaker 1: Okay, all right, let's get into it. Then. Before we
Speaker 1: can even you know, talk about performance tuning, we need
Speaker 1: someone to actually do the tuning. We need a test bed.
Speaker 1: So this source material. It introduces a fictional app called
Speaker 1: ride share. What exactly is that? And I guess how
Speaker 1: does it help us learn this stuff?
Speaker 3: Right?
Speaker 2: So ride share is it's basically designed as this simplified
Speaker 2: API only web app. Think of it like a mini
Speaker 2: Uber or Lyft. You know, it's got the core active
Speaker 2: record models you'd expect drivers, writers, trips, trip requests.
Speaker 1: That's worth thing and active record just quickly, that's the RM, right,
Speaker 1: the object Relational mapper in reels exactly.
Speaker 2: It's the magic layer that connects your Ruby code, your
Speaker 2: classes directly to the database tables. And it really leans
Speaker 2: into that rails philosophy of convention over configuration.
Speaker 1: Ah right, like driver model maps to driver's table.
Speaker 2: Automnate precisely, you don't have to spell it all out
Speaker 2: and for managing schema changes rideshare uses the standard rail
Speaker 2: stuff dbstructured dot s cool along with active record migrations
Speaker 2: under the hood that uses pg dump to capture the structure.
Speaker 1: You know, one thing I found really valuable in the
Speaker 1: source was this strong push to set up and use
Speaker 1: postgrass School locally, like really, make it your own little lab.
Speaker 3: Totally. It's not just theory.
Speaker 2: Having it run locally gives you complete control a safe
Speaker 2: space to experiment, which, let's face it, is absolutely essential
Speaker 2: when you're messing with performance settings. You don't want to
Speaker 2: test this stuff live, definitely not, and getting rights you're
Speaker 2: running is designed to be pretty smooth. You use Homebrew
Speaker 2: urban v for your Ruby version, Butler for gems, and
Speaker 2: then just the standard binrails commands dB do I create, dB,
Speaker 2: DOT migrate, dB console simple stuff. Once you set up,
Speaker 2: you immediately start bumping into core postgred school ideas like
Speaker 2: SQL being a declarative language.
Speaker 1: Okay, what does that actually mean? Declaraty?
Speaker 2: It just means you tell postgres School what you want,
Speaker 2: like get me all the trips from yesterday, and you
Speaker 2: don't specify how to get it. Postgrescool's optimizer, its brain
Speaker 2: figures out the most efficient way to execute that request.
Speaker 1: Ah, so you declare the result. It handles the process
Speaker 1: like ordering food, exactly.
Speaker 2: Like ordering food, and the how it figures out that's
Speaker 2: the query execution plan. It's like the database's internal recipe
Speaker 2: for fetching your data. Plus you've got functions built in
Speaker 2: ones and ones you can define yourself, which really let
Speaker 2: you push complex logic down into the database itself. Seriously,
Speaker 2: we can't stress this enough. Get ride share set up locally.
Speaker 2: It really is the perfect lab for practicing everything we're
Speaker 2: about to cover.
Speaker 1: Okay, performance lab established. Ride Share is running locally, safe
Speaker 1: space acquired. But to really know what's going on, we
Speaker 1: need tools to look inside Postgres school, Right, how do
Speaker 1: we peak under the hood.
Speaker 2: Yeah, the piece grows command line tool is kind of
Speaker 2: your main entry point there. There are a few meta
Speaker 2: commands you'll use all the time, like a cantillybax that
Speaker 2: toggles This expanded view makes query results way easier to read.
Speaker 1: Oh yeah, that's super helpful.
Speaker 2: And e is great. It pomps your current query and
Speaker 2: your text editor life saver for complex SQL. Then there's
Speaker 2: EEL to just list your databases. You can also customize
Speaker 2: PSQL using a timicustl RC file add aliases, change the
Speaker 2: prompt whatever makes you comfortable, and for enabling some really
Speaker 2: powerful extensions like PG stat statements, which will definitely circle
Speaker 2: back to you need to edit your postcrisql dot com
Speaker 2: file specifically the shared preload library is setting.
Speaker 1: Ah, okay, and that canfig change needs a restart.
Speaker 3: Right that specific one does.
Speaker 2: Yeah, Shared preload libraries needs a full postcresco restart to
Speaker 2: take effect.
Speaker 1: Got it? So okay, we can configure things, but how
Speaker 1: do we see what the database is doing? Like right now?
Speaker 1: Is it just log files or is there a more
Speaker 1: direct way? I remember this one time a query just
Speaker 1: ran forever almost took down the whole app. If only
Speaker 1: I'd known about PG stat activity. Oh.
Speaker 2: Absolutely, PG set activity is exactly that. It's your real
Speaker 2: time dashboard. You see every connection, what state it's in active, idle,
Speaker 2: maybe crucially idle in transaction, and you see background processes too,
Speaker 2: like autovacuum, so.
Speaker 1: You can spot those long running queries.
Speaker 3: Yep.
Speaker 2: You can see the curry text, find its process idea
Speaker 2: the PID, and then if you really need to, you
Speaker 2: can try to cancel it gracefully with pg cancel back
Speaker 2: end or in an emergency, terminate it with p determinative
Speaker 2: back end. Use that last one carefully.
Speaker 1: Though right termination is a bit heavy handed.
Speaker 2: It can be Now this ties into understanding pessimistic locking.
Speaker 2: It's what postgrescool does by default. You'll see shared locks
Speaker 2: and exclusive locks. The main takeaway you really want to
Speaker 2: minimize how long you hold exclusive locks because they block
Speaker 2: everyone else trying to access that same data.
Speaker 1: And that can lead to deadlocks.
Speaker 3: Exactly.
Speaker 2: Deadlocks are the worst case scenario, two transactions waiting for
Speaker 2: each other, stuck forever. Postgres will will detect and break them,
Speaker 2: but it means one transaction fails. You can see livelock
Speaker 2: information using the peaklocks view.
Speaker 1: Okay, so lots to monitor. How about experimenting safely?
Speaker 2: These source suggest using generate series to create lots of
Speaker 2: fake data. You can do this in a separate experiment's database.
Speaker 2: It's a great way to simulate production level load without
Speaker 2: touching your real development data.
Speaker 1: That's smart.
Speaker 2: And here's something that often surprises people. Post grescool has
Speaker 2: transactional DDL.
Speaker 1: Transactional DDL data definition.
Speaker 2: Language like create table, exactly.
Speaker 1: Schema changes, create index, alter table, ad column. They happen
Speaker 1: inside a transaction just like data changes. So you can
Speaker 1: literally type begin, then create index mix on my table call,
Speaker 1: then realize you made a mistake and type roll back
Speaker 1: and that index just poof never happened.
Speaker 3: WHOA.
Speaker 2: Okay, that's huge for safety. No half applied schema changes.
Speaker 1: It's an incredible safety net. It means your migrations either
Speaker 1: succeed completely or fail completely, which brings us back to
Speaker 1: safe experimentation. Always test schema changes in staging first, and
Speaker 1: maybe even use read only database users in production for
Speaker 1: certain monitoring tasks. Using roles like p grade old data
Speaker 1: or pgmont just adds another layer of safety.
Speaker 2: Makes sense, Okay, let's switch gears a bit. Data correctness
Speaker 2: data consistency obviously super important in rails. We often reach
Speaker 2: for active record validations, but the force argues pretty strongly
Speaker 2: for using database level constraints too. What's the thinking there?
Speaker 2: Are they redundant not redundant complementary? That's the key. Active
Speaker 2: record validations are great, essential, even for catching errors early
Speaker 2: at the application layer, providing good user feedback. But database
Speaker 2: constraints offer stronger guarantees because they're enforced inside the database engine,
Speaker 2: which is built specifically to handle high concurrency transactions and
Speaker 2: maintain data isolation in ways an application layer just can't.
Speaker 1: Okay, stronger guarantees. What kind of constraints are we talking
Speaker 1: about beyond say, primary key or.
Speaker 3: Not in all, Oh, there's a whole suite.
Speaker 2: You've got unique constraints obviously, foreign key constraints to maintain
Speaker 2: relationships between tables, check constraints for custom rules, and even
Speaker 2: more advanced exclusion constraints.
Speaker 1: Let's take unique If I want to add one, but
Speaker 1: I already have duplicate data, what do I do?
Speaker 2: Good question? You typically need to clean up that data first,
Speaker 2: the source mentions using a common table expression a CTE
Speaker 2: with the row number window function. It's a neat trick
Speaker 2: to identify and then delete the duplicates before you apply
Speaker 2: the unique constraint.
Speaker 1: Okay, and for and keys. Rails didn't always support those natively,
Speaker 1: did it right?
Speaker 2: Native support landed in rails four point two Before that
Speaker 2: you use gems. But yeah, they're fundamental. They ensure, for example,
Speaker 2: that you can't delete a rider if they still have
Speaker 2: associated trips in the database. Prevents orphaned records.
Speaker 1: Got it? What about che check constraints you said, custom rules, Yeah,
Speaker 1: they're really flexible.
Speaker 2: Anything that evaluates the true or false, like you could
Speaker 2: enforce that a trips table's completed timestam must always be
Speaker 2: later than it's create debt timestap. Simple powerful rule.
Speaker 1: Okay, that makes sense, But this raises a practical point.
Speaker 1: How do you add a cheat check constraint like that
Speaker 1: to a table that's already huge and getting hammered with traffic.
Speaker 1: Wouldn't that lock it up while it checks millions of
Speaker 1: old rows?
Speaker 2: Exactly the problem, and there's an elegant solution. You do
Speaker 2: it in two spelps using rails migrations. First you add
Speaker 2: check constraint, but pass the option validated false. This tells postgraschool, okay,
Speaker 2: enforce this rule for all new or updated rows from
Speaker 2: now on, but don't check the old ones yet. That
Speaker 2: part is super fast.
Speaker 1: Ah, so it doesn't block.
Speaker 3: Right.
Speaker 2: Then, in a separate later migration you run validated at
Speaker 2: check constraint for that same constraint. This tells postgres school, okay,
Speaker 2: now go back and check all the existing rows. But
Speaker 2: it does so without taking such a heavy lock. It
Speaker 2: avoids that downtime.
Speaker 1: Clever two steps. What about deferring constraints?
Speaker 2: Yeah, deferable Initially deferred. You can apply this to unique
Speaker 2: primary key, foreign key, and exclusion constraints. It means the
Speaker 2: constraint check is postponed until the very end of the transaction.
Speaker 2: Super useful for things like say, reordering items in a
Speaker 2: list where each item needs a unique position. You might
Speaker 2: temporarily have duplicate positions during the transaction while you swap
Speaker 2: things around, but as long as it's fixed by the
Speaker 2: time you commit, it's okay.
Speaker 1: Interesting, okay. You also mentioned exclusion constraints. Those sound advanced.
Speaker 2: They are powerful and less common, but solve specific problems
Speaker 2: really well. They prevent overlapping data across multiple roads in
Speaker 2: the same table. The classic example is preventing overlapping time
Speaker 2: ranges like booking a meeting room, or, in ride SHARE's case,
Speaker 2: maybe preventing overlapping vehicle reservations. They usually require an extension
Speaker 2: like beat read just and often use range types like
Speaker 2: TSTs range for timestamp ranges along with the overlap operator
Speaker 2: at datcha okay, quick detour case in sensitive unique emails
Speaker 2: common problem. How does postgress will handle that?
Speaker 3: Two main ways?
Speaker 2: Really You could use the site text extension, which provides
Speaker 2: a case in sensitive text type, or you can use generated.
Speaker 1: Columns generated columns like virtual columns sorted.
Speaker 2: Yeah, you define a column that's automatically computed based on others,
Speaker 2: so you could have a lower mail generated column that
Speaker 2: always stores lower email. Then you put a regular unique
Speaker 2: index on that generated column. Rails actually supports these now too.
Speaker 1: Neat and quickly. And domains right, create.
Speaker 2: Type of gas as enom lets you define a fixed
Speaker 2: list of a loudstring values for a column like trip statuses, pending, active, completed.
Speaker 2: Create domain lets you create a custom data type based
Speaker 2: on an existing one, but add check constraints to it,
Speaker 2: making reusable validation rules both useful, different trade offs.
Speaker 1: Okay, this is great for ensuring data integrity, but let's
Speaker 1: talk about actually changing the database schema on a busy
Speaker 1: production system. That moment when you run RAILSDB dot migrate,
Speaker 1: it can be terrifying.
Speaker 2: Oh yeah, the dreaded migration lock exactly.
Speaker 1: Some alter table operations, they take what's called an access
Speaker 1: exclusive lock, right, and that just blocks everything reads rights.
Speaker 1: Your app grinds to a halt. How do we avoid
Speaker 1: that absolute nightmare?
Speaker 3: Right?
Speaker 2: That exclusive lock is the enemy on a busy system.
Speaker 2: The absolute key here, Your best friend really is the
Speaker 2: concurrently keyword for operations like create index or drop index.
Speaker 2: Adding concurrently tells postgress will to do the work without
Speaker 2: taking that heavy lock. It takes longer, more resources, but
Speaker 2: your application stays online.
Speaker 3: It's a life saver.
Speaker 1: So create index concurrently, drop index concurrently. Are there other
Speaker 1: ways to stay safe?
Speaker 3: Definitely.
Speaker 2: There's a fantastic Ruby gym called strong Migrations. You add
Speaker 2: it to your development environment and it actively watches your migrations.
Speaker 2: If it spots a potentially dangerous operation, one that would
Speaker 2: take an access exclusive lock and likely cause downtime, it'll
Speaker 2: either warn you, suggest a safer multi step alternative like
Speaker 2: the chi check constrained example, or even prevent the migration
Speaker 2: from running in production by default.
Speaker 1: Oh wow, So it enforces safer practices during development exactly.
Speaker 2: It catches things early beyond that. You need safeguards at
Speaker 2: the database level too, especially with high concurrency. Setting a
Speaker 2: lock time out is crucial.
Speaker 1: Lock time out that limits how long a query waits
Speaker 1: for a lock.
Speaker 2: Precisely, if a query can't get the lock it needs
Speaker 2: within say, fifty milliseconds, it gets canceled instead of just
Speaker 2: sitting there waiting indefinitely and potentially holding up other processes. Similarly,
Speaker 2: stay time out puts a cap on how long any
Speaker 2: single sequel statement is allowed to run, prevents runaway queries
Speaker 2: from hogging resources. The source also mentions enabling log lock
Speaker 2: weights and tuning deadlock timeout for better visibility in your
Speaker 2: logs when contention happens. Okay, timeouts are key. What about
Speaker 2: removing columns? I've heard that can cause weird errors too. Ah, yes,
Speaker 2: so the stale schema cache problem. This happens when you
Speaker 2: drop a column, but some of your running Rails application
Speaker 2: servers haven't picked up the schema change yet, they try
Speaker 2: to query the column that no longer exists.
Speaker 3: Boom error.
Speaker 1: Right, So how do you remove a column safely?
Speaker 2: The recommended way is using active record dot base dot
Speaker 2: ignored columns. It's a multi step process. First, you add
Speaker 2: the column name to ignored columns in your Rails model,
Speaker 2: deploy that code. Now rail simply pretends the column doesn't exist,
Speaker 2: even though it's still in the database. Then, once your
Speaker 2: sure no code is using it, you create and run
Speaker 2: a migration to actually remove column. Finally, you remove the
Speaker 2: column name from ignored columns in a later deploy. It's
Speaker 2: gradual and safe.
Speaker 1: Makes sense gradual removal. Now this brings up another big one.
Speaker 1: What if you add a new column and need to
Speaker 1: populate it for millions of existing rows backfilling data without downtime?
Speaker 2: Yeah, that's a classic challenge. Running a massive up date
Speaker 2: statement is usually out of the question. Too slow, too
Speaker 2: much locking. You need online backfilling strategies. One approach is
Speaker 2: double writing or dual rights. You modify your application code
Speaker 2: to write to both the old location if any, and
Speaker 2: the new column simultaneously. You run a background job to
Speaker 2: backfill a new column for old records, and once it's done,
Speaker 2: you switch weeds to the new column and eventually remove
Speaker 2: the dual right logic and the old column.
Speaker 1: Okay, double writing any other ways.
Speaker 2: Another technique involves using intermediate tables. You create a temporary table,
Speaker 2: maybe just with the primary key in the new column value.
Speaker 2: You populate that table, perhaps marking it U and lodgy
Speaker 2: so it doesn't hit replication, maybe disabling autovacuum on it
Speaker 2: temporarily for speed. Then you batch update the main table
Speaker 2: from this intermediate table. And crucially, all these backfilling processes
Speaker 2: need to be done in small, manageable batches, often with
Speaker 2: some kind of throttling or delay between batches to avoid
Speaker 2: overwhelming the database or causing replication.
Speaker 1: Lack batching and throttling. Got it, Okay, let's shift focus
Speaker 1: to active record itself. It's amazing. Rails convention over configuration
Speaker 1: is great, but yeah, it can sometimes generate pretty inefficient
Speaker 1: queries if you're not paying attention right.
Speaker 2: Absolutely, the abstraction is powerful, but it can hide what's
Speaker 2: actually happening. So connecting this to the bigger picture, it's
Speaker 2: about making your rails app smarter in how it communicates
Speaker 2: with postgress.
Speaker 1: How do we even spot the bad queries easily?
Speaker 3: Well?
Speaker 2: One really helpful thing in Roll seven and later is
Speaker 2: the improved query logs. They can automatically add context like
Speaker 2: which controller an action trigger the query right into the
Speaker 2: sql log output. Makes tracing a slow query back to
Speaker 2: your application code much much easier.
Speaker 1: That sounds useful. What's a common inefficiency pattern?
Speaker 3: Oh?
Speaker 2: The absolute classic is the M plus one query problem?
Speaker 2: You see it everywhere?
Speaker 1: Ah, Yes, fetch one thing, then loop and fetch relate
Speaker 1: things one.
Speaker 2: By one exactly like load one hundred blog posts and
Speaker 2: then inside the loop for each post, run another query
Speaker 2: to get its author. That's one hundred and one database
Speaker 2: queries when it could probably be just two kills performance.
Speaker 1: Right, So how do we fix N plus one?
Speaker 2: The primary solution is eager loading. You tell active Record
Speaker 2: upfront what associated data you'll need, use methods like dot
Speaker 2: preload or dot cludes rails, then cleverly figures out how
Speaker 2: to load all that data in a minimal number of queries,
Speaker 2: usually just one extra query for each association.
Speaker 1: Yeah preload and dot includes.
Speaker 2: Got it and a quick tip if you've already eagerloaded
Speaker 2: data into an array of objects, use dot size to
Speaker 2: get the count dot dot count. Dot size works unloaded
Speaker 2: array in memory, dot count might trigger another database query
Speaker 2: unnecessarily good tip.
Speaker 1: Any other ways to prevent N plus one?
Speaker 2: Yeah, Rail six point one introduce strict loading. You can
Speaker 2: enable it per association or globally. If you try to
Speaker 2: access an association that wasn't explicitly eger loaded, it raises
Speaker 2: an error instead of silently running the en plus one query.
Speaker 2: It forces you to be explicit and prevents the problem
Speaker 2: by default.
Speaker 1: Ooh, I like that force the good behavior. What about
Speaker 1: optimizing individual queries?
Speaker 2: Simple things First, always use limit If you don't need
Speaker 2: all possible results, don't pull back ten thousand rows if
Speaker 2: you only display twenty. Also, the returning clause on insert
Speaker 2: active records dot insertle method supports this. Now it lets
Speaker 2: you get back the IDs or other columns of the
Speaker 2: rose you just inserted without needing a separate select query afterwards.
Speaker 2: Saves a round trip.
Speaker 1: Nice and processing large amounts of data use.
Speaker 2: Active records batching methods dot finbach or dot in batches.
Speaker 2: They retrieve records in batches default one thousand, which keeps
Speaker 2: memory usage low and avoids overwhelming the database or your
Speaker 2: application with enormous result sets. Much more reliable for large tables.
Speaker 1: Okay, batching is key. What about more complex SQL logic?
Speaker 1: Does Active record help there?
Speaker 2: It does increasingly so. Active record has good support for subqueries.
Speaker 2: Now you can construct queries where part of the ware clause,
Speaker 2: for instance, is itself another SQL query, useful for things
Speaker 2: like finding drivers who have completed more trips than the
Speaker 2: overall average number of trips per driver.
Speaker 1: Okay, subqueries. I've also heard about CT's common table expressions.
Speaker 2: Yes, CTEs using the with keyword in SQL are fantastic
Speaker 2: for complex queries. They'll let you break down a big,
Speaker 2: hairy query into smaller named logical steps. It makes the
Speaker 2: sequel vastly more readable and maintainable. Active record has ways
Speaker 2: to build queries using CTEs.
Speaker 1: Too, so better organization. What about views?
Speaker 2: Database views are another way to encapsulate complex sequel. You
Speaker 2: define the query logic as a view directly imposed cresscol
Speaker 2: and then you can query it from rails, almost like
Speaker 2: a regular table. The scenic gem is very popular for
Speaker 2: managing database views within your rails migrations.
Speaker 1: Okay, views and materialized views. What's the difference?
Speaker 2: Ah, Now, this is where it gets really interesting. Materialized
Speaker 2: views take it a step further. They don't just store
Speaker 2: the query definition. They actually execute the query and store
Speaker 2: the results physically like a cash table.
Speaker 1: So queries against them are super fast.
Speaker 2: Lightened fast because the complex calculation or joint is already done.
Speaker 2: They're perfect for complex reports or dashboard data that doesn't
Speaker 2: need to be absolutely real time.
Speaker 3: And the cool part you.
Speaker 2: Can often refresh them can currently update the stored data
Speaker 2: without locking readers. Provided the materialized view as a unique
Speaker 2: index defined on it, zero downtime updates for your cash data.
Speaker 1: Wow, concurrent refresh that's powerful. What about caching within rails itself?
Speaker 3: Right.
Speaker 2: Rails has layers two. The query cash is automatic within
Speaker 2: a single controller action. If you run the exact same
Speaker 2: sequel query twice in one request, RAILS cash is the
Speaker 2: result of the first call and returns it instantly the
Speaker 2: second time, avoiding a redundant database.
Speaker 1: Hit okay automatic per request.
Speaker 2: Then there are prepared statements postcresscool can parse a query
Speaker 2: plan once and reuse it for subsequent executions with different parameters.
Speaker 2: RAILS uses these under the hood. Rail seven improved how
Speaker 2: it handles them, making reuse more likely by numerrating columns,
Speaker 2: which helps reduce parsing overhead.
Speaker 1: And counter cases.
Speaker 2: Counter cases are a common pattern, not strictly a RAILS feature,
Speaker 2: but supported by it. You add an integer calumn to
Speaker 2: a model, say trips count on the user model. Then
Speaker 2: you can figure the trip model to automatically increment or
Speaker 2: decrement that counter whenever a trip is created or destroyed
Speaker 2: for that.
Speaker 1: User, so you get counts almost instantly without a slow query.
Speaker 2: Exactly reading the count is just reading it into your column.
Speaker 2: The tradeoff is slightly increased right latency, you have to
Speaker 2: update the counter, and potential for slight inconsistencies if things
Speaker 2: go wrong but they're often a huge performance win for
Speaker 2: frequently needed counts.
Speaker 1: Makes sense. What about calculations like averages or sums.
Speaker 2: Do them in the database. Don't pull a million numbers
Speaker 2: back to Ruby just to calculate the average. Use CQL
Speaker 2: aggregate functions like avg sum countet max min. Active record
Speaker 2: provides methods like dot average sum, et cetera that generate
Speaker 2: the correct sequel. It drastically reduces data transfer and Ruby
Speaker 2: object allocation.
Speaker 1: Right less object delocation is that a big deal?
Speaker 2: It can be, especially in tight loops or high throughput scenarios.
Speaker 2: Every active record object RAILS creates consumes memory and CPU cycles.
Speaker 3: If you only need raw.
Speaker 2: Data maybe for a report or an export, Using lower
Speaker 2: level methods like dot find by Soukel or even active
Speaker 2: record dot base, dot connection, dot execute can be significantly faster.
Speaker 2: There were dren simpler data structures, hashes or rays instead
Speaker 2: of full blown active record objects, dramatically reducing that allocation overhead.
Speaker 1: Okay, lots of ways to optimize within RAILS, but let's
Speaker 1: go deeper into the database itself. When a query hits postgriss, well,
Speaker 1: how do we see what it's really doing? And how
Speaker 1: do we choose the right index to speed it up.
Speaker 2: Right now we're getting into database level observability. A really
Speaker 2: crucial tool here is the pgstat statements extension we mentioned earlier.
Speaker 2: You enable it in postcresq al dot COF.
Speaker 1: What is it track?
Speaker 2: It tracks execution statistics for every normalized query run against
Speaker 2: your database, total time spent, average time, how many times
Speaker 2: it was called, rose returned, et cetera. It's invaluable for
Speaker 2: finding your most expensive queries globally, the ones consuming the
Speaker 2: most database time overall, so you can.
Speaker 1: See the biggest hitters across the whole application exactly.
Speaker 2: It helps you prioritize optimization efforts for a more visual approach.
Speaker 2: Pg hero is a great dashboard tool. Many teams use it.
Speaker 2: It gives you a web UI to see slow queries.
Speaker 2: It can analyze query plans using explain, show you large
Speaker 2: tables indexes, find unused indexes, and even show currently running queries.
Speaker 1: Pg Hero Okay, but the core tool is explain right.
Speaker 3: Explain is fundamental running.
Speaker 2: Explain before a query shows you post Grescol's plan for
Speaker 2: executing it. Adding Analyze actually runs the query and shows
Speaker 2: you the plan along with the actual time spent and
Speaker 2: rose returned at.
Speaker 1: Each step, so explain analyze gives you the real picture.
Speaker 2: Yes, you can also add buffers to see memory usage
Speaker 2: like cash hits and format YAMEL or JSON for easier parsing.
Speaker 2: It shows you things like whether it's doing a sequential
Speaker 2: scan reading the whole table usually bad, or an index
Speaker 2: scan or ideally an index only scan. It shows estimated costs,
Speaker 2: filter conditions. It's how you diagnose a slow query.
Speaker 1: Which leads to the question how do you make sure
Speaker 1: Postgress uses the index you think it should or figure
Speaker 1: out if you're missing one.
Speaker 2: Finding missing indexes often starts with looking for those slow
Speaker 2: sequential scans and explain plans or MPG stats statements. Data
Speaker 2: pg Hero also has a suggested indexes feature based on
Speaker 2: its analysis the rail's best practices. Jim can sometimes flag
Speaker 2: missing foreign key indexes too.
Speaker 1: Can you make Postgress logs slow queries automatically YEP.
Speaker 2: Set log minduration statement in postcressql dot com.
Speaker 1: Yeah.
Speaker 2: Any query taking longer than say, five or milliseconds gets
Speaker 2: logged automatically. Even better, you can use the auto explain extension,
Speaker 2: which automatically logs the explain plan for slow queries so
Speaker 2: you see exactly why it was slow right in your logs.
Speaker 1: Auto explain Nice. Okay, let's talk index type, single column,
Speaker 1: multi column what's the strategy.
Speaker 2: Well, a multi column index on ABC can be used
Speaker 2: for queries filtering on A or A and B or A,
Speaker 2: B and C, but generally won't be used efficiently if you.
Speaker 3: Only filter on or C.
Speaker 2: It's left to right. You need to think about your
Speaker 2: query patterns. Sometimes multiple single column indexes are better. Sometimes
Speaker 2: a carefully ordered multi column indexes key. You also want
Speaker 2: to avoid redundant indexes, like having an index on A
Speaker 2: and another on AB. The first one might be unnecessary.
Speaker 1: Okay, what about indexing on like a lowercased email.
Speaker 2: That's indexes on expressions. You can literally do create unique
Speaker 2: index on users lower email. The index stores the result
Speaker 2: of the lower function, allowing fast case in sensitive lookups
Speaker 2: or uniqueness checks.
Speaker 1: Cool now, GIM indexes those sound different.
Speaker 3: Yeah.
Speaker 2: GN stands for generalized inverted index and this is where
Speaker 2: things get really interesting. For certain data types. GN indexes
Speaker 2: are optimized for indexing columns that contain multiple values think
Speaker 2: arrays or especially JSMB columns.
Speaker 1: So you can efficiently query inside a JSMB.
Speaker 2: Column exactly with a gene index on a json B
Speaker 2: column holding, say, trip details, you could very quickly find
Speaker 2: all trips where metadata bags and trunk is two, or
Speaker 2: where metadata by water offered is true. Without a gen index,
Speaker 2: searching inside json B is usually a slow, sequential scan.
Speaker 2: Gn is also used by extensions like PGTRGM for fast
Speaker 2: text similarity searches, finding Henry when someone types Henrietta, for example,
Speaker 2: really powerful for fuzzy searching.
Speaker 1: Wow, okay, jsonb's searching and fuzzy text. What are partial indexes?
Speaker 2: Partial indexes are indexes that have a wear clause in
Speaker 2: their definition. This means the index only includes rows that
Speaker 2: match that wear clock.
Speaker 1: Why would you too that to.
Speaker 2: Make the index much smaller and more efficient. A classic
Speaker 2: example is indexing soft deleted records. Create index on users
Speaker 2: we're deleted at is not null. If only one percent
Speaker 2: of your users are deleted, this index is tiny compared
Speaker 2: to indexing all users. It's perfect for queries that specifically
Speaker 2: target that subset of rows.
Speaker 1: Smaller index faster queries for that subset, smart and brin indexes.
Speaker 2: Brin block range index. Now, connecting this to the bigger picture,
Speaker 2: think about very large tables may pen only tables like
Speaker 2: event logs, where data is inserted in order off correlated
Speaker 2: with the timestamp. Light created app for these tables. Britt
Speaker 2: indexes can offer performance similar to a standard B tree
Speaker 2: index for range queries like finding events between two timestamps,
Speaker 2: but they're drastically smaller. We're talking maybe forty kilobytes versus
Speaker 2: two hundred megabytes for the same data range.
Speaker 1: It's huge space saving tiny indexes for ordered data. Interesting
Speaker 1: do indexes help with order by? Why?
Speaker 3: Absolutely?
Speaker 2: If you have an index on the columns you're ordering by,
Speaker 2: postgrescool can often just read the data directly from the
Speaker 2: index in the correct order, avoiding a costly sorting step.
Speaker 2: This includes handling a null's last or nulls first efficiently
Speaker 2: if the index is.
Speaker 1: Defined correctly, and covering indexes covering indexes.
Speaker 2: This uses the inclue gag keyword added in post crescool eleven.
Speaker 2: It lets you add extra columns to an index just
Speaker 2: for their data, not for searching or ordering. The benefit
Speaker 2: if a query can get all columns it needs both
Speaker 2: for filtering, ordering and for the seleclist directly from the
Speaker 2: index itself. Postgrescool can perform an index only scan. It
Speaker 2: never even has to visit the main table heap that
Speaker 2: avoids a whole layer of IO and can be a massive.
Speaker 1: Performance boost index only scans got it. Okay, So we've
Speaker 1: optimized queries, picked good indexes, but databases need ongoing care
Speaker 1: right like cars needing oil changes. The source talks about vacuum, analyze,
Speaker 1: re index the VR maintenance tasks.
Speaker 2: Yeah, absolutely vital, and it loops back to how Postgres
Speaker 2: school handles changes that NVCC system multiversion concurrency control. When
Speaker 2: you update or delete a row, postgriss doesn't immediately overwrite
Speaker 2: or remove the old data. It marks the old row
Speaker 2: version as invisible to new transactions, but the physical space
Speaker 2: isn't reclaimed right away. These invisible rows are called dead.
Speaker 1: Toples, and those dead tuples are bloat exactly.
Speaker 2: They take up space in your tables and indexes, making
Speaker 2: them larger than they need to be, which can slow
Speaker 2: down queries. This is where vacuum comes in.
Speaker 1: So vacuum cleans up the dead tupules.
Speaker 2: Yes, and the really neat thing is auto vacuum, which
Speaker 2: is postgres schools built in background process that does this automatically.
Speaker 2: It monitors tables for changes and runs vvace to reclaim
Speaker 2: space and analyze an ed SHEBSI to update statistics for
Speaker 2: the query planner periodically.
Speaker 1: Be said, it's often too conservative.
Speaker 2: Yeah, The default auto vacuum settings are often tuned for safety,
Speaker 2: not aggressiveness. On busy databases with high right rates, lots
Speaker 2: of updates and deletes, the default hittings might not keep
Speaker 2: up and blow can accumulate. You can, and often should,
Speaker 2: tune parameters like auto vacuum vacuum cost limit how much
Speaker 2: work it does per run and vacuum vacuum cost delay
Speaker 2: how long it pauses per tables or globally to make
Speaker 2: it run more often or more aggressively to keep bloat
Speaker 2: under control.
Speaker 1: Okay, tune auto vacuum. What about indexes? Do they get
Speaker 1: bloated to They do?
Speaker 2: And indexes can become fragmented or inefficient over time for
Speaker 2: rebuilding them. The best practice now is re index concurrently.
Speaker 1: Ah concurrently again, so no downtime right.
Speaker 2: Introduced in POSTGRESSCULL thirteen, it rebuilds the index in the
Speaker 2: background without blocking reads or rights to the table. Once
Speaker 2: the new index is ready, it swaps it in atomically.
Speaker 2: For older Postgres versions, the Prakic extension can do something
Speaker 2: similar for both tables and indexes.
Speaker 1: So mostly rely on autovacuum and renex Concurrently, do you
Speaker 1: ever need manual vacuum.
Speaker 2: Sometimes you might run vacuum analyze your pable manually after
Speaker 2: a huge data loaded deletion, just to make sure stats
Speaker 2: are up to date immediately. There's also a vacuum ver
Speaker 2: both option to see what it's doing, and since Postgres
Speaker 2: twelve vacuum skip blocked is useful to make sure your
Speaker 2: manual vacuum doesn't get stuck waiting for a lock on
Speaker 2: the table.
Speaker 1: Is there a way to see the bloat?
Speaker 2: Yeah, you can actually simulate it locally for learning, disable
Speaker 2: auto vacuum on a test table or on a ton
Speaker 2: of you P dates and then use SQL queries you
Speaker 2: can find them online or tools like pg hero to
Speaker 2: estimate the table and index bloat. Then you could run
Speaker 2: vaco full well, which does lock the table heavily, so
Speaker 2: don't run it on production just to see it reclaim
Speaker 2: the space aggressively. It helps build intuition.
Speaker 1: Good for understanding. You mentioned unused indexes earlier.
Speaker 2: Yes, crucial maintenance indexes aren't free, They take up disc
Speaker 2: space and more importantly, they slow down rights insert update
Speaker 2: to lead because the index has to be updated too regularly.
Speaker 2: Identifying and removing unused or redundant overlapping indexes is a
Speaker 2: key optimization. Tools like pg hero are great for finding
Speaker 2: these based on usage statistics tracked by postcress, so.
Speaker 1: Clean up unused indexes. How can we schedule these maintenance tasks?
Speaker 2: The pupg chron extension is super handy for this. It's
Speaker 2: a scheduler that runs inside postgress role itself. You can
Speaker 2: use it to schedule recurring SQEL commands like running a
Speaker 2: manual vacuum analyze on specific busy tables every night, or
Speaker 2: maybe rebuilding certain indexes periodically with index.
Speaker 1: Concurrently pgcra cron for scheduling any other quick maintenance tools.
Speaker 2: The rails pg extras gem provides some convenient RAKE tasks
Speaker 2: for common checks right from your railvapp, things like checking
Speaker 2: database cash hit rates, finding long running queries, diagnosing lock contention,
Speaker 2: and identifying null indexes. Null indexes yeah indexes where a
Speaker 2: very high percentage of the entries are actually NLLL. Depending
Speaker 2: on your queries, these might be candidates for conversion into
Speaker 2: some more efficient partial indexes using a were column is
Speaker 2: not NL class got.
Speaker 1: It okay, final big area scaling, more users, more traffic.
Speaker 1: This often leads to problems with database connections, right hitting limits.
Speaker 2: Yeah, that's a common scaling bottleneck. Postgress will has a
Speaker 2: hard limit on simultaneous connections set by the max connections parameter.
Speaker 2: If your application tries to open more connections than that
Speaker 2: limit allows, you get the dreaded fatal Sorry too many
Speaker 2: clients already error?
Speaker 1: Ugh yeah, not fun. How do you deal with that?
Speaker 3: Well, first you need visibility.
Speaker 2: Use pgstat activity again to see how many connections are open,
Speaker 2: in what state they're in. Are they active, idle or
Speaker 2: worryingly idle in transaction meaning a connection is holding a
Speaker 2: transaction open but isn't doing any work. Potentially holding locks.
Speaker 1: Idle in transaction is bad.
Speaker 3: Very bad.
Speaker 2: You need to find and fix the application code causing that.
Speaker 2: But the main architectural solution for handling lots of connections
Speaker 2: efficiently is connection pooling.
Speaker 1: Connection pooling like pg bouncer.
Speaker 3: It's exactly.
Speaker 2: Pg bouncer is the standard, free, open source connection pooler
Speaker 2: for Postgress. What's fascinating here is how it works. It
Speaker 2: sits between your rails application and your postgrescool database. Your
Speaker 2: Rails apps connect to pg bouncer, which can handle thousands
Speaker 2: of front end connections, and pg bouncer maintains a smaller
Speaker 2: managed pool of actual connections to the back end postgresscole database,
Speaker 2: reusing them efficiently. It drastically reduces the overhead of constantly
Speaker 2: opening and closing connections to Postgress itself.
Speaker 1: So it lets you handle way more app servers without
Speaker 1: overwhelming the database's connection limits.
Speaker 2: Precisely, it's essential for scaling rails apps with postgresscool. You
Speaker 2: do need to configure pg bouncer's pool modes though. Transaction
Speaker 2: pooling is very common and efficient, but it has a catch.
Speaker 2: It doesn't work well with postgrescool session based features like
Speaker 2: prepared statements, so if you use transaction pooling, you generally
Speaker 2: need to disable prepared statements in your rails database dot
Speaker 2: eml oh.
Speaker 1: Okay disable prepared statements for transaction pooling.
Speaker 2: What's the alternative session pool mode in pg bouncer. It
Speaker 2: works fine with prepared statements, but it's less efficient at
Speaker 2: re using connections as it pins a back end connection
Speaker 2: to a client for the entire session duration. It's often
Speaker 2: the fallback if you can't disable prepared statements.
Speaker 1: Got it? Transaction versus session pooling, and we still need
Speaker 1: those timeouts right.
Speaker 2: Absolutely, statement time out, lock, time out, and especially idle
Speaker 2: in transaction. Session timeout become even more critical when you
Speaker 2: have a pooler involved to prevent misbehaving application instances from
Speaker 2: hogging pooled connections.
Speaker 1: Okay, let's dive a bit deeper into locking. We mentioned
Speaker 1: pessimistic locking.
Speaker 2: Right postgressholes Default locks are taken up front. Peaglocks few
Speaker 2: gives you the granular details you can explicitly request locks
Speaker 2: in SQL. Select it for update is common. It locks
Speaker 2: the rose you select, preventing any other transaction from updating
Speaker 2: or deleting them or selecting them for update until your
Speaker 2: transaction commits or rolls back.
Speaker 1: What if the row is already locked.
Speaker 2: By default for update will wait, but you can add
Speaker 2: no weight If the row is locked, the query fails immediately,
Speaker 2: or you can use skiplocked. This tells postgress to just
Speaker 2: ignore any locked rows and return only the ones that
Speaker 2: can lock immediately. Super useful for implementing job queues where
Speaker 2: multiple workers might try to grab the next available job.
Speaker 1: No wait and skip locked. Okay, is there a weaker.
Speaker 2: Lock yees selectaseball for share. This takes a shared lock.
Speaker 2: Multiple transactions can hold a shared lock on the same
Speaker 2: row simultaneously. It prevents anyone from getting an exclusive lock,
Speaker 2: like for update or from delete, but it allows other
Speaker 2: transactions to also read or select for share. Useful if
Speaker 2: you need to read data and ensure it doesn't change
Speaker 2: before your transaction finishes, but you don't mind others reading
Speaker 2: it too.
Speaker 1: Okay, that's pessimistic locking and postgress. What about optimistic locking
Speaker 1: and RAILS, right, that's different.
Speaker 2: Active record optimistic locking is an application level strategy. You
Speaker 2: add a lock version integer column to your table. When
Speaker 2: railsloads a record, it remembers the lock version. When you
Speaker 2: go to save it, RAILS checks if the lock version
Speaker 2: in the database still match is the one it loaded.
Speaker 2: If it doesn't match, meeting someone else modified the record
Speaker 2: in the meantime, RAILS raises an active record stale object terror.
Speaker 2: Instead of overwriting the changes, it forces you to handle
Speaker 2: the conflict in your application code.
Speaker 1: So detect conflicts at right time rather than preventing them
Speaker 1: with locks exactly.
Speaker 2: And then there are advisory locks. These are completely application
Speaker 2: defined locks using functions like peak advisory lock key. They
Speaker 2: don't lock table rows, they just lock an arbitrary number.
Speaker 2: The key useful for coordinating background jobs or ensuring only
Speaker 2: one process runs a specific task at a time without
Speaker 2: needing a dedicated table.
Speaker 1: Okay, lots of locking options shifting again. Dealing with large
Speaker 1: data sets often means pagination. What are the pitfalls?
Speaker 2: The biggest pitfall is traditional limit offset pagination. It's easy
Speaker 2: to implement dot limit twenty dot offset, but it gets
Speaker 2: incredibly slow on large tables as the offset value grows.
Speaker 1: Why is high offset slow.
Speaker 2: Because postgress will still has to fetch all the rows
Speaker 2: up to the offset plus limit point, sort them, and
Speaker 2: then discard the offset rows. So offset five hundred thousand
Speaker 2: is going to be agonizingly slow because it has to
Speaker 2: process over five million rows just to find the twenty
Speaker 2: you want.
Speaker 1: Ouch. So what's better for large tables? Cursors?
Speaker 2: Database cursors declared cursor fetch solve the offset problem by
Speaker 2: maintaining state on the server. They're consistent, but they have drawbacks.
Speaker 2: They keep a transaction open, which consumes resources and doesn't
Speaker 2: play well with pg bouncer's transaction pooling mode. An active
Speaker 2: record doesn't natively support them easily.
Speaker 1: Okay, So cursors are tricky, what's the recommended way?
Speaker 2: Then key set pagination sometimes called seek based pagination. We
Speaker 2: connect this to the bigger picture. This is generally the
Speaker 2: most efficient method for large data sets. Instead of offset,
Speaker 2: you use a wear clause based on the value of
Speaker 2: the last item scene on the previous page, combined with
Speaker 2: order buy and limit. For example, where is a last
Speaker 2: seeded order by sp limit twenty. Because you're filtering directly
Speaker 2: using an index column, the database can jump almost directly
Speaker 2: to the starting point for the next page, avoiding scanning
Speaker 2: all the previous rows. It stays fast even on page
Speaker 2: ten thousand.
Speaker 1: Key set pagination use the last scene value in the
Speaker 1: wear clause, got it last topic Bulk data writes. How
Speaker 1: to do those efficiently?
Speaker 2: Upsorts are common, combining an insert with an update if
Speaker 2: the row already exists. Postgrescoll has insert insert and tree
Speaker 2: on conflict do nothing if it exists, ignore the insert
Speaker 2: or insert in on conflict, do update set if exists,
Speaker 2: update specify columns. Rails's insertal method has an on conflict
Speaker 2: option to handle this, and Postcress fifteen introduced this standard
Speaker 2: SQL merge command, which is even more powerful for complex
Speaker 2: absurd logic.
Speaker 1: Okay, upsurts handle conflicts. What about just loading lots of
Speaker 1: new data like from a file.
Speaker 2: For bulk loading from files, especially csvs, the absolute fastest
Speaker 2: way is the native postgress will copy command copy mytable
Speaker 2: from my fil dot csv with format csv header. It
Speaker 2: bypasses a lot of the normal SQL processing overhead and
Speaker 2: is incredibly efficient, especially if we run into the same
Speaker 2: transaction as a create table or trunkate as it can
Speaker 2: minimize wall loogging.
Speaker 1: Then copy is king for bulk loads. Anything else cool
Speaker 1: For external.
Speaker 2: Data, yeah, this is a surprisingly powerful and clever tool
Speaker 2: foreign data rappers, specifically the filep and do extension file
Speaker 2: EPI darol lets you define an external file like CSV
Speaker 2: log file as if it were a postcrescoll table. You
Speaker 2: can then run SQL queries directly against that external file
Speaker 2: without importing the data seriously.
Speaker 1: Query as CSV file with SQL without loading it seriously.
Speaker 2: Create extension fileffew year, create server file server foreign and
Speaker 2: data wrapper FILEFEEDW, create foreign table, mylogues, server filesiver options,
Speaker 2: fill namepath, dollars, dot csv, format csv. Then just selects
Speaker 2: from milogues where air code e cools five hundred. It's
Speaker 2: amazing for analyzing log files or accessing archive data stored
Speaker 2: externally a real game changer sometimes.
Speaker 1: Wow, Okay, what a journey we've been on. We started
Speaker 1: right at the beginning, setting up that local performance lab
Speaker 1: with rideshare. Then we dove into admin basics, safe experimentation,
Speaker 1: then hit data integrity with constraints which go way beyond
Speaker 1: basic rills validations. We tackled those scary production migrations, learning
Speaker 1: about concurrently and tools like strong migrations. Then we really
Speaker 1: got into optimizing active record itself n plus one queries,
Speaker 1: eager loading cts, materialized views, and finally went deep into
Speaker 1: postgress internals, explain advanced indexing like gins bren, partial indexes,
Speaker 1: essential maintenance with vacuum and re index and handling scaling
Speaker 1: challenges like connection pooling with pg bouncer locking, and efficient
Speaker 1: folk data handling with copyy and even FDWs. You really
Speaker 1: have gained a shortcut here to some seriously high performance
Speaker 1: postgrescol skills. That's an incredibly valuable step.
Speaker 2: It really is, and that's the goal, right to equip
Speaker 2: you with these practical, actionable insights, hopefully some surprising facts
Speaker 2: too that can genuinely make a difference in your applications.
Speaker 2: You should now have a much deeper feel for how
Speaker 2: postgresco operates and how to really harness its power with rails.
Speaker 2: So maybe a final thought to leave you with, Given
Speaker 2: all this immense power and flexibility we've uncovered in post
Speaker 2: gris skool today, just think about your own work. Is
Speaker 2: there existing application bottleneck, maybe something you struggled with that
Speaker 2: could be dramatically simplified or sped up by using one
Speaker 2: of these database features, maybe one you hadn't even considered before.
Speaker 2: Keep asking that question, keep exploring, because continuously improving the
Speaker 2: operational excellence of your databases, well, it's an ongoing process,
Speaker 2: but it's definitely a rewarding one,