JSON v JSONB: A Dive into PostgreSQL
Friday, March 22, 2019JSON, or JavaScript Object Notation, is a lightweight data interchange format that is easy for both computers and humans to deal with. The rules are pretty simple:
- a JSON document is an
object
- an
object
consists of akey
and avalue
- a
key
is astring
- a
value
can be astring
,number
,boolean
,array
,object
ornull
So, when dealing with data, JSON has become fairly popular: it's the default document storage type for databases like CouchDB and MongoDB (though MongoDB technically uses a JSON-like format), and available in PostgreSQL as the JSON and JSONB data types.
Unit Testing in PostgreSQL with PLV8
Tuesday, March 14, 2017PostgreSQL is an amazing and extensible database, providing a ton of functionality. One of the best parts, in my opinion, is the ability to add additional programming languages to create stored procedures. This allows developers to move business logic deeper into the database itself. Unfortunately, this is often very hard to test in isolation.
The current state of testing in PostgreSQL is to run make installcheck
,
which runs a myriad of SQL commands living in files in the sql
directory,
with the results checked against a bunch more files living in the expected
directory. This works, but is often-times difficult to add into existing
test suites, which ultimately leaves holes in testing.
So, You Want to Build a Database - Part 2: The Heart of a Database, CRUD(S)
Wednesday, February 10, 2016All databases consist of a handful of operations: Create, Read, Update, Delete, and sometimes Scan. If this sounds a lot like reading and writing files on a disk, it is. At their core, databases need access to both data and metadata. An operating system provides this in a very basic form of a filesystem. As a simple approach to building a database, the reliance on a filesystem is key, as it provides a lot of metadata, and allows us to move forward with one less layer to deal with.
So, You Want to Build a Database (Part 1)
Sunday, January 31, 2016It's silly, really: rebuilding one of the things that has been considered a "solved problem" for a number of years. But, I've always felt that in order to understand a problem fully, one must understand the problems that those who came before us were trying to solve; to take a trip through their design decisions, and to understand why they chose the solutions that they did.
When Your Postgres Database and ORM Collide: Partitioning
Thursday, March 26, 2015An ORM (Object-Relational Mapping) has become a near-essential tool of software development. Whether you agree with the model or not, it has become ubiquitous. So, what happens when your ORM is so generic that it can't actually deal with the advanced features of your database? Problem: impedance mismatch. How bad can it be? Really bad, and the workarounds can be just as bad, if not worse.
Understanding Community: Cliques and Inclusiveness
Tuesday, June 3, 2014There comes a time in every community where members of that community must step back and take a look at how they appear to behave to those outside of them. The operative word is appear, and that's the part I want to focus on.
Building a MongoDB Clone in Postgres: Part 2
Friday, June 15, 2012In the first installment we dealt with creating collections and deep inspection of the JSON object once it was inserted. In this installment, we will be covering saving the data and building WHERE clauses from MongoDB queries in order to retrieve the data that we've written.
Building a MongoDB Clone in Postgres: Part 1
Monday, June 4, 2012I had a crazy thought. Don't all good ideas start with that phrase? Well, this one was suitably crazy: why not build my own version of MongoDB right on top of Postgres? It sounds a little far-fetched, but in all honesty it's pretty simple.
Evented Arduino (with Node.js)
Tuesday, May 29, 2012I love the Arduino; it's one of the most awesome technologies I've seen in quite a while: its heart is a gorgeous microcontroller with a plethora of inputs and outputs. I also love Node.js: its event driven model is very elegant and the fact that it's Javascript makes it pretty easy to develop in, even for the novice.
Servitude: CSS and JavaScript Injection Sugar
Tuesday, December 27, 2011Multiple requests suck. Let me rephrase that: multiple requests really suck. The more requests you make, the more connections that are made, the more data that is transfered, the longer it takes to for your application to become responsive. Servitude helps you reduce the number of requests made for CSS and JavaScript down to one and injects it into the DOM for you in the order you request -- faster requests mean more responsiveness; more responsiveness means a better application.
An Introduction to Bricks.js
Tuesday, October 4, 2011It's been a few months since I released Bricks.js, and I figured it was finally time to talk about it. Bricks is a fast, and extremely modular web application framework built on top of Node.js that works a little differently.
Faster (sometimes) Associative Arrays with Node.js
Friday, May 13, 2011I had been meaning to spend some time with Judy Arrays but I hadn't quite found a good reason to explore them to their full extent. While attending NodeConf I caught Marco Rogers' talk on C++ bindings for Node.js which gave me a fantastic reason to spend some time in the Judy world. A few days later I came up with this project: Judy Arrays in Node.js. Unfortunately, it's been about half of a decade since my last foray into C++, and at least a decade and a half before that via academia, so while all attempts have been made to adhere to best standards of Node.js add-on development, I cannot guarantee that everything is 100% correct and that there are no memory leaks.
Of Morality and Software Development
Thursday, January 27, 2011It's not every day that I find myself in a conundrum -- not just any conundrum, but a moral one. I rarely think of computers and software in the terms of morality: right and wrong, good and bad, but instead the expression of ideas, a beautiful manifestation of thought. This time things are different.
Let me back up a little bit. It was the height of the WikiLeaks release of the Iraq War Logs and I was outraged. Typically with outrage comes the desire to do something about it: this was no exception. I figured that I could do something about it, but I wasn't willing to risk my own hide by hosting a copy of the data on my own servers. Laws here in the US are fickle. I could be in the clear, but still end up on some watch list. I could legally be OK, but if my travel is suddenly impeded, that's a bad thing. I may be idealistic, but when it comes to the possibility of losing my livelihood or being put on some sort of watch list I tend to take a step back.
Of Pork, Freezers, Tweets, and Node.js
Monday, January 24, 2011It was summer and I was craving pork. Not just any pork, but Tails and Trotters pork -- fed with hazelnuts and absolutely delicious. I somehow convinced my lovely partner-in-crime to split the cost of half of a pig; there was only one problem, we had a freezer but its pedigree was entirely unknown. Rather than take a chance on losing a whole lot of yummy a plan was hatched: we'd bring the freezer into the 21st century (or at least the monitoring of it).
Node.js Date Utils
Sunday, January 9, 2011Introducing my first Github repository: node-date-utils. During redevelopment of my freezer daemon (more to come later), I found a couple of missing Date methods. This is an attempt to fill some of them in.
Stupid PostgreSQL Tricks: Writable Views
Thursday, August 19, 2010When needing two or more fairly disparate systems to work together seamlessly, having complete flexibility at the database level can be a blessing.
Take for instance the problem of a ten year old legacy system hosting millions of accounts, and an up to date content management system that needs complete access to that data as if it were its own. You can manage multiple systems with complicated triggers, methods for moving data around, expensive joins, funky stored procedures, hacks to the code, or you can simply use a writable view.
NoSQL meet SQL: The Schema-With Strike Back
Thursday, April 15, 2010SQL is everywhere. Believe it or not there are legacy relational "schema-with" databases filled with data all over the internet. Chances are even your own office has at least one SQL database lurking in a closet somewhere.
So, how do you leverage your existing "schema-with" databases and still be able to use the power of Map/Reduce? Introducing MR SQL: A Map/Reduce Front-End to SQL.
An Un-new Idea: Server-side JavaScript
Thursday, November 19, 2009My first few run-ins with JavaScript left me wanting. JavaScript was in its infancy, the platform was fairly immature, and I had my sites set on the server side.
Fast-forward a decade, and I found myself taking a job writing software for big-box retail product finders: the platform was Mozilla XUL, running on embedded Linux systems, the development environment a mix of JavaScript and C++ sprinkled liberally with JSUnit.
NoSQL meet SQL
Wednesday, November 11, 2009Often times, I don't get to get my hands dirty at work. Not being one to let myself atrophy, I keep my eyes out for new and exciting things to catch my fancy, and spend hours and hours writing new code: usually reinventing the wheel, often times poking and prodding, just trying to figure out what I'm going to do with what I find.
One of the projects that caught my eye a bit over a year ago was CouchDB, a RESTful document storage engine, that happens to have Map/Reduce support. Being the database freak that I am, I started thinking about all of the projects I've worked on in the past that could have been improved with a document model over pseudo-relational databases. So many came to mind, and I was excited about the flexibility of CouchDB; so useful for so many things, especially with strong data analysis abilities via map and reduce.
The Google Incident
Saturday, October 24, 2009Starting a new job is always difficult. Coming up to speed on essential projects, finding your niche, even remembering everyones names. Then there's the added challenge of starting a job at a partner of Google. Backing up a bit, my new job was approached by Google as both a data provider, and a partner in their new Social App section of iGoogle.
XHTML/CSS Playing Cards
Monday, February 2, 2009They say that a picture is worth a thousand words. Unfortunately, it's also worth 1000 bytes or more. In my quest to build the ultimate game of solitaire, I needed cards, 52 of them to be exact. That's a lot of bandwidth for something as simple as cards. I decided to try to fix this problem by making my cards using XHTML and CSS.