Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Craig Kerstiens
Craig Kerstiens
At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive. It was "Why have composite types? Why would you use them?". I threw in an answer a few others did as well, but collectively we didn't have anything definitive but all these seemed like valid cases. Composite types ) are a custom type that i...
Read MoreKat Batuigas
Kat Batuigas
This post is the second in a two-part series -- read the first here: Going Back to Basics with PostgreSQL Data Types . In my last post, I shared some interesting (and at times surprising) things that I learned while digging into data types in PostgreSQL. Data types like numeric, integer, date, and char/varchar exist in every other relational database system since the need to work with such data is pretty much a given. The implementation may vary somewhat between systems, but generally there are...
Read MoreKat Batuigas
Kat Batuigas
When I first started to learn how to code, I was introduced to the concept of data types: a 6 is not the same as "6", because the former is numeric (typically an integer type, with some variations in terminology based on the language) and the latter a string; "true" is not necessarily the same as true, because true can be a Boolean value in some languages. Underneath the code, these pieces of data are really just a combination of ones and zeros, but declaring their types allows them to play a pa...
Read MoreSteve Pousty
Steve Pousty
Greetings friends of Crunchy Data, it is my pleasure to announce the initial release of our application developer portal . An awesome team has been working behind the scenes to bring together this nice little website to help application developers find all their Postgres needs in one place. Our goal is to become a single-stop resource for application developers looking to work with PostgreSQL. We have released three main parts to the site that form the foundation for future growth. Let’s go ove...
Read MoreMark Lane
Mark Lane
Let's say you needed to document all of the tables in your PostgreSQL database. You wanted the output of the psql meta-command for all of the tables so you could put it in a shared documentation area. However, there were a lot of tables and you did not want to have to type all the commands that you needed. Before I explain how I can help, we will need to set up a simple database and provide some background on the psql meta-command. Open a connection to a Postgres instance with the psql clien...
Read MoreJonathan S. Katz
Jonathan S. Katz
Advanced PostgreSQL high-availability and disaster recovery strategies designed to maximize uptime involve distributing database clusters across multiple data centers. While on the surface, this may seem intuitive (e.g. eliminate outages due to a single-point-of-failure), there are many nuances to consider, including avoiding the dreaded split-brain problem. To set up a PostgreSQL deployment that spans across data centers (or regions, or Kubernetes clusters), and to mitigate the risk of a...
Read MoreJonathan S. Katz
Jonathan S. Katz
When I first started working with PostgreSQL and containers , one of the first items presented to me was a recipe to get PostgreSQL 10 setup with pgAdmin 4 using Docker, which was over two years ago. Quite a bit has changed in the ecosystem since then, including PostgreSQL itself! As such, it felt like it was time to update the recipe and work through some new examples. Much like before, there's a " tl;dr " if you do not wish to read through my ramblings, but a more in depth explanation there...
Read MoreJonathan S. Katz
Jonathan S. Katz
Tools like the PostgreSQL Operator make it easy to get PostgreSQL up and running on Kubernetes , but what about actually accessing your Postgres databases? pgAdmin 4 is a popular choice for managing PostgreSQL workloads as it's both accessible from a web browser and provides an intuitive interface for daily PostgreSQL tasks such as running queries, adding new tables, and much more. I've previously shown how to easily deploy pgAdmin 4 with PostgreSQL on Docker , but what about on Kubernete...
Read MorePaul Ramsey
Paul Ramsey
One of the least-appreciated PostgreSQL extensions is the powerful PgRouting extension, which allows routing on dynamically generated graphs. Because it's often used for geographic routing (and is a part of Crunchy Spatial ), PgRouting depends on the PostGIS extension, but there's no reason it could not be used for graph analysis for any number of other graph problems. Here's a simple and practical example of backing a web map with PgRouting . Thanks to the magic of packaging, PgRouting is...
Read MoreSteve Pousty
Steve Pousty
Let's imagine a scenario in which you are dealing with JSON in your application and you want to store it in your database. You let out a heavy sigh and think, "I guess I am going to have to add something besides my favorite DB (Postgres) to my architecture . I wish I could just keep using PostgreSQL." You start thinking that instead of one problem, now you have many. You're probably going to have to learn a new data query syntax, data creation statement, install new software, and worst of all f...
Read More