Created: by Pradeep Gowda Updated: May 17, 2024 Tagged: postgresql · databases · sql

PostgreSQL is Enough

Learning PostgreSQL


Migrating and upgrading PostgreSQL

Tips and tricks

This is one of the things PG does a good job. Keeping notes.

  • Postgres Full Text Search vs the rest; HN; Oct 2022.
  • ParadeDB - Postgres for Search and Analytics; ParadeDB brings column-oriented storage and vectorized query execution to Postgres tables. Users can choose between row and column-oriented storage at table creation time; Column-oriented tables are stored as Parquet files and are managed by Delta Lake (Run analytical queries directly on top of S3, Azure, or GCP object stores without physical data movement.); Search by keyword with BM25 scoring, configurable tokenizers, and multi-language support; Search by semantic meaning with support for sparse and dense vectors; built in rust, AGPL.

Using PostgreSQL as a queue

So that’s what we built: a new queue backed by a single, and surprisingly simple, Postgres table. Our publishers write to it. Our consumers (workers) read from it. We maintain things like queue ordering by adding an ORDER BY clause in the query that consumers use to read from it (groundbreaking, we know). And we guarantee that jobs won’t be picked up by more than one worker through simple read/write row-level locks. The new system is actually kind of absurdly simple when you look at it. And that’s a good thing. It’s also behaved flawlessly so far.

It comes with several, significant upsides for our team. For one, the application state is no longer spread out over two systems (RabbitMQ storage and Postgres). It’s now centralized in our application database. This makes disaster recovery that much easier, and increases the resiliency of the overall system by removing moving pieces.

To make all of this run smoothly, we enqueue and dequeue thousands of jobs every day.

(the load isn’t all that big)

PostgreSQL and ML

  • Home – PostgresMLAn open source Postgres based platform for your interactive AI powered applications. Instantly scale your workloads with dedicated replicas or our serverless GPU accelerated cloud.

PostgreSQL on MacOS

Installing PostgreSQL on a Mac with Homebrew - Daily Dev Tips – includes tips on how to create a non-postgres user.

PostgreSQL on the cloud

  1. Supabase – “an open source Firebase alternative. Postgres database, Authentication, instant APIs, Edge Functions, Realtime subscriptions, Storage, and Vector embeddings”.
  2. Xata – “serverless data platform for PostgreSQL.”
  3. Tembo – “Tembo is the Postgres developer platform for building every data service.”