Latest posts
Devious SQL: Dynamic DDL in PostgreSQL
Data Definition Language (DDL) in SQL itself is notoriously non-dynamic but this next post on Devious SQL provides some examples of Dynamic DDL Postgres syntax for utility queries where you need to review the current state of the database.
6 min read
Postgres 14: It's The Little Things
Today I wanted to call some extra attention to those little things, the ones that don't get the spotlight, but simply make a developer's life better.
5 min read
Querying Spatial Data with PostGIS and ogr_fdw
The cool thing about foreign data wrappers is that they're an alternative to needing to have everything in the same data store. With spatial data being stored and shared in so many different formats, imagine being able to abstract that conversion away and just focus on analysis. Read on for a couple of quick demos.
7 min read
Devious SQL: Message Queuing Using Native PostgreSQL
An interesting question came up on the postgresql IRC channel about how to use native PostgreSQL features to handle some sort of queuing behavior. While the specific usage was related to handling serialization of specific events to some external broker, the specifics are less important than the overall structure.
8 min read
Insert-Only Tables and Autovacuum Issues Prior to PostgreSQL 13
If you have insert-only tables in a version of PostgreSQL earlier that 13, you could benefit from running a regularly scheduled VACUUM.
5 min read
Fast, Flexible Summaries with Aggregate Filters and Windows
PostgreSQL can provide high performance summaries over multi-million record tables, and supports some great SQL sugar to make it concise and readable, in particular aggregate filtering, a feature unique to PostgreSQL and SQLite.
3 min read
Waiting for PostGIS 3.2: Secure Cloud Raster Access
Raster data access from the spatial database is an important feature, and the coming release of PostGIS will make remote access more practical, by allowing access to private cloud storage.
6 min read
Understanding Foreign Data Wrappers in Postgres and postgres_fdw
Foreign data wrappers can simplify data querying and analysis when you need data from disparate sources.
5 min read
Using Cert Manager to Deploy TLS for Postgres on Kubernetes
Let's look at how we can use cert-manager on Kubernetes to manage TLS for Postgres clusters.
6 min read
Postgres Full-Text Search: A Search Engine in a Database
With Postgres, you don't need to immediately look farther than your own database management system for a full-text search solution. If you haven't yet given Postgres' built-in full-text search a try, read on for a simple intro.
10 min read
Waiting for PostGIS 3.2: ST_Contour and ST_SetZ
One theme of the 3.2 release is new analytical functionality in the raster module, and access to cloud-based rasters via the "out-db" option for rasters. Let's explore two new functions and exercise cloud raster support at the same time.
6 min read
Using PostGIS and pg_featureserv with QGIS
Crunchy Data has developed a suite of spatial web services that work natively with PostGIS to expose your data to the web, using industry-standard protocols.
5 min read
Waiting for PostGIS 3.2: ST_MakeValid
One of the less visible improvements coming in PostGIS 3.2 (via the GEOS 3.10 release) is a new algorithm for repairing invalid polygons and multipolygons.
4 min read
Cut Out the Middle Tier: Generating JSON Directly from Postgres
PostgreSQL has built-in JSON generators that can be used to create structured JSON output right in the database, upping performance and radically simplifying web tiers.
4 min read
Getting Started with PGO, Postgres Operator 5.0
An important design goal for PGO 5.0 was to make it as easy as possible to run production-ready Postgres with the features that one expects. Let's see what it takes to run cloud native Postgres that is ready for production.
4 min read
The Next Generation of Kubernetes Native Postgres
We're excited to announce the release of PGO 5.0, the open source Postgres Operator from Crunchy Data. While I'm very excited for you to try out PGO 5.0 and provide feedback, I also want to provide some background on this release.
6 min read
Logging Tips for Postgres, Featuring Your Slow Queries
Today we're going to take a look at a useful setting for your Postgres logs to help identify performance issues. We'll take a walk through integrating a third-party logging service such as LogDNA with Crunchy Bridge PostgreSQL and setting up logging so you're ready to start monitoring and watching for performance issues.
8 min read
Fun with pg_checksums
Data checksums are a great feature in PostgreSQL. They are used to detect any corruption of the data that Postgres stores on disk. Every system we develop at Crunchy Data has this feature enabled by default. It's not only Postgres itself that can make use of these checksums.
10 min read
PostgreSQL on Linux: Counting Committed Memory
By default Linux uses a controversial (for databases) memory extension feature called Overcommit. How that interacts with PostgreSQL is covered in the Managing Kernel Resources section of the PG manual.
6 min read
Better Range Types in Postgres 14: Turning 100 Lines of SQL Into 3
Almost a decade after range types were introduced, Postgres 14 makes it easier to write "boring SQL" for range data. Meet the "multirange" data type.
5 min read
