Bookmarks

Hey Everyone! I've been working on something I want to share with you. As you know, I've started relying on AI assistants to do most of my coding, but I...

Passle blog

Pull request viewer that color-codes every diff line/token by how much human attention it probably needs

We can take a value set in an HTML attribute and use it in CSS, even extracting each individual digit in order to animate separately.

Every data platform now starts at Postgres — because that's where the data starts

Taking ‘just use Postgres’ to its logical endpoint: git push to deploy into a single Postgres process.

Instead of using git as a database, what if you used a database as a git?

How ParadeDB uses principles from search engines to optimize Postgres' Top K performance.

One of the great things about PostgreSQL's jsonb type is the flexibility it gives you — you can store whatever structure you need without defining columns up front. But that flexibility comes with a trade-off: there's nothing stopping bad data from getting in. You can slap a CHECK constraint on a jsonb column, but writing validation logic in SQL or PL/pgSQL for anything beyond the trivial gets ugly fast.I've been working on a PostgreSQL extension called json_schema_validate that solves this problem by letting you validate JSON and JSONB data against JSON Schema specifications directly in the database. If you've used JSON Schema in application code before, it's the same idea — just running inside PostgreSQL.Why validate in the database?Application-level validation is fine until it isn't. You have multiple applications writing to the same table, or someone runs a manual INSERT, or a migration script skips the validation layer. The database is the one place everything has to pass through, so it's the natural place to enforce data shape.With this extension, a CHECK constraint can enforce a full JSON Schema:CREATE TABLE events ( id serial PRIMARY KEY, data jsonb NOT NULL CHECK ( jsonschema_is_valid(data, '{ "type": "object", "required": ["event_type", "timestamp"], "properties": { "event_type": {"type": "string", "enum": ["click", "view", "purchase"]}, "timestamp": {"type": "string", "format": "date-time"}, "user_id": {"type": "integer", "minimum": 1}, "metadata": {"type": "object"} }, "additionalProperties": false }'::jsonschema_compiled) ) );That ::jsonschema_compiled cast is important — it tells PostgreSQL to parse the schema once and cache it, rather than re-parsing it on every row.What it supportsThe extension covers a large subset of JSON Schema Draft 7. The basics are all there — type validation, required properties, enum, const — but it goes well beyond that.String validation includes minLength, maxLength, pattern (POSIX regular expressions), and format for common types like dates, email addresses, IP addresses, URIs, and UUIDs:SELECT jsonschema_is_valid( '"2024-01-15T09:30:00Z"', '{"type": "string", "format": "date-time"}' ); -- trueNumeric validation covers minimum, maximum, exclusiveMinimum, exclusiveMaximum, and multipleOf:SELECT jsonschema_is_valid( '{"price": 9.99}', '{ "type": "object", "properties": { "price": {"type": "number", "exclusiveMinimum": 0, "multipleOf": 0.01} } }' ); -- trueArray validation supports items, minItems, maxItems, uniqueItems, and the contains/minContains/maxContains family:SELECT jsonschema_is_valid( '["postgres", "mysql", "postgres"]', '{"type": "array", "items": {"type": "string"}, "uniqueItems": true}' ); -- false (duplicate "postgres")Object validation goes beyond just properties and required. You get additionalProperties, patternProperties for regex-matched property names, propertyNames to constrain the keys themselves, and minProperties/maxProperties:SELECT jsonschema_is_valid( '{"env_HOME": "/home/user", "env_PATH": "/usr/bin", "name": "config"}', '{ "type": "object", "patternProperties": { "^env_": {"type": "string"} } }' ); -- trueSchema composition with allOf, anyOf, oneOf, and not lets you combine schemas in the usual ways. And conditional schemas with if/then/else handle the cases where validation depends on the data:SELECT jsonschema_is_valid( '{"type": "business", "tax_id": "123-45-6789"}', '{ "type": "object", "if": { "properties": {"type": {"const": "business"}}, "required": ["type"] }, "then": {"required": ["tax_id"]}, "else": {"required": ["full_name"]} }' ); -- trueSchema reuse works with $ref and $defs (or the older definitions spelling), using JSON Pointer syntax for local references.A note on JSON Schema versionsJSON Schema has gone through several drafts — Draft 7, 2019-09, and 2020-12 being the most widely used. The good news is that the core validation keywords (type, properties, required, string/number/array constraints, schema composition, if/then/else, $ref) are stable across all of them. The differences between drafts are mostly in advanced features like tuple validation (prefixItems replacing the array form of items), dynamic references ($dynamicRef), and evaluation tracking (unevaluatedProperties/unevaluatedItems) — none of which are implemented yet. So in practice, schemas written for any of these drafts will work with this extension, as long as they stick to the supported keywords.One deliberate choice: the format keyword is treated as an assertion (invalid formats cause validation failure). In 2020-12, format is technically just an annotation by default, but assertion behavior is what most people actually want from a validation tool.Two modes: boolean and errorsFor CHECK constraints and simple validation, jsonschema_is_valid() returns a boolean. But when you need to know what failed, jsonschema_validate() returns a JSON array of error objects with the path and a description:SELECT jsonschema_validate( '{"name": 123, "tags": "not-an-array"}', '{ "properties": { "name": {"type": "string"}, "tags": {"type": "array", "items": {"type": "string"}} } }' ); -- [{"path": "name", "message": "Expected type string but got number"}, -- {"path": "tags", "message": "Expected type array but got string"}]This is useful for building validation into application logic or trigger functions where you want to return meaningful error messages to the caller.PerformanceThe functions are marked IMMUTABLE and PARALLEL SAFE, so PostgreSQL can optimize queries that use them. Regex patterns used in pattern and patternProperties keywords are compiled once and cached in a session-level hash table, so repeated validation against the same schema doesn't recompile patterns.For CHECK constraints specifically, casting the schema literal to jsonschema_compiled avoids re-parsing the schema JSON on every row insertion.Both json and jsonb input types are supported, though jsonb will be faster since it's already in a parsed binary format.Benchmarks vs. pg_jsonschemaThe other well-known option for JSON Schema validation in PostgreSQL is pg_jsonschema, a Rust-based extension from Supabase. I was curious how the two compare, so I ran some benchmarks on PostgreSQL 17.2.The test setup: a table with 100,000 rows of varied data (name, age, email, score fields), stored as both json and jsonb columns. Each test validates every row against a schema and counts the matches. All tests were run three times; the numbers below are representative. Times shown are wall-clock from \timing in psql.Both extensions support validating json and jsonb data, so I tested both. Each extension uses its own preferred schema form: ::jsonschema_compiled for ours (pre-parsed, regex cached), and ::json for pg_jsonschema (which is all it supports). Our extension supports compiled schemas with both json and jsonb data, so the schema-side advantage applies regardless of data type.Test 1: Property and type validation (jsonb data)Schema checks object type, requires name and email, validates types for four properties (string, integer with minimum, string, number):json_schema_validate: ~59 ms pg_jsonschema: ~348 ms5.9x faster.Test 2: Property and type validation (json data)Same schema, but validating json data instead of jsonb:json_schema_validate: ~80 ms pg_jsonschema: ~334 ms4.2x faster. Slower than the jsonb case since the json text has to be parsed on every call, but the compiled schema still avoids re-parsing the schema side.Test 3: Regex pattern matching (jsonb data)Schema validates two properties with regex patterns — name must match ^user_[0-9]+$ and email must match ^[^@]+@[^@]+\.[^@]+$:json_schema_validate: ~48 ms pg_jsonschema: ~3502 ms73x faster. This is where the regex cache pays off. Our extension compiles each regex pattern once per session and stores it in a hash table. pg_jsonschema appears to recompile the pattern on every call.Test 4: Regex pattern matching (json data)json_schema_validate: ~71 ms pg_jsonschema: ~3518 ms50x faster.Test 5: Validation failure (jsonb data)Schema requires a field (missing_field) that no row has, so every row fails validation:json_schema_validate: ~16 ms pg_jsonschema: ~148 ms9x faster on the failure path.SummaryTestjson_schema_validatepg_jsonschemaSpeedupProperty + required (jsonb)59 ms348 ms5.9xProperty + required (json)80 ms334 ms4.2xRegex patterns (jsonb)48 ms3,502 ms73xRegex patterns (json)71 ms3,518 ms50xValidation failure (jsonb)16 ms148 ms9xCaveatsA few things to keep in mind before reading too much into these numbers:This was tested on a single machine (aarch64 Linux) with PostgreSQL 17.2. Results will vary on different hardware and configurations.Each extension uses its own best schema form: ::jsonschema_compiled for ours, ::json for pg_jsonschema (which is all it supports). Both json and jsonb data were tested, so neither extension is disadvantaged by data format.pg_jsonschema uses the jsonschema Rust crate, which is a full-featured, spec-compliant implementation. Our extension implements a subset of the spec in C, which gives it less overhead but also fewer features.pg_jsonschema supports a broader set of JSON Schema features, including some that we list as unimplemented (like $id, external references, and vocabulary support).The pgrx build system reported "dev profile [unoptimized + debuginfo]" for the SQL generation step, though the shared library itself was compiled with release optimizations. I'm not 100% certain this didn't affect the final binary.The performance difference in the non-regex tests likely comes from our extens...

Minimal Postgres editor for speed and collaboration - pgplex/pgconsole

Databases backup tool (PostgreSQL, MySQL, MongoDB) - databasus/databasus

PostgreSQL server wire protocol. Build your own server and start serving connections. - jeroenrinzema/psql-wire

I'm proposing a very ambitious patch set for PostgreSQL 19. Only time will tell whether it ends up in the release, but I can't resist using ...

As a PostgreSQL expert, one of the most common “ghosts” I hunt during database audits is the zombie session. You know the one: a backend process that stays active or idle in transaction, holding onto critical locks and preventing vacuum from doing its job, all because the client disappeared without saying goodbye. In the words of Miracle Max from The Princess Bride, there’s a big difference between mostly dead and all dead.

If you're running an AI-powered application like a chatbot, a RAG pipeline, an analytics assistant, or others, you've likely added a cache to cut down on expensive LLM calls. Most caches work by matching the exact query string. Same string, cache hit. Different string, cache miss.

The solutions for PostgreSQL latency across regions are either incomplete or locked to a specific platform. Here's why I built something different.

One of the limitation of pg_dumpall up to PostgreSQL version 18 is, that you can only dump in text mode. While pg_dump can dump in various formats (plain,custom,tar and directory) since ages. pg_dump was always limited to text mode. This will change with PostgreSQL 19 because this was committed recently. The default is still plain […]

Better JIT for Postgres. Contribute to vladich/pg_jitter development by creating an account on GitHub.

My friend Henrietta Dombrovskaya pinged me on Telegram. Her production cluster had just been killed by the OOM killer after eating 2 TB of RAM. work_mem was set to 2 MB. Something didn’t add up. Hetty, like me, likes playing with monster hardware. 2 TB of RAM is not unusual in her world. But losing the whole cluster to a single query during peak operations is a very different kind of problem from a 3am outage.

Use inflect.acronym to keep API, SMS, and other acronyms correctly cased in class names and autoloading.

I'm Hiroshi Shibata (hsbt), a Ruby committer and the maintainer of RubyGems and Bundler. ...

Contribute to rails/website development by creating an account on GitHub.

We’re live in beta! Here are our next steps Last week, we at gem.coop announced support for namespaces that you can publish to and use namespaced gems in Bundler today. Our original post outlines how namespaces improve package security, but in this post we’ll go through how to use them and how Bundler handles namespaced gems.

Don’t let one ambitious user trigger a denial of service.