Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
Over and over when I look at applications for performance, the lesson I learn and re-learn is, do more things right inside the database . Create, read, update, delete! All the things you do to a table or collection of tables to work with your ever-changing data. Most CRUD examples, and most CRUD thinking, tend to focus on one table at a time. That's easy to understand. It's also unrealistic. Even the simplest application will be working with several interlinked normalized tables. Here's our wor...
Read MorePaul Ramsey
Paul Ramsey
One of my favourite (in an ironic sense) data formats is the "CSV in the CSV", a CSV file in which one or more of the column is itself structured as CSV. Putting CSV-formatted columns in your CSV file is a low tech approach to shipping a multi-table relational data structure in a single file. The file can be read by anything that can read CSV (which is everything?) and ships around the related data in a very readable form. But how can we interact with that extra data? If you want to try this blo...
Read MorePaul Ramsey
Paul Ramsey
Back when I first learned SQL, calculating percentages over a set of individual contributions was an ungainly business: • First calculate the denominator of the percentage, • Then join that denominator back to the original table to calculate the percentage. First calculate the denominator of the percentage, Then join that denominator back to the original table to calculate the percentage. This requires two passes of the table: once for the denominator and once for the percentage. For BI queries...
Read MorePaul Ramsey
Paul Ramsey
Last week Craig Kerstiens published a great introduction to Postgres terminology , covering some of the basics you might run into when just getting started. The funny thing about jargon is how quickly we get used to it, and forget we are even using it. It becomes part of the secret handshake, the way we signal to other members of our tribe that we're part of the group. When I first started going to Postgres conferences and listening to talks by Postgres core developers I suddenly found myself...
Read MorePaul Ramsey
Paul Ramsey
In a recent post , we introduced pg_eventserv and the real-time web notifications from database actions. In this post, we will dive into a practical use case: displaying state, calculating events, and tracking historical location for a set of moving objects . This demonstration uses pg_eventserv for eventing, and pg_featureserv for external web API, and OpenLayers as the map API, to build a small example application that shows off the common features of moving objects systems. Try it...
Read MorePaul Ramsey
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: NoooO! Why can I not write a table into public? For developers and experimenters, one of the long-time joys of PostgreSQL has been the free-and-easy security policy that PostgreSQL has shipped with for the "public" schema. • "public" is in the default , so you can always find things in it; and, • any user can cr...
Read MorePaul Ramsey
Paul Ramsey
By combining triggers, the PostgreSQL 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 converts events from the PostgreSQL event bus to standard WebSockets messages that any web client can handle. For multi-user real-time applications (like a fleet tracker, or auction system, for example), this setup can be a boon! The database is the central source-of-truth, and all ap...
Read MorePaul Ramsey
Paul Ramsey
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. There's already a lot of encryption in Crunchy Bridge ! First, your data are "encrypted at rest". That means that the "volumes" (what in an earlier era would be called the disk drives) your data is saved to are encrypted. Also all the backup files generated by your server are encrypted. In practice, this means...
Read MorePaul Ramsey
Paul Ramsey
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. The non-spatial setup starts with two tables with the numbers 1 to 1,000,000 in them, then deletes two records from one of the tables. The spatial setup is a 2M record table of geographic names, and a 3K rec...
Read MorePaul Ramsey
Paul Ramsey
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 l...
Read More