SQL

TIL (Today I Learned) - short notes/articles from my day to day learning Introduction Recently I was working with a legacy pub sub architecture with MongoDB which was utilizing mongo change streams for pushing real time data changes. Now I had to find something similar that could be incorporated in PostgreSQL, a quick google search will show you that PostgreSQL or MySQL doesn’t have something exactly similar to mongo streams. Now, something like this can also be implemented on application level but in that architecture, the stream is centralized to our application and not very efficient if we are talking in terms of accessing it from multiple different services....

Problem How to get index scan on indexed timestamp column in postgres alongside a predicate? Solution Ensure usage of functions which are tagged as STABLE or IMMUTATBLE in query predicate and also explicitly specify the predicate in case of partial index Case Study Consider the following table named User. (Can directly jump to explanation section) User + id (INT) + username (TEXT) + created_at (TIMESTAMPTZ) At some point the table saw exponential growth in inserts so it was decided to index created_at column but since only data from frequent time series was required (let’s say last one month), it made sense to just partially index the table....