Postgres, the 40-year-old open-source, the Toyota of databases, powers pretty much every major platform you know. It's the number one choice for most companies because it's battle-tested, fast, comes with great power, and with that comes great responsibility.

But this doesn't even begin to tell the Postgres story. It's not for nothing that it keeps getting the gold medal for most loved database after dethroning the Swedish database MySQL, sending it to Valhalla.

It supports custom objects, unstructured data, can replace Redis, Elasticsearch, RabbitMQ, MongoDB, Kafka, and even your REST API server.

For all of these and more, Postgres is beyond just a database. It's kind of like an operating system, a full-blown back-end stack of its own.

And that's not all. This extremely reliable beast of a database just got better a couple weeks back with a major upgrade, making it even more than twice as fast.

This was a lot. Let's rewind a little. Poss, aka Postgres or just PG, is an open-source database working in a client-server architecture.

Unlike my beloved SQLite, which I covered in the video above, Postgres focuses on performance and extensibility.

This means you can extend it, and my god, people have. We'll get there.

Born in Berkeley, California in 1985, a successor to the Ingres database, it won its author a Turing Award some 30 years later.

Michael Stonebraker, yes, that's a hard name to ignore, wanted to improve Ingres, which he had previously created and named it Post-Ingres, which morphed into what we all know today.

Its secret sauce lies with something the Postgres team calls multi-version concurrency control, which essentially means every transaction gets a data snapshot, allowing extremely efficient concurrency.

This has its drawbacks, especially with high write ops, as one can imagine. But luckily, Postgres is extremely tweakable, sometimes to an extent you didn't quite expect.

I decided someone needed to try to create a Postgres configuration optimized to process queries as slow as possible.

Why? I'm not sure. So today we're going in from setup through the basics, making it the king of databases, to extending it so that it becomes the everything stack, a system that's capable of every function, and the reason you may too build your next projects with or on Postgres.

Let's go. This one took a lot of research, so a shot of espresso is advised.

And with that in the veins, let's look at Postgres, the world's most advanced open-source relational database.

At least that's their claim. Who's there? Well, the team is comprised of a core group and many, many contributors, with no formal list of features.

We enjoy allowing devs to explore the topics of their own choosing.

I'd love to work there. Back to the team. Well, the last time they took a photo was 2006, and that was a long time ago.

Nevertheless, behold the Postgres team. So, back to our point.

Looking at the different dev groups in PG can tell you a lot about what's going on, but we're here to get our hands dirty.

Pick your favorite package manager. I'm on a Mac. So, brew install PostgreSQL and you've got psql ready to fire up the default database with the very original name Postgres.

By the way, running psql and a DB name is equivalent to running the full command psql with localhost the host, the default port 5432, my computer's username as a login, and the database name Postgres.

A few backslash commands for convenience: backslash L lists databases while D D T help us when we create some tables.

We'll get there in a sec. But before that, how about we upgrade our terminal life with some sweet syntax highlighting and autocompletion.

PG CLI is a super popular choice over PSQL and what I'll be using from now on. Now we're talking a list of options, colors, descriptions like it should be.

One tip, just like shell commands in Vim, PGCLI and psql take an exclamation mark after backslash, sending it to the shell.

So to clear the screen, we can run that and voilà, backslash q quits.

So, let's get to some interesting bits. One of PG's biggest selling points are composite types, which basically mean you can configure a special class with custom fields.

Why do that? Well, while you could obviously configure that in your code and then separate them into columns, consider what I just said.

First, there's no need to separate the columns, and a composite type takes one column but can still be sliced and diced later.

But beyond that, imagine the level of safety you're getting by forcing the structure of data coming into your type.

Let's see an example to make things clear.

I'll create a type called address which will hold two fields, a street and a city, both text fields.

Next, we create a table called customers which will hold a simple name but also a ship-to column of type address. Starting to get the idea now?

D will show us the schema of the table and again our address as a type of the field. Let's use it. When you're running a database in a home lab, storage is everything.

You need space for your databases, your backups, the whole deal.

That's why I'm so happy with the Ugreen DXP 4800 Plus, our sponsor for today's video, which they've kindly sent it over.

With support for up to 136 TB across its four bays, I finally freed up all the space I need, ensuring I never worry about my database or backups ever again.

And to keep those databases running smoothly, this NAS is powered by a 12th gen 5-core Intel processor.

This means it's snappy and responsive and is crucial for handling large queries and data operations.

Plus, with transfer speeds reaching up to 1,250 megabytes per second, moving massive database files or backups is incredibly fast.

Now, for those of you who follow the channel, you know how much I rely on Docker for deploying applications and expanding my HomeLab capabilities.

The Ugreen NAS integrates perfectly with Docker, making it even easier to deploy and manage stateful applications and other tools.

Setting it all up was surprisingly straightforward, even though it's a powerful machine. The OS is a breeze to navigate.

Installing the hard drive requires pretty much nothing. You just slide them in, and the system's step-by-step guidance makes configuration painless.

It's become my go-to for multi-device personal cloud. I can access all of my files from my phone, my laptop, anywhere.

So, if you're looking for a robust, fast, and secure storage solution for your own personal cloud, the Ugreen NAS series is now available on Amazon and the Ugreen official website.

Check out the links in the description to learn more. Now, back to the video. We'll insert a dear friend called Homer Simpson to write his address.

We're using the ROW function, and this is just for visibility. Holding the fields by order street and then city name.

Like I said, we can drop the ROW function and insert Marge. This time without it still works.

Selecting our users shows a composite struct under ship_to. Let's select a nested field. This is done using the parenthesis and a dot notation followed by the nested field.

name and city as if it was a column of its own. Naturally, you can add a WHERE clause and play with it as well. Picking any other field works exactly the same.

You can also pick both and create a snapshot result of the different fields separated to their matching columns, allowing for a wide range of SQL queries so that you don't lose any functionality by using these.

While nested fields are great, sometimes you'd rather store documents.

For that, you can use, I'm joking. You can store JSONs in Postgres.

Yeah, yeah, I know what the NoSQL fans are thinking. Yes, you don't get the exact same performance, but it's very much doable.

Not only doable, there's an actual type that parses inserted JSON and lets you query unstructured nested fields.

Check this out. This time, our table is called users. We'll give them a primary key, serial ID, a name that cannot be null, and a profile holding the type JSONB, which is a JSON PG takes and parses.

You can absolutely store standard JSONs, which will be stored as text. JSONB stored as a decomposed binary, which while being slower to process is indexed and queriable.

Here's our users table schema. Let's see how it works. We insert into users name and profile, and the value profile holds just a JSON text.

Note I added age as INT expecting the parser to recognize it alone. Now querying is a bit different.

You might think like me that using the previous trick with composite types and a dot notation will work.

But that's not the case, and we're told that this doesn't apply to JSONB.

So here's the trick. Select from users where profile and the operator dash and a double greater than sign.

This is not your standard arrow, and every app that supports Nerd Fonts makes it super hard to show. So for the real combo of characters, we'll use a browser to the rescue.

And this is what's expected. Now let's find users where the city key in profile is Springfield.

And there we have it, adding a bunch of family members to the Simpson home to show another operator.

The at sign lets you send a raw key value to find profile like so. If you only care about existence of a key, question mark takes any key and returns a list of entries that have it.

Remember, this is unstructured data, and we do have one family member that has interests instead of occupation.

As with NoSQL, there's no forcing a schema here, and we shouldn't expect it, which makes the question mark operator extremely useful.

By the way, you can also update unstructured data. It's usually a bad idea because it's risky and probably a sign you should be using another type.

Nevertheless, to do that, use the JSONB set function, taking the profile and the updated value casted to JSONB.

See, Homer just turned 20 at the end of the list.

So far, great features, but the next one is a secret sauce behind PG's performance and why it's so widely used.

Multi-version concurrency control, or MVCC in short, means that every transaction gets a snapshot of the data, making it extremely performant by preventing traffic jams with incoming transactions.

To see how it works, we'll use a common SQL pair: the BEGIN and COMMIT commands are starting and ending a query.

Usually, this is done for you behind the scenes. Just to make sure it works, we can hit BEGIN again, and PG tells us the transaction is already in progress in this session.

Now, in the other shell, we'll also begin a query and update Homer's last name to Gibson. Once run successfully, let's check the data again on the top split where Homer's last name is still intact.

However, below, since we haven't yet committed the change, the last name is different. Again, same database, same table.

Only when we create a transaction, we're using a snapshot of the data. This way, write doesn't block read operations.

What if we tried writing in both sessions at the same time? Well, we still have the bottom split uncommitted, which means there's a write operation going on.

When approving, you'll note that the session hangs. Nothing comes back. This is due to two write commands going on at the same time, which in this case is blocking.

If we commit the blocking write session below, we expect to see both released. We'll use a small T-Max trick to select both tables in two splits at the same time.

There's still a difference due to the fact that we've only committed the lower split with the Gimson update.

We'll commit again, select all again on both sessions and both back to Simpson.

Now, what if we're in the middle of an update transaction, not yet committed, and we decide to drop the changes.

Instead of committing, we can roll back to restore the data. This is a great way to ensure data manipulation on the fly without risk.

Use begin and commit or roll back to dry run the changes on a snapshot rather than the database itself and make sure it was successful before sending it through.

This explains performance and cool basic features. But one of PostgreSQL's biggest selling points is its extensibility. It can quite literally replace most of your backend stack from message queues and cache systems through text search, pub/sub, job queues, and chron jobs.

Some of these are so powerful there are PostgreSQL extensions which are known as a full-blown product on their own. First up, cache. Let's replace Redis with PostgreSQL, shall we?

To implement cache in PostgreSQL effectively, we will use unlogged tables. How are these different from normal tables?

Unlogged tables don't generate WAL (write-ahead log). If you're curious about WAL, I explained it on the SQLite video up here.

There's obviously a trade-off. Unlogged tables aren't crash safe without WAL record.

If a database server crashes, an unlogged table is automatically truncated. But with cache, we don't really expect proper persistence.

Postgres is NOT A DATABASE.

Join 1000+ subscribers getting one dev/sec/ops tip every Friday: signup.omerxx.com 

CLI, Tmux, Neovim , Dev, Sec, and Ops, everything that's fun!


Postgres is NOT A DATABASE.

Scripts