A Herd Of Rabbits Part 2: RabbitMQ Data Pipelines

2020 Mar31
R

abbitMQ is a powerful message borker allowing engineers to implement complex messaging topologies with relative ease. At the day job we used RabbitMQ as the backbone of our real time data infrastructure. In the previous post we setup a simple PostgreSQL trigger to send change capture messages to a RabbitMQ exchange. Conceptually, this is where we left off:

In this early stage, we basically have a fire-hose that we can selectively tap into. But we have no way to control the flow of data.

To recap a bit before we get too deep, we had a simple and manual way of handling real time operations. Effectively, we just baked all of the logic in the specific application code path.

Read More

A Herd Of Rabbits Part 1: Postgres Change Capture

2020 Mar27

P
ostgres is no longer "just a database." It has become a data integration and distribution platform. It has hooks for integrating custom data types, data formats, remote data store integration, remote index support, a rich extension ecosystem, cascading logical replication facilities. It is practically an application server. A proverbial swiss army knife to say the least.

At the day job, we use postgres as the primary database. As communication platform (chat) we do a good deal of real-time whiz-bangery. Being that we are an early stage start-up, we try to follow the keep it simple, stupid (k.i.s.s.) approach. Do the simplest thing possible until it isn't simple anymore.  The simple approach to real time was a

Read More

Fun With Postgres: Custom Constraints

2019 Sep29

P
ostgres comes with a rich set of tool to help developers maintain data integrity. Many of them come in the form of Constraints. Foreign Key, Not Null, Exclusion, and Check constraint allow developers to off load what would usually be a lot of application code to the database server with very litte effort.

Between the Foreign key, which verifies values in another table, and the Check constraint, which verifies values in a specific column, you can typically accomplish just about everything you need to do rather easily. The main problem the you'll run into is that these kinds of constraints are restricted to a single column on a table. Additionally they only apply the the current value in that

Read More

Flexible Schemas with PostgreSQL and Elasticsearch

2018 Dec26

R
elational Databases typically make use of a rigid schema - predefined tables containing typed columns allowing for a rich set of functionality that would otherwise be impossible. It is both a major strength as well as a major weakness. On one hand strong typing allows databases to expose a rich set of operators, functions and functionality for each of the types. For postgres, this usually presents itself in the form of column types sql syntax to interact with them. On the other hand it means that all of the data in the table is uniform and deviations or alterations are rather difficult to do.

At the day job, I am in the process of migrating a number of applications

Read More
filed under:  postgres elasticsearch json

Build JSON API Responses With Postgres CTEs

2017 Apr30
P

agination is a recurring problem that developers have to deal with when implementing data access layers for APIs. It can be particularly tricky with the more traditional RDMS like [MySQL](https://www.mysql.com/) or [Postgresql](https://www.postgresql.org/). For example, let's say we had an API endpoint that allowed consumers to search a data base of moves. We could search by title, director, starring actors, etc. Our data base has millions of movies, and we know we don't want to return all all the potential matches for every search request. We only want to return the top 25 or so records and indicate in the response that there are more results to query for:

{
  meta: {
    total: 12000

Read More
filed under:  sql postgres node.js