Posts about Production Postgres

In an earlier post, I went into a lot of detail about unlogged tables. But tables are not the only thing to get the unlogged treatment - as of version 15 of Postgres, sequences can be unlogged as well! If you want to create your own, it's simply a matter of adding the UNLOGGED

Is your database ready for production?

You've been building your application for months, you've tested with beta users, you've gotten feedback and iterated. You've gone through your launch checklist, email beta users, publish the blog post, post to hacker news and hope the comments are friendly. But is your database ready for whatever may come on launch day or even 2 months in? Here's a handy checklist to make sure you're not caught flat footed.

• Backups❓
• High availability❓
• Logs properly configured❓
  • Archived and persisted❓
  • Logging slow queries❓
  • Auto explain❓

Integer overflow occurs when a computer program tries to store an integer but the value being stored exceeds the maximum value that can be represented by the data type being used to store it. We have helped a few Crunchy Data clients navigate this recently and wanted to write up some notes.

In Postgres, there are three integer types:

• smallint

While supporting customers at Crunchy Data, I sometimes see users add unlogged tables. In this post, I'm going to review some of the specifics about this.

Unlogged tables in Postgresql are a way of preventing specific tables from generating WAL (Write Ahead Log) information by adding the unlogged

The Postgres Write Ahead Log (WAL) is a functional component to the database. WAL makes a lot of key functionality possible, like Point-in-Time-Recovery backups

We spend a lot of time at Crunchy Data helping people dig into the performance of their Postgres. If you're setting up a new Postgres database or already running on in production there are a number of very basic steps you can take that will save your tail in the future when it comes to investigating performance. Here is your guide that'll take less than 5 minutes to get in place. Future you will thank you for doing this today.

1. Use pg_stat_statements to record queries

A question recently came up in the internal Crunchy Data slack channel: Does anyone actually use enums out in the wild?

Enums in Postgres

If you're unfamiliar with enums, they’re enumerated types, a static set of values in a database like days of the week or a shipping status. Enums are a powerful feature of Postgres that allows you to define a set of predefined values that can be assigned to a column. However, enums can have some limitations and drawbacks that make them less than ideal for certain scenarios.

Let's look at a practical example for enums. Here we create a table with order_status

The IN operator has to be in the top-3 SQL-specific operators that people learn, the close competitors are LIKE and BETWEEN. Thus, IN feels familiar when crafting SQL: you can use it with nested SQL statements, or create your own list. But, it does have limitations — what if you wanted to send a list of unknown length? You can’t really use SQL placeholders without also modifying the SQL statement.

What if I told you there was a less ubiquitous operator that offered all of the power and less of the drawbacks. In this article we'll talk about using = ANY(array)

With Crunchy Data's focus on Postgres we often talk with people that are looking to reduce their dependency on other databases and modernize their data stack. A big portion of that is migrating and building new apps on Postgres. One common tool in migration from an Oracle database is Orafce

We are excited to announce some big improvements for Crunchy Postgres for you available today. Crunchy Postgres provides everything you need for production ready Postgres in an opinionated distribution built fully on open source, upstream Postgres. Crunchy Postgres powered by our automation provides simple provisioning, backups, high availability, monitoring, and more. Existing Crunchy customers can download this from our Customer Access Portal