Posts under postgres

Tree structure query with PostgreSQL

When dealing with hierarchical data, we usually use recursion for constructing the tree structure. It would be a performance problem if you query the tree node data from the database every time the recursion happens. Also, if you work with languages which are heavily based on callback like Javascript, you will find it is very […]

Embracing SQL In Postgres

Having the ability to work SQL in your favorite database engine is a life-long, good skill to have. In this post I’ll show you some of the favorite things I use in Postgres.

Using Custom Types in Postgres

Using Types For Fun and Profit In my last post about pulling documents from queries I showed an interesting way to return a result set using row_to_json to crunch down 1-many records into a JSON array. This works pretty well and is really fast – but it’s not exactly pretty: create or replace function get_member(member_id […]

Pulling Documents From a Relational Query in Postgres

JSON, JSONB and Postgres 9.4 Upfront: Postgres isn’t the only database system in town to formally support JSON; SQL Server, MySQL, and Oracle do as well. Really – JSON is just text so there’s no miracle when it comes to “supporting” it in a relational system. The fun comes with how a system supports JSON. […]

A Better ID Generator For PostgreSQL

Postgres Global IDs Can Be More Than GUIDs When developers think about a globally-unique identifier, they usually think of UUIDs (or GUIDs) and will then create a table with a GUID as a primary key. This is problematic if your system grows or your writes/second increase. The first reason is that GUIDs are large string blobs […]

PostgreSQL Rising Some buzz going around the web today about Most people don't understand why it's interesting – so here's a quick explanation. Postgres can be configured with a lot of interesting options – the ability to run Geospatial indexing (PostGIS), creating functions with Ruby and Javascript, and extending the query engine to go up […]

Something Borrowed, Something New

30 Seconds. Just 30 Seconds. I always say – if you can't give me the pitch in a single sentence, in 30 seconds, your idea isn't worth what you think it is. So here's what I got for you:> PostGres will blow your mind; given its Enterprise features (Compression, partitioning, Full Text indexing, etc) , […]

How To Backup Your Postgres DB To Amazon Nightly

Hello Postgres You've setup Postgres and your new awesome website is banging away at one of the top database engines out there – hurrah! But that's not the end of the story – you need to back up your data before you do anything else! So let's get to it… There are two ways to […]