Latest posts from Paul Ramsey

The end is nigh! PostgreSQL has substantially tightened restrictions on the use of the "public" schema.

Here, a standard login user (not superuser) tries to make a table, as one does:

user=> CREATE TABLE mydata (id integer);

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE mydata (id integer);

By combining triggers, the PostgreSQL LISTEN/NOTIFY system, and the pg_eventserv service, you can build a real-time application that keeps your web application state perfectly in sync with your database state.

pg_eventserv

Ha ha, made you look!

This post is not a crazy scam (you be the judge) but just a practical description of using cryptographical algorithms to encrypt and decrypt data inside PostgreSQL.

Encryption in Crunchy Bridge

There's already a lot of encryption in Crunchy Bridge

Find me all the things in set "A" that are not in set "B".

This is a pretty common query pattern, and it occurs in both non-spatial and spatial situations. As usual, there are multiple ways to express this query in SQL, but only a couple queries will result in the best possible performance.

Setup

The PostgreSQL BRIN index is a specialized index for (as the documentation says) "handling very large tables in which certain columns have some natural correlation with their physical location within the table".

For data of that sort, BRIN indexes offer extremely low insert costs (good for high velocity data) and extremely small index sizes (good for high volume data).

But what data has this "natural correlation"?

Most frequently, data with a timestamp that is continuously adding new rows.

• A log table
• A table of GPS track points
• A table of IoT sensor measurements

It should be the easiest thing in the world: you are modeling your data and you need a column for some numbers, what type do you use?

PostgreSQL offers a lot of different number types, and they all have advantages and limitations. You want the number type that is going to:

• Store your data using the smallest amount of space
• Represent your data with the smallest amount of error
• Manipulate your data using the correct logic

The sheer cleverness of relational databases is often discounted because we so frequently use them for very simple data management tasks.

Serialize an object into a row, store with unique key. yawwwn

Search for unique key, deserialize row into an object. yawwwwwwn

The real power of relational databases is juggling "relations" (aka tables) in large numbers and figuring out on-the-fly the most effective way to filter out rows and find an answer.

PostgreSQL has an undeniably clever query planning system that auto-tunes based on the data in the system. It samples tables to gain statistics about the distribution of data, and uses those statistics to choose the order of joins and filters applied to the data for the most efficient query execution.

Even more amazing, the query planning system is modular enough to integrate user-defined data types, like the geometry

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