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.