PostgreSQL

PostgreSQL is Enough; and Just Use Postgres for Everything.

Just use Postgres

Learning PostgreSQL

• DBA Roadmap: Learn to become a database administrator with PostgreSQL
• The Internals of PostgreSQL : Introduction

Internals

• How Postgres stores data on disk – this one’s a page turner | drew’s dev blog

Articles

• The growing pains of database architecture at Figma. Scaling to $2B business with one RDS instance.
• Working with PostgreSQL - Zerodha Tech Blog This post is in the context of the large, data heavy PostgreSQL instances that store historical transactional data and reports, the databases that power Console and its large scale number crunching and reporting. It talks about how we self-host, tune, and manage all our DB instances on bare EC2 instances.

Migrating and upgrading PostgreSQL

• How we migrated our PostgreSQL database with 11 seconds downtime - Government Digital Service; 2024
• xataio/pgroll — is an open source command-line tool that offers safe and reversible schema migrations for PostgreSQL by serving multiple schema versions simultaneously. It takes care of the complex migration operations to ensure that client applications continue working while the database schema is being updated. This includes ensuring changes are applied without locking the database, and that both old and new schema versions work simultaneously (even when breaking changes are being made!). This removes risks related to schema migrations, and greatly simplifies client application rollout, also allowing for instant rollbacks.. Read introductory blogpost on xata.io

Tips and tricks

• PostgreSQL - Don’t do this - from the pgwiki.
• Nine ways to shoot yourself in the foot with PostgreSQL. Discussion on HN and notes:
  • seq_page_cost = 1.0 & random_page_cost = 4.0 for rusty disks. the random_page_cost can be a bit higher than 1.0 for SSDs.
  • Configure Vacuum and maintenance_work_mem regularly
  • If you are deleting more than large no of rows (say 10,000) frequently, consider partitions
  • consider using md5/sha hashes instead of big string indices if the lookup is exact match
  • postgres scales well as a queue
  • read the postgresql manaul fully!
  • understand how EXPLAIN works
  • PGTune - calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration
  • Don’t Do This - PostgreSQL wiki
  • pgBadger  is a PostgreSQL log analyzer built for speed with fully detailed reports and professional rendering. It outperforms any other PostgreSQL log analyzer.
  • Postgres performance at any scale | PostgreSQL Tuning - pganalyze — has paid support at $149/mo for 1 db.

PostgreSQL and Full Text Search

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 / Kafka

• Choose Postgres queue technology :: Adriano Caloiaro’s personal blog

• SQL Maxis: Why We Ditched RabbitMQ And Replaced It With A Postgres 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)

---

Sequin “A Postgres alternative to Kafka. Get a feature-rich message stream with none of the operational overhead”. See Build your own SQS or Kafka with Postgres blog post.

PostgreSQL and ML

• Home – PostgresML — An 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”.
   1. postgres.new: In-browser Postgres with an AI interface by Supabase; HN
2. Xata — “serverless data platform for PostgreSQL.”
3. Tembo — “Tembo is the Postgres developer platform for building every data service.”

PostgreSQL-ish databases

Apart from the ones above (“on the cloud”),

• CedarDB, based on the work on Umbra DB. See announcement blog post - An ode to PostgreSQL, and why it is still time to start over | CedarDB - The All-In-One-Database