Security is a feature. Choose it!
Nearly every modern organization, large or small, has a significant amount of IT infrastructure reliant on 3rd parties. For the purpose of this post, 3rd parties includes anything you think of as "the cloud" (e.g. not my servers) or any service that you don't fully control on your hardware. Web hosting, email, social media, and file storage are all examples of services commonly outsourced.
I wrote previously about the responsibilities associated with managing data securely. A solid cybersecurity posture should require evaluating 3rd parties for proper security practices. This post provides a loose framework for evaluating a 3rd party's overall cybersecurity posture. This evaluation can then be used in your ongoing decision making to pick new vendors and reevaluate existing vendors. This post does not go in depth to any specific technology or vendor specific details, instead I intend to focus more on the culture of security.
Security as a feature
Security as a feature in this model has 3 overarching components:
- Problem solving
- Learn from mistakes
The quality of cybersecurity is an aggregate of these three components. An organization with good security practices is well prepared, knows how to react when something goes wrong, and they learn from their mistakes.
Plan for the worst, hope for the best.
The reality is, until more organizations make cybersecurity a higher priority, the never-ending stream of data breaches will continue.
PostGIS: Tame your spatial data
One of the main reasons why PostgreSQL became my personal favorite RDMS was its PostGIS extension, providing the most robust spatial database in the world. The goal of this post is to examine one common headache with spatial data: it can become painfully slow in a hurry because spatial data gets big in a hurry.
I've discussed this concept before with reducing the OSM roads layer as a quick example. Another common source of this headache are large polygon layers, such as the US county boundaries. This post goes into detail about using PostGIS to simplify large polygon objects, the effects this has on storage, performance and the accuracy of spatial analysis when using simplified geometries.
A numeric example
This post assumes basic knowledge of SQL syntax, PostgreSQL and PostGIS.
To begin this conversation, let's examine a bit about what we already know about storing numbers. Let's start with Pi (π).
Pi is a mathematical constant, roughly represented as
though the decimals go on and on into infinity. The following example shows how to create a table with a single row storing two
different representations of Pi. The first column,
pi_long, stores 31 decimals of Pi. The
pi_short column stores only two decimal
DROP TABLE IF EXISTS cool_numbers; CREATE TEMP TABLE cool_numbers AS SELECT 3.1415926535897932384626433832795::NUMERIC(32,31) AS pi_long, 3.14::NUMERIC(3,2) AS pi_short;
As you may expect, storing a number with more digits takes more disk space than storing a number with fewer digits. This is called
precision. The following
query uses the
pg_size_pretty() function to illustrate that the number with more decimal spaces takes up more disk space.
PostgreSQL: From Idea to Database - Table of Contents
PostgreSQL: From Idea to Database is a series of blog posts written to teach practical database design.
This series does not wax poetic about the theory of relational databases or normalized form. Instead we focus on practical examples that illustrates why the theory matters and applies.
Series in progress
Note: This series is currently a work in progress!
Table of contents
The following table on contents includes the links to the pages that are already completed along with placeholders for future topics. The future topics are likely to change in title, content and scope and I fully suspect the ToC will only grow as I start to work in the real content!
- Introduction to PostgreSQL: From Idea to Database series
- Design elements for database projects
- Project Management encourages successful projects
- PostgreSQL Crash Course
- PostgreSQL vs. MySQL: Why we use PostgreSQL
- Database Anti-patterns: Performance Killers
- Deploying PostgreSQL using Sqitch
- Database lifecycle
- Backup and Restore
- Databases and change over time
- PostgreSQL, JSON and PiWS
- PostgreSQL Functions - Data API
- PostgreSQL 10 Parallel Queries and Performance
- Testing PostgreSQL Unlogged Tables for Performance
- PostgreSQL Load Testing
Spatial data (PostGIS)
Get in touch
If you have questions or comments please Contact us or
PostgreSQL: From Idea to Database - Introduction
Databases are a critical component for nearly any modern application. The database is also a component that often is surrounded by confusion and apprehension.
This post is the first of the series PostgreSQL: From Idea to Database. The goal of this series to provide a guideline to how we approach database development at RustProof Labs. This includes covering both methodology and technology. This series provides working code examples, friendly explanations, and real-world database design scenarios. Hopefully this series helps explain and teach database design is a new, friendly format.
Project for reference
A major challenge with teaching database design is when you don't have a real project to use as an example. For this, I've chosen to use The PiWS project, an open source weather station designed around the Raspberry Pi. To read more about the PiWS, see my introductory post.
The PiWS project was chosen for a variety of reasons, but there are three main reasons:
- I know the code
- The project was developed in a way worth describing
- It's open source!
The third reason is a big benefit because the source code used in this series will come from the project's GitHub repository. This gives us a real project with real code to study how to approach the task of designing a database.
Throughout this series I provide examples of how the PiWS project was initially designed and how it has evolved.
Introducing the PiWS (Pi Weather Station)
The PiWS, short for The Pi Weather Station, is an open source, affordable weather station designed for everyone. This is not a traditional, commercial weather station that provides you a fixed set of sensors and collect data without providing you a way to keep your own data.
Instead, The PiWS is designed around open source hardware and software, provides you with a choice of sensors, and encourages long-term retention of your sensor data.
What does it do?
The PiWS collects data from a variety of analog and digital sensors to collect data from your local environment. When we talk about "environment", that could be indoor, outdoor, garden... or in the attic, basement, garage, or that one room upstairs that always seems cold.
The data from the sensors is collected in a PostgreSQL database on the Raspberry Pi, allowing you to store the data long term, analyze trends, or just satisfy your curiosity!
Why the PiWS?
The idea for the PiWS started around the time I started building TrackYourGarden (TYG) in 2015; it's another part of my desire to understand everything possible about our local micro-climate. I said at that time:
"It's hard to answer the questions of "When can I plant plant X?" living at 6,000 feet. We have a fairly short growing season; it has snowed on Mother's day two years in a row now, and it's not unusual to have at least one light snowfall around Halloween.... so naturally I figured the best thing to do was to gather some data (surprise!)"