Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn’t know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I’m capturing some of the responses here along with some resources many of the features.
Read on

I spend a lot of time with dev tool and data companies. I think I’ve more or less banished myself to a life of working in the space, no consumer products for me. In that world a common topic that comes up amongst marketing teams is how do I get my team to contribute to content? Sometimes the person already has an idea of how they want the team to jump onto the bandwagon of their plan, sometimes they’re entirely open minded. I won’t get into pros and cons of various approaches here, rather after sharing some of my approaches in one on one settings I thought it could be useful to share more broadly here.
Read on

In my career I’ve had to conduct a number of retrospectives. Ahead of them it already sucked, there was an outage at some point, customers were impacted, and it was our fault. Never was it solely on our underlying infrastructure provider (AWS or Heroku), nope the blame was on us and we’d failed in some way. And as soon as the incident was resolved, it wasn’t time to go home and decompress with a beer, it was time start the process of a retrospective.

Finding the motivation to get right back to work is tough, but not losing time is important. There is probably a lot out there on retrospectives, and in general I was well rehearsed at them. But since I’d not performed a large scale one in a few years I found myself rusty and thought it’d be good to share some of our process.
Read on

Postgres is an interesting open source project. It’s truly one of a kind, it has its own license to prove it as opposed to falling under something like Apache or GPL. The Postgres community structure is something that is pretty well defined if you’re involved in the community, but to those outside it’s likely a little less clear. In case you’re curious to learn more about the community here’s a rundown of a few various aspects of it:
Read on

I’ve always felt an affinity for you in my 9 years of working with you. I know others have known you longer, but that doesn’t mean they love you more. Years ago when others complained about your rigidness or that you weren’t as accommodating as others I found solace in your steadfast values:

Don’t lose data

Adhere to standards

Move forward with a balancing act between new fads of the day while still continuously improving

You’ve been there and seen it all. Years ago you were being disrupted by XML databases. As companies made heavy investment into what such a document database would do for their organization you proceeded to “simply” add a datatype that accomplished the same and brought your years of progress along with it.

In the early years you had the standard format of index b-tree that most database engines leveraged. Then quietly but confidently you started adding more. Then came K-nearest neighbor, generalized inverted indexes (GIN), and generalized search-tree (GiST), only to be followed by space partitioned GiST and block range indexes (BRIN). Now the only question is which do I use?

All the while there was this other camp using for something that felt cool but outside my world: GIS. GIS, geographical information systems, I thought was something only civil engineers used. Then GPS came along, then the iPhone and location based devices came along and suddenly I wanted to find out the nearest path to my Peets, or manage geographical region for my grocery delivery service. PostGIS had been there all along building up this powerful feature set, sadly to this day I still mostly marvel from the sideline at this whole other feature set I long to take advantage of… one day… one day.

A little over 5 years ago I fell in love with your fastly improving analytical capabilities. No you weren’t an MPP system yet, but here came window functions and CTEs, then I almost understood recursive CTEs (still working on that one). I can iterate over data in a recursive fashion without PL/PgSQL? Yes please! I only want to use it more.

And then five years ago, document stores start taking over the world. I feel like I’ve seen this story before, wasn’t XML going to change the internet? Enter JSON, the JSON datatype, and JSONB. Wow, this is really nice to mix relational, document storage, join against things. I suddenly don’t get why more don’t take this flexible approach to building on a good foundation and layering on the refinements.

Extensions! Where have you been all my life? There’s Citus, and HyperLogLog, and ZomboDB, with each I can add functionality to Postgres without it being limited to the standard release, they can be in C or not. Wait, all along so much has been built on this foundation? PostGIS, full-text search, hstore? I like all those things, why didn’t you tell me all along about this foundation? Postgres, I like what I’m seeing how you’re allowing others to do more without having it be in the core of Postgres. This extension stuff is really kinda cool that it’s Postgres and then some, kinda like C and then ++, wait nevermind scratch that analogy.

Sorry, I’ve rambled a bit. You’re a little over twenty years old now. I’ve known you for nearly ten of those years so I know there’s so much about your background I don’t know, I hope we get to spend the time together to share it all. This 10 release is really an exciting one to me. We’ve spent all this time together and I feel like each passing year the bond grows fonder.

Now you’ve brought me better parallelism so I can further utilize my system resources. I now have partitioning. Thank you! I don’t have to roll my own hacks to help age out old data for my time series database. Logical replication will make so many other things possible, such as more online upgrades and integration with other systems.

Postgres, I just want to say thank you for the past ten years together. Thank you for all you’ve done and for all you’ll continue to do in the future.

Managing connections in Postgres is a topic that seems to come up several times a week in conversations. I’ve written some about scaling your connections and the right approach when you truly need a high level of connections, which is to use a connection pooler like pgBouncer. But what do you do before that point and how can you better track what is going on with your connections in Postgres?
Read on

As your database grows and scales there are some operations that you need to take more care of than you did when you were just starting. When working with your application in your dev environment you may not be fully aware of the cost of some operations until you run them against production. And at some point most of us have been guilty of it, running some migration that starts at 5 minutes, then 15 minutes in it’s still running, and suddenly production traffic is impacted.

There are two operations that tend to happen quite frequently, each with some straightforward approaches to mitigate having any noticable amount of downtime. Let’s look at each of the operations, how they work and then how you can approach them in a safer way.
Read on

It’s not a very disputed topic that you should backup your database, and further test your backups. What is a little less discussed, at least for Postgres, is the types of backups that exist. Within Postgres there are two forms of backups and understanding them is a useful foundation for anyone working with Postgres. The two backup types are

Physical: which consist of the actual bytes on disk,

Logical: which is a more portable format.

Let’s dig into each a bit more so you can better assess which makes sense for you.
Read on

This year Postgres open and PGConf SV have combined to great a bigger and better conference right in downtown San Francisco. I’m obviously biased as I’m one of the co-chairs, and I know every conference organizer says picking the talks was hard, but I’m especially excited for the line-up this year. The hard part for me is going to be which talks do I miss out on because I’m sitting in the other session that’s ongoing. You can see the full list of talk and tutorial sessions, but I thought it’d be fun to do a rundown of some of my favorites.
Read on

A massive amount of reporting queries, whether really intensive data analysis, or just basic insights into your business involving looking at data over a certain time period. Postgres has really rich support for dealing with time out of the box, something that’s often very underweighted when dealing with a database. Sure, if you have a time-series database it’s implied, but even then how flexible and friendly is it from a query perspective? With Postgres there’s a lot of key items available to you, let’s dig in at the things that make your life easier when querying.
Read on