Latest posts from Paul Ramsey
Easy PostgreSQL Time Bins
Paul has some fast and easy tricks to show you how to get time series data into nice reportable data charts. Using functions like floor(), generate_series(), width_bucket(), and date_bin() you can bin your data in groups any way you like and retrieve charts in a flash.
7 min readPostgres Raster Query Basics
Paul takes us through how rasters are stored and queried in Postgres/PostGIS. He gives us the must have tips for dealing with rasters inside a database using digital elevation mode (DEM) examples.
6 min readSQL Tricks for More Effective CRUD
Level up your SQL skills with this quick tutorial of some simple and not-so-simple create, read, update, and delete (CRUD) statements.
7 min readPostgres Strings to Arrays and Back Again
Time to have some fun with strings and arrays. Learn how to separate strings into arrays, find array size, and turn comma separated lists into individual rows.
4 min readPercentage Calculations Using Postgres Window Functions
Learn how to do percentage calculations in one-pass. Sample data and queries for basic use of window functions.
3 min readPostgres Insider Terminology
If you've ever wondered - What is a tuple? What is a Postgres page? What's the difference between a record and a row? What is a relation? What's an array? What is TOAST? This post is for you! These are terms you hear all the time in the Postgres world. This post breaks down what these terms mean and how they are used.
7 min readMoving Objects and Geofencing with Postgres & PostGIS
A hands on demonstration for building real-time maps and geofences for moving objects. We will use the newly announced pg_enventserv for eventing along with pg_featureserv for a web API to build a fully functional web application.
5 min readBe Ready! Public schema changes in Postgres 15
Big changes are coming to the public schema settings in Postgres 15. There is no more global write privilege to public. Paul breaks down best practices on how to easily give permissions by role.
4 min readReal-time Database Events with pg_eventserv
Introducing our newest open-source project, pg_eventserv! pg_eventserv takes events generated by the PostgreSQL NOTIFY command and passes the payload along to waiting WebSockets clients.
5 min readGenerate Unlimited Crypto Using Postgres!
Just kidding. This is not crypto mining tutorial! It is a very practical description of using pgcrypto for encrypting and decrypting data inside your Postgres database.
9 min readRise of the Anti-Join
Find me all the things in set "A" that are not in set "B". Paul has some suggestions of when to use the anti-join pattern in queries with some impressive results.
5 min readPostgres Indexing: When Does BRIN Win?
Wondering about how to choose between BRIN and BTree indexes? Read about the best cases for BRIN indexes with some testing against BTree.
6 min readChoosing a PostgreSQL Number Format
Paul does a deep dive on number data types. Read about how to choose a number type that's most efficient for your data and storage requirements.
5 min readPostgres Indexes, Selectivity, and Statistics
Paul looks under the covers at Postgres query plans, indexes, filters, and statistics.
6 min readInstant Heatmap with pg_featureserv
Use your spatial data with this lightweight web server to make an instant heatmap! Paul sets up a demo using locations, name data, and our new container apps feature to run the pg_featureserv right inside the database.
5 min readParquet and Postgres in the Data Lake
Have too much static data? Paul has a idea of moving some of it to a datalake. He provides a walk-through of setting up Parquet with Postgres using the parquet_fdw.
8 min readPostGIS vs GPU: Performance and Spatial Joins
Paul takes a recent example of GPU-assisted spatial joins project to see how PostGIS stacks up in the same situation. He goes through the steps of joining a large record set of parking data with neighborhood data.
5 min readElevation Profiles and Flightlines with PostGIS
Paul walks through spatial indexes and queries to find a solution to a recent postgis-user query.
5 min read
Extracting and Substituting Text with Regular Expressions in PostgreSQL
Regular expressions? Exceptional expressions according to Paul! Some nice examples and tips for using regrex inside Postgres. Topics include true/false regex, text extraction, text substitutions, and using regex flags.
5 min read
