I head up the Cloud team at @citusdata. Citus extends Postgres to be a horizontally scalable distributed database. If you have a database, especially Postgres, that needs to scale beyond a single node (typically at 100GB and up) would love to chat with you to see if we can help.
Previously I spent a number of years @heroku, a Platform-as-a-service, which takes much of the overhead out of IT from keeping the lights on and lets developers focus on building features and adding value. The bulk of my time at Heroku I spent running product and marketing for Heroku Data.
In addition to writing about tech, startups, databases on here I:
- Curate Postgres Weekly, a weekly email newsletter with Postgres content
The Best Postgres Feature You’re Not Using – CTEs Aka WITH Clauses
The feature itself is known as CTEs or common table expressions, you may also here it referred to as
WITH
clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you’re doing...
--- Created by Craig Kerstiens 11/18/2013 --- Query highlights users that have over 50% of tasks on a given project --- Gives comma separated list of their tasks and the project --- Initial query to grab project title and tasks per user WITH users_tasks AS ( SELECT users.id as user_id, users.email, array_agg(tasks.name) as task_list, projects.title FROM users, tasks, project WHERE users.id = tasks.user_id projects.title = tasks.project_id GROUP BY users.email, projects.title ), --- Calculates the total tasks per each project total_tasks_per_project AS ( SELECT project_id, count(*) as task_count FROM tasks GROUP BY project_id ), --- Calculates the projects per each user tasks_per_project_per_user AS ( SELECT user_id, project_id, count(*) as task_count FROM tasks GROUP BY user_id, project_id ), --- Gets user ids that have over 50% of tasks assigned overloaded_users AS ( SELECT tasks_per_project_per_user.user_id, FROM tasks_per_project_per_user, total_tasks_per_project WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project / 2) ) SELECT email, task_list, title FROM users_tasks, overloaded_users WHERE users_tasks.user_id = overloaded_users.user_id
MassiveJS: Node Postgres data access
Massive is a PostgreSQL-specific data access tool. The goal of Massive is to make it easier for you to use PostgreSQL’s amazing features, not to hide them under a load of abstraction.
Massive is not an ORM. It loads your schema at runtime, creating an object instance that allows you to query tables, views, functions, and stored SQL files as if they were first-order methods:
Metrics Maven: Calculating a Moving Average in PostgreSQL
<span class="token keyword">SELECT</span> <span class="token number">ad</span><span class="token punctuation">.</span>date<span class="token punctuation">,</span> AVG<span class="token punctuation">(</span><span class="token number">ad</span><span class="token punctuation">.</span>downloads<span class="token punctuation">)</span> <span class="token keyword">OVER</span><span class="token punctuation">(</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">ad</span><span class="token punctuation">.</span>date <span class="token keyword">ROWS</span> <span class="token operator">BETWEEN</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span> <span class="token operator">AND</span> <span class="token keyword">CURRENT</span> <span class="token keyword">ROW</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> avg_downloads_ytd <span class="token keyword">FROM</span> app_downloads_by_date <span class="token number">ad</span> <span class="token punctuation">;</span>