Speaker 1: Okay, let's unpack this. If you think of Postgres School
Speaker 1: server is just you know, place to store data like
Speaker 1: a big passive bucket, you're missing out on a tremendous
Speaker 1: amount of power. Today we're diving into Postgres School server programming.
Speaker 1: We're drawing from some really insightful excerpts from the technical
Speaker 1: book Postgreschool Server Programming. Our mission really is to show
Speaker 1: you how Postgres School can actually be seen as an
Speaker 1: application development framework.
Speaker 2: Now.
Speaker 1: Pushing logic directly to the database layer offers well shortcuts
Speaker 1: to building faster, more secure, and highly maintainable applications. Yeah.
Speaker 2: And what's fascinating here is we're not just talking about
Speaker 2: database administration and the traditional sense, not at all. We're
Speaker 2: exploring how the database itself can become like a proactive,
Speaker 2: intelligent part of your application's core logic.
Speaker 1: Right.
Speaker 2: It stops being just passive storage and starts being an
Speaker 2: active participant, your single source of truth for business rules.
Speaker 2: You know, hashtag tag the transformative power of server programming.
Speaker 1: So let's challenge that whole data bucket philosophy. Yeah, imagine
Speaker 1: a common situation like a bank transfer. Say Bob wants
Speaker 1: to send I don't know fourteen dollars to Mary h
Speaker 1: The typical way you might write separate sequl commands from
Speaker 1: your application, right, want to take money from Bob a debit,
Speaker 1: another to give to Mary a credit? But I mean
Speaker 1: think about the problems there at first security, Do your
Speaker 1: app really need to know Bob's balance or just can't
Speaker 1: he send the money?
Speaker 2: That's a big one.
Speaker 1: Then speed, you've got multiple round trips to the database
Speaker 1: slows things down. And the big one integrity. What if
Speaker 1: the system crashes halfway through, Bob's money is gone, Mary
Speaker 1: never gets it.
Speaker 2: Nightmare scenario exactly, And that's precisely why server programming is
Speaker 2: such a well a game changer. By executing that transfer
Speaker 2: logic directly on the database server, you're centralizing it. It's
Speaker 2: not just tidy code. It's a fundamental shift. You're putting
Speaker 2: the rules in the most robust place, the database itself.
Speaker 2: It becomes this active.
Speaker 1: Partner active partner.
Speaker 2: I like that, and it cuts out all those network
Speaker 2: round trips, so it's way faster. You write a function.
Speaker 2: Maybe in plpg's it does all the checks, does Bob
Speaker 2: exist enough funds, is Merry valid? And it just returns okay,
Speaker 2: or you know, not enough funds. The client app doesn't
Speaker 2: need the details, just the success or failure. No sensitive
Speaker 2: data leakage.
Speaker 1: That is incredibly powerful for security, for consistency, yeah yeah,
Speaker 1: But what if you want the database to react automatically
Speaker 1: like inventory management, stock levels, change offers, need updating instantly,
Speaker 1: no app involved triggers.
Speaker 2: That's exactly what triggers are for magic pretty much. They
Speaker 2: let operations in the database automatically cause other things to happen.
Speaker 2: So say you've got your fruit sinstock and your fruit
Speaker 2: offer tables, A trigger can make sure that when you
Speaker 2: update an offer amount for apples, uh huh, the reserved
Speaker 2: count in fruits in stock just adjusts automatically. It stops
Speaker 2: you over reserving, stops you selling stock that's already spoken for,
Speaker 2: even across different tables. It enforces that link.
Speaker 1: Okay, that's clever.
Speaker 2: And building on that automatic reaction idea, triggers are fantastic
Speaker 2: for auditing, indispensable. You can set one up to log
Speaker 2: every single action on important tables like imagine a salary's table.
Speaker 2: An audit trigger can grab the username, the timestamp, the
Speaker 2: operation type, insert, update, delete, and even capture the old
Speaker 2: salary and the new salary. Maybe store the whole old
Speaker 2: and new row as JSON for a complete record. It's
Speaker 2: a rock solid audit trail. It's automatic, totally automatic. Plus
Speaker 2: triggers can enforce data consistency, simple things like automatically converting
Speaker 2: an employee name to uppercase whenever it's inserted or updated.
Speaker 2: Ensures uniformity. You know, no need to rely on every
Speaker 2: client app remembering the rule.
Speaker 1: So functions, centralized logic, triggers automate reactions. But what about
Speaker 1: teaching postcress new tricks entirely? What does extensibility really mean here?
Speaker 1: Is it just more functions?
Speaker 2: Oh, It's much more profound than just functions. Extensibility means
Speaker 2: you can actually extend postgres school itself. It's language, it's capabilities.
Speaker 2: You can define your own custom data types like uh
Speaker 2: fruity maybe that understands bushels. And you can teach it
Speaker 2: custom operators like how to compare apples and oranges directly
Speaker 2: in a query? H seriously, well metaphorically maybe, But you
Speaker 2: can define operators for your custom types. And yes, user
Speaker 2: define functions. UDFs are a huge part, and these functions
Speaker 2: can get sophisticated. They can handle complex arguments like XML
Speaker 2: or JSON or return results in those formats too great
Speaker 2: for modern architectures like SOA or micro services.
Speaker 1: Right makes sense, and you mentioned performance earlier. Extensibility ties
Speaker 1: into that too, especially server side caching.
Speaker 2: Absolutely, server side casing is a classic pattern enabled by functions.
Speaker 2: The logic is simple. Really, inside your function, first check
Speaker 2: if the value you need is already cashed, maybe in
Speaker 2: a table. Is it there, is it recent enough? If yes, great,
Speaker 2: return it. If not, compute it, store it in the cash,
Speaker 2: then retun it.
Speaker 1: So next time it's fast.
Speaker 2: Exactly. Think about calculating total company sales that might involve
Speaker 2: querying thousands of rows cash the result. There's this great
Speaker 2: story about a Java e email system. It was sending
Speaker 2: maybe a few hundred emails a second, okay, but not amazing.
Speaker 2: Then someone rewrote the email generation part as ap peel
Speaker 2: Peril function inside.
Speaker 1: Postgres school, inside the database.
Speaker 2: Inside the database, suddenly it wasn't sending hundreds, It was
Speaker 2: spewing out tens of thousands of emails per second.
Speaker 1: Wow, that's that's incredible.
Speaker 2: It was all because the computation happened near data minimal
Speaker 2: network latency. It went from like a two point two
Speaker 2: millisecond query from the client to zero point one to
Speaker 2: two milliseconds inside the database. Huge difference.
Speaker 1: That is a massive jump, and you can see how
Speaker 1: that ties into maintenance and security too. If all your
Speaker 1: core data logic is in database functions or views, upgrading
Speaker 1: is just running a script, a detail.
Speaker 2: Script, YEP, data definition language. You update the function in
Speaker 2: the database.
Speaker 1: No downtime, no complex roll out across all your different.
Speaker 2: Client apps, exactly. All clients just start using the new
Speaker 2: logic immediately. It simplifies maintenance enormously. It's security big win
Speaker 2: there too. If you grant database users permission to execute
Speaker 2: only specific functions, not direct access to tables, then even
Speaker 2: if an attacker compromises your web server, say, they can't
Speaker 2: just run select from users and dump all your data.
Speaker 1: They can only run the functions you've a looked.
Speaker 2: What you've designed to be safe right to only return
Speaker 2: specific non sensitive info or perform validated actions. It drastically
Speaker 2: reduces the attack surface hashtag tag tech trag y. Postgress
Speaker 2: School the environment advantage.
Speaker 1: So, okay, putting lodging in the database is powerful, we
Speaker 1: get that, but why postgres school specifically, what makes it
Speaker 1: the go to platform for this kind of server programming.
Speaker 2: Well, first off, the cost of acquisition.
Speaker 1: It's zero. Can't be free, right.
Speaker 2: Compare that to commercial databases SEQL server Oracle. You're talking
Speaker 2: potentially thousands, even tens of thousands in licensing fees. Postgress
Speaker 2: will costs nothing to download, nothing to use, and this
Speaker 2: directly leads to a low cost of failure. When you're
Speaker 2: starting a project things are uncertain, you might experiment change direction.
Speaker 2: Being able to try things out, even scrap an idea
Speaker 2: without being locked into expensive long term licenses, that's a
Speaker 2: huge advantage. It frees you up to.
Speaker 1: Innovate definitely, and that ties into licensing more broadly, doesn't it.
Speaker 1: I remember the unease when Oracle bought my sequel that
Speaker 1: vast and thunderous sucking sound.
Speaker 2: Does someone put it huh, yes, exactly. There is a
Speaker 2: lot of uncertainty about the future of my squels licensing.
Speaker 2: Postgres School's license, though, is incredibly liberal bs D style. Basically,
Speaker 2: you can have the source code, do anything with it
Speaker 2: that you want, redistribute it however you jolly well please,
Speaker 2: and those rights extend indefinitely.
Speaker 1: Wow.
Speaker 2: For companies, that removes massive legal risk. You know, the
Speaker 2: terms won't suddenly change. You know, you can build products
Speaker 2: on it without unexpected fees popping up.
Speaker 1: That's huge peace of mind.
Speaker 2: Then there's predictability. Postgreds School is known for sticking really
Speaker 2: closely to SQL standards. Now, sometimes that might feel a
Speaker 2: bit stricter, maybe less forgiving than other databases if you
Speaker 2: write slightly nonstandard sequel, but the upside is fewer unintended behaviors,
Speaker 2: fewer surprises. You know it's going to work the way
Speaker 2: the documentation says it works. That means less chance of
Speaker 2: your application breaking in subtle ways down the road because
Speaker 2: of some weird database quirk. It provides a really stable foundation.
Speaker 2: You can trust it.
Speaker 1: Okay, stability, predictability, Yeah, what else? You mentioned something about
Speaker 1: the community earlier.
Speaker 2: Ah, yeah, the community aspect is actually really interesting and
Speaker 2: often overlooked. With commercial databases, trying to talk to the
Speaker 2: actual core developers, good luck, you're usually dealing with layers of.
Speaker 1: Support stat right ticketing systems.
Speaker 2: Exactly with postgres School, many of the core developers hang
Speaker 2: out on IRC channels, they go to conferences, you can
Speaker 2: actually talk to them.
Speaker 1: And they like beer apparently.
Speaker 2: Well the book mentions that, But the point is there's
Speaker 2: this direct connection. It fosters a real sense of shared
Speaker 2: ownership and concern for the project's health. Expert help is
Speaker 2: often just a question of a way. It's a very
Speaker 2: vibrant ecosystem.
Speaker 1: That's a really different feel.
Speaker 2: Definitely, and technically a huge advantage is its support for
Speaker 2: multiple procedural languages. It's not just plpgcquall the built in one.
Speaker 2: You can write server side functions in Python, Perl, TCL,
Speaker 2: even C.
Speaker 1: And you can add or remove these languages on.
Speaker 2: A running server, no downtime needed to install, Say peel
Speaker 2: Python support and think back to our earlier example. The
Speaker 2: bad way of doing things maybe with PHP connecting, pulling data,
Speaker 2: figuring things out client side, then sending an update back.
Speaker 1: Right, the multiple roundtrips, the potential integrity issues exactly.
Speaker 2: That leads to terrible scalability. The right way is pushing
Speaker 2: that logic into a database function using one of these
Speaker 2: powerful languages. A single efficient.
Speaker 1: Call makes sense. What about transactions? How does post grace
Speaker 1: handle those?
Speaker 2: It's very sensible, very safe. By default. The standard isolation
Speaker 2: level is read committed me, meaning your transaction will only
Speaker 2: ever see data that has been fully committed, fully saved
Speaker 2: by other transactions. You don't get dirty reads seeing incomplete
Speaker 2: changes that might get rolled back later. That avoids a
Speaker 2: whole class of bugs.
Speaker 1: That sounds safer it is.
Speaker 2: And by default, each individual seql statement you send acts
Speaker 2: as its own little transaction. It commits immediately, though client
Speaker 2: tools like fusequal often wrap things in a transaction block automatically.
Speaker 2: If you're typing multiple commands okay, and one more thing
Speaker 2: on control postgrescool gives you more control over how your
Speaker 2: functions execute, specifically around security, their security and voker. That's
Speaker 2: the default. The function runs with the permissions of the
Speaker 2: user calling it, but there's also security definer. This is
Speaker 2: really useful. The function runs with the permissions of the
Speaker 2: user who created it.
Speaker 1: Ah, so you could let a less privileged user run
Speaker 1: a function that needs higher privileges for a very specific controlled.
Speaker 2: Task, precisely temporary safe privilege escalation. They can't access the
Speaker 2: underlying table directly, but they can call the function which
Speaker 2: performs a specific safe action on their behalf. And you
Speaker 2: can also define a cost for a function. This is
Speaker 2: like giving a hint to the query planner, the.
Speaker 1: Part of the database that figures out how to run queries.
Speaker 2: Yeah, you can tell it. Hey, this function is really
Speaker 2: expensive to run, so the planner will try to avoid
Speaker 2: calling it too often if it can find a cheaper
Speaker 2: way to get the result. Helps optimize overall performance. Hashtag
Speaker 2: hashtag deep dive into language capabilities and advanced techniques.
Speaker 1: Okay, let's dive into some of the actual coding. Then
Speaker 1: the warkhorse so PLPG SQUL you called it the workhoorse.
Speaker 1: It's built in, no extra setup needed. What makes it
Speaker 1: so fundamental for server side logic and postgress.
Speaker 2: It's just incredibly well suited for combining SQL with procedural logic,
Speaker 2: you know, if statements, loops, variables. It's great for encapsulating
Speaker 2: those multi step operations we talked about, like the bank transfer.
Speaker 2: You can access function arguments easily, either by their position
Speaker 2: or by a name. You've got your standard of penols,
Speaker 2: your case. Statements for conditional logic loops are there too.
Speaker 2: But a really important warning. Looping through query results inside
Speaker 2: plpg'sl Generally that's considered doing it wrong. It's usually very inefficient,
Speaker 2: high processor cost, high memory use. SQL is designed for
Speaker 2: set based operations. You should almost always try to find
Speaker 2: a way to express your logic using SQL sets rather
Speaker 2: than row biro looping in plpg sql let the database
Speaker 2: engine optimize the set operation.
Speaker 1: Good tip. Okay, what about getting results out of functions?
Speaker 2: Right? You use perform if you just want to execute
Speaker 2: a query for its side effects like an insert or update,
Speaker 2: and you don't care about the result. If you do
Speaker 2: need the result, you use select into to put it
Speaker 2: into a variable.
Speaker 1: Makes sense. And you can return more than just single
Speaker 1: values right like lists or tables?
Speaker 2: Oh yeah, absolutely, you can return a set of integer
Speaker 2: like generating the Fibonacci sequence. You can return set of
Speaker 2: table name, which effectively returns rows matching that table structure.
Speaker 2: You can define functions with out parameters which become part
Speaker 2: of the result row, or use the very clear returns
Speaker 2: table syntax to define the output columns right there.
Speaker 1: What if the structure isn't known beforehand?
Speaker 2: Good question. You can use return set off record, but
Speaker 2: then you have to provide a column definition list when
Speaker 2: you call the function. More flexibly, you can use polymorphic
Speaker 2: types like set off an element or set off any
Speaker 2: compatible for functions that can work with or return different
Speaker 2: but related types. And there's variatic arguments, which lets a
Speaker 2: function accept an arbitrary number of arguments of a certain
Speaker 2: type past as an array super flexible.
Speaker 1: You also mentioned cursors briefly before for large results.
Speaker 2: Right reef Cursors specifically, cursors are internal structures that let
Speaker 2: you fetch query results incrementally lazily, instead of the database
Speaker 2: computing the entire results set and sending it all at once,
Speaker 2: which could take ages and lots of memory. For huge queries,
Speaker 2: you get a pointer kinda yeah, a reference. Then your
Speaker 2: application can fetch rows from that cursor as needed. It's
Speaker 2: great if you only need the first few rows quickly,
Speaker 2: or if you want to return multiple independent result sets
Speaker 2: from a single function. Call PLPGCQ trigger functions Automated guardians no.
Speaker 1: Get back to triggers. These automatic guardians you said, they
Speaker 1: get old and NW records. How does that actually work
Speaker 1: in the code.
Speaker 2: So when you write a trigger function one that returns
Speaker 2: trigger postgresscool automatically makes special variables available inside it. Old
Speaker 2: is a record variable holding the row values before the
Speaker 2: operation for update and delete. New holds the values after
Speaker 2: the operation for insert and update. And you also get
Speaker 2: tg variables like TGP tells you if it was an insert,
Speaker 2: update or delete tg table name gives you the table
Speaker 2: name and so on. Lots of context, so.
Speaker 1: That audit trigger example, it uses those exactly.
Speaker 2: It would check TGOP. If it's an insert, it logs
Speaker 2: the new record maybe converted to Jason. If delete, it
Speaker 2: logs old. If update, it logs both old and new,
Speaker 2: along with the username from current user timestamp now table
Speaker 2: name and operation type creates that perfect audit law.
Speaker 1: Very cool, and you mentioned preventing actions.
Speaker 2: Yeah, you could have a simple trigger function that just
Speaker 2: raises an exception if t GOP is delete or truncate
Speaker 2: on certain critical tables, just stops the operation cold or
Speaker 2: more subtly. In a before trigger one that runs before
Speaker 2: the change is actually made, you can modify the new w.
Speaker 1: Record uh like the upper case name example.
Speaker 2: Precisely we're setting timestamps. A common pattern is a function
Speaker 2: that sets create at and created by only on insert,
Speaker 2: and updated at and updated by on update, ensuring those
Speaker 2: created fields are immutable after the initial insert. But again,
Speaker 2: the reminder triggers are powerful, but use them judiciously. For
Speaker 2: complex application logic, they can make debugging harder because the
Speaker 2: logic isn't always obvious in the main application code. Keep
Speaker 2: them focused.
Speaker 1: If you can seeing inside.
Speaker 2: That debugging point is key. If you've got complex logic
Speaker 2: hidden away and functions and triggers, how do you troubleshoot
Speaker 2: when things go wrong. It's not like setting a break
Speaker 2: point in your Java or Python code.
Speaker 1: Is it. It's definitely different. The simplest approach is what you
Speaker 1: might call manual debugging. Using raise notice, you just sprinkle
Speaker 1: raise notice reached point a with value my variable throughout
Speaker 1: your code. The messages show up in your client like
Speaker 1: psdes col like print statements.
Speaker 2: Basic.
Speaker 1: It's exactly simple, no installation needed. Great for checking values
Speaker 1: in regression test scripts. The downside is it can clutter
Speaker 1: your output if you leave them in. For actual error handling,
Speaker 1: you use reez exception. You can provide a custom error
Speaker 1: message and even a specific SQL state code which client
Speaker 1: applications can catch and react to programmatically. Okay, And if
Speaker 1: you want messages to go to the server's log file
Speaker 1: instead of the client maybe for background tasks or just
Speaker 1: less noise, you use.
Speaker 2: Raise luck right, So print debugging essentially, what about stepping
Speaker 2: through code?
Speaker 1: For that? You need the visual PLPG SQL debugger usually
Speaker 1: integrates with GUI tools like PG admin once it's set up,
Speaker 1: and the setup can be a bit of a pain.
Speaker 1: Fair warning is pretty powerful. You can set break points
Speaker 1: in your plpg sql.
Speaker 2: Code like in a normal id YEP, step.
Speaker 1: Through line by line, step into, step over, inspective values
Speaker 1: of variables, even change variable values on the fly to
Speaker 1: test different scenarios. You can also set global breakpoints like
Speaker 1: stop whenever any code calls. This specific function really handy
Speaker 1: for tracking down unexpected.
Speaker 2: Calls and the advantages.
Speaker 1: The big advantage over raised notice is it doesn't use
Speaker 1: server resources when you're not actively debugging, and you don't
Speaker 1: need to modify your function code with print statements. The
Speaker 1: disadvantage that installation process can be fiddly. Using unrestricted languages
Speaker 1: expanding horizons PL Python.
Speaker 2: Okay, let's shift gears beyond PLTG squel. You mentioned unrestricted
Speaker 2: or untrusted languages like pl python. That untrusted label, why
Speaker 2: is it there? What's the deal? Right?
Speaker 1: The untrusted language is pl python, PEEL, perileu, PLTCLU, peel, JAVAU.
Speaker 1: They're called that because they break out of the database sandbox.
Speaker 1: They can by default access the server's file system, make
Speaker 1: network connections, send signals to other processes, basically do anything
Speaker 1: the underlying language, Python, Perol, etc. Could do on the
Speaker 1: host operating system.
Speaker 2: Ahotential security risk.
Speaker 1: Huge potential security risk. That's why you absolutely should not
Speaker 1: grant permission to create functions in these languages to arbitrary
Speaker 1: database users. You don't fully trust. They could potentially read
Speaker 1: sensitive files or disrupt the server.
Speaker 2: Got it, so why use them?
Speaker 1: Power and convenience, Python, for example, has vast libraries for
Speaker 1: everything imaginable. It's often much easier and requires less boilerplate
Speaker 1: code to do complex text processing, interact with web services,
Speaker 1: or use external libraries and peel Python that it would
Speaker 1: be in plpg sql or especially C plus. Many developers
Speaker 1: are already comfortable with Python and peel Python handles the
Speaker 1: data type conversions between postcrescool types and Python types pretty
Speaker 1: transparently for you.
Speaker 2: Okay, so if I'm writing a pel Python function, how
Speaker 2: do I run SQL queries from within that Python code.
Speaker 1: Peel python provides a dedicated module usually accessed as plea high.
Speaker 1: It has functions like peelpi dot execute to run arbitrary
Speaker 1: SQL queries. It also has peelpi dot prepare, which lets
Speaker 1: you create a prepared statement plan. This is really important
Speaker 1: for performing. So if you're running the same kind of
Speaker 1: query multiple times, maybe just with different parameters.
Speaker 2: Prepare ones execute many times exactly.
Speaker 1: Preparing can be costly, so caching that plan makes subsequent
Speaker 1: executions much faster. And when you're writing a trigger function
Speaker 1: in peel Python, instead of old and new to W variables,
Speaker 1: you get a Python dictionary usually called TD, which contains
Speaker 1: all the trigger context td ol, td new, tdvn td, win,
Speaker 1: et cetera. Right, what about dynamic sqel building queris on
Speaker 1: the fly? Huge risk of seql injection There absolutely critical point.
Speaker 1: You should never just concatenate strings together with user input
Speaker 1: to build SQL queries in any language, including PL Python.
Speaker 1: Plupy provides functions specifically for safe quoting Peelpi dot quote
Speaker 1: item for table or column names, peelpi dot quote literal
Speaker 1: for string values, and peelpi dot quote nullable which handles
Speaker 1: nls correctly. Always use these when incorporating external data into
Speaker 1: dynamic queries. It's your primary defense. Good advice. How does
Speaker 1: error handling work within PL Python? Does it interact with
Speaker 1: database transactions? You use standard Python t dot accept blocks
Speaker 1: to catch errors that might occur within your Python code
Speaker 1: or from SQL queries executed via PLI. For transactional control
Speaker 1: within your function, pl Python provides PLPI dot subtransaction. This
Speaker 1: lets you create save points. You can wrap a block
Speaker 1: of code and a subtransaction context manager, and if an
Speaker 1: exception occurs inside that block, only the database changes made
Speaker 1: within that block are rolled back ah.
Speaker 2: So you can contain failures exactly.
Speaker 1: It's important to note, though, that this only rolls back
Speaker 1: database changes. Any changes to your Python variables within that
Speaker 1: block will persist unless you explicitly handle them in your
Speaker 1: accept clause. Python's atomicity is separate from the databases.
Speaker 2: Right and debugging pl pythons still no visible debugger I guess.
Speaker 1: Not built in no, so you fall back to similar
Speaker 1: techniques as with plpg sqel, but using pythons tools dot
Speaker 1: PLIPI dot notice, PLPI dot warning, pelpi dot error to
Speaker 1: send messages standard Python A certain statements are useful too.
Speaker 1: You can also redirect pythonsys dot sit out and syst
Speaker 1: dot sitch a dor within your function and to send
Speaker 1: output to the postgrec will server log file, which could
Speaker 1: be helpful. But the real power here is doing things
Speaker 1: you just wouldn't normally think of doing inside a database,
Speaker 1: generating image thumbnails using Python's PIL library, sending emails using SENTIMENTPLIP,
Speaker 1: Interacting with external APIs all possible directly from database functions.
Speaker 1: With pl python. It really encourages thinking outside the SQL box,
Speaker 1: writing advanced functions and c raw power.
Speaker 2: Okay, peel python opens up a lot, But then there's
Speaker 2: C writing functions directly and C that sounds like the
Speaker 2: ultimate level of integration and probably complexity. When you absolutely
Speaker 2: need C, you go to C.
Speaker 1: When you need the absolute bare metal performance, or what
Speaker 1: do you need to interact with Postgrescol's internal APIs at
Speaker 1: a level that interprets languages just can't reach. Think extremely
Speaker 1: computationally intensive algorithms or functions that need to process millions
Speaker 1: of rows per second with minimal overhead, or maybe tightly
Speaker 1: integrate with operating.
Speaker 2: System features, and how does it work? Is it complicated?
Speaker 1: There's definitely more ceremony involved than with plpg sql or
Speaker 1: peel Python. You need to include specific postgress will header
Speaker 1: files postgress dot HfN gr dot H. You need a
Speaker 1: special macro pg modumi magic to identify the compiled object
Speaker 1: as compatible. Each C function needs to be declared with
Speaker 1: PGF function in ov one and to use specific C
Speaker 1: macros like pg targin thirty two to get arguments and
Speaker 1: pg turn in thirty two to return values. Some more boilerplate,
Speaker 1: A bit more, yes, but the built process is actually
Speaker 1: quite streamlined now thanks to pdxs posts wll's extension building infrastructure.
Speaker 1: You typically just write a simple make file, run make
Speaker 1: and then pseudo make install and postcress will knows how
Speaker 1: to compile and link your C code correctly. Then you
Speaker 1: just create function and SQL to link it.
Speaker 2: Okay, not too bad. What about advanced features in C functions?
Speaker 2: Handling NL's, For.
Speaker 1: Instance, C requires explicit handling of nls. Unlike plpg sql,
Speaker 1: you use the pgr nl macro to check if a
Speaker 1: specific argument is NLL before you try to access its
Speaker 1: value with pg turner, and if you need to return
Speaker 1: NL you use pgr turnal forgetting. This as a common
Speaker 1: source of crashes, C is less forgiving.
Speaker 2: What about very attic arguments.
Speaker 1: Those arrive in your CE function as a Postgres school
Speaker 1: array type. You need to use specific C functions to
Speaker 1: deconstruct that array, getting its dimensions, checking its element type,
Speaker 1: and then iterating through the elements. A bit more work
Speaker 1: than in popg sqel.
Speaker 2: Memory management must be crucial in SEA leaks.
Speaker 1: Crashes absolutely vital. You must use postgrescols memory management functions
Speaker 1: primarily pallic and free, not standard Sea mallek and free.
Speaker 1: Palic allocates memory within postgres School's current memory context. The
Speaker 1: beauty is that memory allocated in shorter lived contexts like
Speaker 1: the protuple or perfunction call context, is automatically freed when
Speaker 1: that context is destroyed. This prevents many common memory leaks.
Speaker 1: Ah that's clever it is and always zero fyll allocated
Speaker 1: structures using memset to avoid subtle bugs from uninitialized memory.
Speaker 1: For air reporting, you don't just print. You use postgres
Speaker 1: School's air function dot airport is very structured. You specify
Speaker 1: at the severity level error warning notice, log et cetera,
Speaker 1: and soul state code, primary message detail hint. It handles
Speaker 1: localization and routes the message correctly to client or log
Speaker 1: error level aborts the current transaction. Importantly, unlike exceptions in
Speaker 1: plpg sql, air report messages are generally sent to the
Speaker 1: client immediately, not buffered until the end of the function.
Speaker 2: Okay, and how do these C functions run SQL queries
Speaker 2: back into.
Speaker 1: The database that's done using the SBI, the server programming interface.
Speaker 1: Inside your C function, you call SBI connect that you
Speaker 1: can execute queries using functions like spy ASEC or SPIKESIGI
Speaker 1: plan for prepared statements. You process the results, and critically,
Speaker 1: you must call SBI finish before your C function returns.
Speaker 1: SBI handers transaction context correctly, but understanding data visibility rules.
Speaker 1: What changes your function can see from other transactions or
Speaker 1: even earlier commands within the same transaction is important for
Speaker 1: complex scenarios, so.
Speaker 2: C offers the ultimate power what other advanced things become possible.
Speaker 1: You can do some really sophistic icated stuff. Write extremely
Speaker 1: high performance triggers in C for logging changes with minimal overhead.
Speaker 1: Register C functions to be called automatically on command or
Speaker 1: on rollback of a transaction. Great for cleanup or post
Speaker 1: commit actions. You can even interact directly with postcres schools
Speaker 1: shared memory and use lightweight locks for synchronization between different
Speaker 1: back end processes, enabling complex interprocess communication managed by the database. Honestly,
Speaker 1: the best way to learn advanced SEA extension techniques.
Speaker 2: Is let me guess.
Speaker 1: Use the source looke aha exactly. Look at the code
Speaker 1: for Postcrescol's own contrip modules. They cover a huge range
Speaker 1: of examples, from custom data types to procedural languages. It's
Speaker 1: all there, open source, ready to be studied, hashtag tag
Speaker 1: tax stealing your database with po proxy and polishing extensions.
Speaker 2: Okay, let's shift focus again. We've built these powerful functions,
Speaker 2: maybe even in c our single server is humming. But
Speaker 2: what happens when the load gets truly massive? How do
Speaker 2: you scale postcrescool horizontally?
Speaker 1: Right? So you've optimized your single server, added indexes, tune queries,
Speaker 1: maybe even us some of those c functions, but your
Speaker 1: database is still creaking under the load. Maybe CPU is
Speaker 1: pegged at eighty percent plus four hours a day. Traditional
Speaker 1: scaling options have limits buying bigger server Vertical scaling gets expensive.
Speaker 1: Fast master slave replication helps with read loads, but not
Speaker 1: write loads. Multi master replication adds complexity for really heavy workloads,
Speaker 1: especially right heavy ones. Horizontal partitioning or scharding becomes the
Speaker 1: logical step. You split your data across multiple independent postgress
Speaker 1: wile servers partitions.
Speaker 2: How do you manage that split? Doesn't that make the
Speaker 2: application logic way more complex?
Speaker 1: It can if you're not careful that's where proxy databases
Speaker 1: come in. The application talks to a proxy layer which
Speaker 1: knows how to route queries to the correct data partitions.
Speaker 1: This keeps the partitioning logic transparent to the application itself.
Speaker 2: Okay, let's ground this. You mentioned a chat application example earlier. Yah,
Speaker 2: how did optimization play out before even thinking about charting?
Speaker 1: Yeah, the example started with simple user info and message
Speaker 1: table and basic functions user log in, send message, et cetera. Initially,
Speaker 1: fetching just seven new messages took something like seven or
Speaker 1: and sixty milliseconds pretty slow. But then just by adding
Speaker 1: the right indexes on foreign keys and message time stamps,
Speaker 1: and crucially setting the fill factor on the message table
Speaker 1: to allow for hot t.
Speaker 2: Updates, stay shot TA updates, keep.
Speaker 1: Only touple updates. It's a postgres will optimization where if
Speaker 1: an update doesn't change any index columns, postcres can sometimes
Speaker 1: update the road directly in place on a data page
Speaker 1: without needing a new index entry. It massively reduces right
Speaker 1: overhead for certain update patterns. Just those changes brought the
Speaker 1: query time down from seven to sixty meters to under
Speaker 1: three meters, and then with further tuning down to about
Speaker 1: one point nine meters, a huge improvement without adding any servers.
Speaker 1: Always optimize first.
Speaker 2: Good point. But okay, say we do need to shard.
Speaker 2: How do we split the data?
Speaker 1: You need a partitioning key. If you have integer primary keys,
Speaker 1: a simple modul operation like user in three bitwise and
Speaker 1: d with three equivalent to modulo four for positive numbers
Speaker 1: can distribute users across four partitions. If your key is
Speaker 1: text like a username, you typically hash it first hashtext
Speaker 1: username and then apply the modular to the resulting integer
Speaker 1: hash for even distribution. And to manage the routing from
Speaker 1: the proxy to these partitions, postgrescool has a specialized tool,
Speaker 1: peel proxy.
Speaker 2: Another procedural language YEP, but this one isn't for general logic.
Speaker 1: It's specifically designed for database charding. You install like any
Speaker 1: other language, create extension pal proxy.
Speaker 2: How does it syntax work? How do you tell it
Speaker 2: where to send in the queries?
Speaker 1: It's quite declarative. Inside a peel proxy function, you use
Speaker 1: connect to specify the connection string for a remote database partition.
Speaker 1: You can group these connections into named clusters. Then the
Speaker 1: core is the r on clause you tell it which
Speaker 1: partitions to run the remote function on. This could be
Speaker 1: based on a routing function partition, part funk user, or
Speaker 1: you can specify any run on any single partition, maybe
Speaker 1: for reads that could be anywhere or all, run the
Speaker 1: function in parallel on all partitions. Perhaps for broadcasting a command.
Speaker 1: You also specify the remote function call using select or target.
Speaker 2: Okay, it seems logical. You mentioned parallel execution with run
Speaker 2: on all.
Speaker 1: Yeah. And there's also the spli statement, which is really
Speaker 1: powerful for distributing bulk operations. Imagine a function create users
Speaker 1: that takes a raise of usernames, passwords, and emails. You
Speaker 1: can write a pl proxy function that uses SPLT to
Speaker 1: take these arrays, figure out which partition each new user
Speaker 1: belongs to based on their username hash, and then efficiently
Speaker 1: send batches of users to the correct partitions in parallel.
Speaker 1: Very slick.
Speaker 2: How does pl proxy know about the cluster members?
Speaker 1: Two main ways. The older way uses special configuration functions
Speaker 1: like plproxy dot get cluster partitions that you define to
Speaker 1: return the connection strengths. The more modern and generally recommended
Speaker 1: way is to use sql MED, the standard SQL management
Speaker 1: of external data features. You create foreign data wrapper pull proxy,
Speaker 1: then create server for each partition and create user mapping
Speaker 1: to store credentials securely pl proxy, then queries these standard
Speaker 1: caps objects define the cluster configuration right.
Speaker 2: And migrating existing data.
Speaker 1: Usually involve setting up the schemas on the partitions, then
Speaker 1: carefully copying data row by row or chunk by chunk,
Speaker 1: using the partitioning key to determine the destination. It could
Speaker 1: be complex for live systems. It's careful planning.
Speaker 2: Okay, so we've staled out. Now let's talk about sharing
Speaker 2: all this cool stuff. We might have built custom functions, types, operators.
Speaker 2: Why package them as a postcrischool extension. What's the benefit
Speaker 2: over just shipping seql scripts?
Speaker 1: So extensions are a massive improvement over the bad old
Speaker 1: days of just providing dishquare yell files like the old
Speaker 1: contrip modules.
Speaker 2: Why were those bad?
Speaker 1: They had no concept of dependencies, no versioning, no clean installation,
Speaker 1: and crucially no upgrade path. If you install version one
Speaker 1: of a script and a version two came out that
Speaker 1: changed a function signature or a table definition, you often
Speaker 1: had to manually drop everything from version one, losing data,
Speaker 1: and then load version two. Painful yikes. And extension solves
Speaker 1: all this. It's a formal package containing all related sequl objects, functions, tables, views, types,
Speaker 1: operators can figuration parameters. To install it with single command
Speaker 1: create extension my extension. Postgrescool tracks the installed objects as
Speaker 1: belonging to that extension and the killer feature upgrades. If
Speaker 1: you release version two point oh you provide an extension
Speaker 1: one point two point oh dot s well update script.
Speaker 1: Users just run ulter extension my extension update and postcrescool
Speaker 1: applies the necessary changes cleanly, preserving data. Extensions can also
Speaker 1: declare dependencies on other extensions.
Speaker 2: That sounds much much better. How do you build one again?
Speaker 1: You typically use the PGXS build system with a simple makefile.
Speaker 1: It handles compiling any c code and packaging the SQL
Speaker 1: files correctly. Pseudo make install puts it where postgrescool can find.
Speaker 2: It and sharing it.
Speaker 1: You publish it to the Postgres School Extension Network PGXN
Speaker 1: PGXN dot org. There are tools like pgx and utails
Speaker 1: to help package your extension correctly. You need a metada
Speaker 1: adjacent file describing your extension, its licensed dependencies, and crucially
Speaker 1: it's version number. Using semantic versioning, this allows users to
Speaker 1: easily search for, download and install all extensions using clients
Speaker 1: like PGX and client. It really makes postgress will incredibly
Speaker 1: extensible and inviting for the open source community. Compared to
Speaker 1: other databases, this standardized, robust extension system is well pretty revolutionary.
Speaker 1: It makes it easy for anyone to extend postgreds to
Speaker 1: do almost anything they want. Hashtag taghtag outro eerro.
Speaker 2: Wow, we've covered a ton today. We really have taken
Speaker 2: a deep dive into POSTGRESSWIL server programming. It's clear it
Speaker 2: transforms the database from just you know, storage into a
Speaker 2: really dynamic application development framework. From the basics of peelpgseql,
Speaker 2: through the power and risks of Peel, Python and see
Speaker 2: and even scaling out horizontally with Peel proxy. The common
Speaker 2: thread is pushing logic closer to the data, and that
Speaker 2: leads to apps that are faster, definitely more secure, and
Speaker 2: easier to maintain.
Speaker 1: Absolutely, and we just sort of zoom out for a
Speaker 1: final thought. In a world that's increasingly driven by data,
Speaker 1: really understanding and using these service side capabilities isn't just
Speaker 1: another technical skill. It's more like paradigm shift, you know,
Speaker 1: a different way of thinking about where your critical logic
Speaker 1: should live, which maybe raises a question for you listening
Speaker 1: right now. What untapped potential, what hidden power are you
Speaker 1: overlooking in your current database solutions by treating them as
Speaker 1: just simple data buckets