Paul Ramsey
Paul Ramsey
The pg_featureserv micro-service is a thin middleware that binds tables and functions in a PostgreSQL database to a JSON collections API, accessible over HTTP. Using the Crunchy Bridge container apps , I'm going to give a quick overview of how to set up a web based spatial heatmap from Postgres. The application uses PostgreSQL to store and search 2.2M geographic names in the USA. Type in the search box and the auto-fill form will find candidate words. Select a word, and the database will pe...
Read MoreChristopher Winslett
Christopher Winslett
Have you traced a sql statement back to the source application? Of course you have! Was it time consuming? Probably! SQL queries can hide in plain sight. If you've read through any of Crunchy's advice on query performance or healthy databases you know there's a goldmine of optimization waiting for you in your database. When using an ORM, the queries are obfuscated, and tracing a specific query to the application code can be challenging. Using Ruby as an example below (BTW, I <3 Ruby; I will...
Read MoreKeith Fiske
Keith Fiske
One of the most requested features by Crunchy Data customers using modern enterprise database environments is some form of data encryption. However, nailing down exactly what someone means when they say "We need our data encrypted" is often a challenge due to the actual requirements not being fully clarified or even understood. So, before anyone tries to implement database encryption it is critically important to understand what needs to be encrypted and what benefit is actually gained by the...
Read MoreChristopher Winslett
Christopher Winslett
For many developers, databases are basically magic. Like Penn & Teller, this blog post is set to break the illusion. Databases are just like any other code, they have algorithms and processes. These algorithms and processes are meant to improve performance, but can cause limitations if they are not expected. Disclaimer : it is okay to break the rules. Sometimes, you may choose to have slow database interactions, because you know they are rare events. Assuming a well-designed database infrastru...
Read MoreAndrew L'Ecuyer
Andrew L'Ecuyer
Recently, there has been a bit of a debate here at Crunchy Data around SQL editors. While some members of the Crunchy Team such as Elizabeth ( @e_g_christensen ) prefer pgAdmin 4, others such as Craig (@ craigkerstiens ) prefer using psql . And one the great things about the PostgreSQL ecosystem is that there is no right answer to this debate! Instead, you have choice and flexibility when it comes to finding and using the tools that meet your specific database development and/or management need...
Read MorePaul Ramsey
Paul Ramsey
A couple weeks ago, I came across a blog from Retool on their experience migrating a 4TB database. They put in place some good procedures and managed a successful migration, but the whole experience was complicated by the size of the database. The size of the database was the result of a couple of very large "logging" tables: an edit log and an audit log. The thing about log tables is, they don't change much. They are append-only by design. They are also queried fairly irregularly, and the qu...
Read MoreAndrew L'Ecuyer
Andrew L'Ecuyer
Whether upgrading PGO itself, or upgrading the PostgreSQL databases PGO manages, seamless upgrades should be a core feature for any cloud or Kubernetes-based database solution. As a result, one of the goals when we set out to build version five of PGO, the Postgres Operator from Crunchy Data, was to provide a seamless and user-friendly upgrade experience. Today we’re excited to introduce support for major version PostgreSQL upgrades in PGO v5.1. Using the new PGUpgrade API, you can now seamlessl...
Read MoreAndrew L'Ecuyer
Andrew L'Ecuyer
A colleague of mine recently tweeted the following, highlighting the challenges often associated with database upgrades: Last week had 3 calls before noon of people having difficulty with database upgrades and connection scaling. Two things that should be staples for a database provider and they had no idea until 6 months in when they tried to upgrade. As an industry we can build better. — Craig Kerstiens (@craigkerstiens) March 28, 2022 When I saw that tweet, I couldn’t help but appreciate the...
Read MoreElizabeth Christensen
Elizabeth Christensen
PostGIS is one of the most awesome extensions for PostgreSQL and can turn a relational database into a really powerful GIS ( Geographic Information System ). The PostGIS community is really great about documentation and training and this post is aimed at getting you some resources on how to get started with the major components of using PostGIS as a super beginner. I’ll help you get a sample dataset up, import a shape file, and get that all published to a web browser. PostGIS is a Postgres...
Read MoreCraig Kerstiens
Craig Kerstiens
Today, I'm excited to announce the public beta of Postgres Container Apps for your Postgres databases on Crunchy Bridge. With Postgres Container Apps you can, from directly inside Postgres with a simple function call, spin up a container that is running right alongside your Postgres database. Let's take an immediate look of how we can spin up a Postgres featureserv: It's as simple as that, and now I can connect directly to my pg_featureserv at: http://p.3fqs3ruv2vfq3malzedvtog4xq.db.postgresbri...
Read More