Hasty Briefsbeta

All tags

#database

200 stories total

Bilingual

Transforming a Clojure Database into a Library with GraalVM Native Image and FFI
3 months ago
- ChronDB was initially a server-based time-traveling key/value database with Git as its storage engine.
- The need to embed ChronDB in a Rust binary led to exploring GraalVM Native Image and FFI for polyglot compatibility.
- The architecture consists of five layers: Language Bindings, C API, Java Bridge, Clojure Bridge, and ChronDB Core.
- The handle registry pattern uses integer handles for clean FFI boundary crossing, avoiding pointer complexities.
- GraalVM Native Image requires Java layer with @CEntryPoint annotations for C-callable functions.
- The build process involves creating an uberjar, compiling Java with SVM classpath, and generating a shared library.
- Python bindings use ctypes for direct C library access, managing GraalVM isolate lifecycle and string encoding.
- Rust bindings leverage bindgen for FFI declarations, emphasizing ownership and RAII for safe resource management.
- Data flows through layers with JSON as the interchange format, ensuring consistent behavior across languages.
- The architecture allows for single source of truth, no runtime JVM dependency, and easy addition of new language bindings.
Databases should contain their own Metadata – Use SQL Everywhere
3 months ago
- Floe database provides system views (sys.*) to query metadata about itself, enabling users to diagnose and optimize performance.
- System objects like sys.table, sys.view, sys.function, and sys.query_log allow users to analyze queries, storage, and usage patterns directly via SQL.
- Floe supports PostgreSQL wire protocol and ADBC for compatibility but emphasizes its own efficient metadata model with Snowflake IDs for keys.
- Diagnostic views (sys.diag*) simplify common troubleshooting tasks without complex joins.
- Floe aims to balance modern metadata design with legacy standards (e.g., INFORMATION_SCHEMA) for broad tool compatibility.
Write Transactions Are a Footgun with Rust's SQLx and SQLite
3 months ago
- Write transactions in SQLx with SQLite can cause lock starvation and performance issues.
- SQLite is single-writer, requiring an EXCLUSIVE lock for writes, which can lead to contention.
- Async transactions in SQLx may hold locks across await points, blocking other writers.
- Batched writes can mitigate lock starvation by reducing the number of write operations.
- Atomic writes to multiple tables require raw SQL, posing SQL injection risks.
- SQLx could potentially use Rust's type system to prevent holding write locks across awaits.
- Documentation updates and community discussions are suggested to raise awareness of this issue.
pg_background: Make Postgres do the long work (while your session stays light)
3 months ago
- pg_background allows PostgreSQL to execute SQL commands asynchronously in background worker processes, freeing up client sessions.
- Key features include non-blocking operations, autonomous transactions, resource isolation, and server-side observability.
- Common use cases include background maintenance tasks (VACUUM, ANALYZE), async backfills, audit writes, and deferred heavy operations.
- The v2 API introduces safer handles (PID + cookie) to prevent PID reuse issues and offers better monitoring and control.
- Recent updates (v1.6 to v1.8) focus on production hardening, security, memory efficiency, and operational controls.
- Important operational considerations include managing max_worker_processes, understanding detach vs. cancel, and using observability tools.
- pg_background is designed to be a simple, reliable tool for async execution within PostgreSQL, minimizing the need for external job systems.
Stoolap/Node: A Native Node.js Driver That's Surprisingly Fast
3 months ago
- Stoolap is an embedded SQL database written in pure Rust, now with a Node.js driver (@stoolap/node) for direct access from JavaScript/TypeScript.
- Stoolap outperforms SQLite in 47 out of 53 benchmark tests, especially in complex queries (e.g., COUNT DISTINCT is 138x faster).
- Key features include MVCC transactions, cost-based query optimizer, parallel execution, semantic query caching, and temporal queries.
- SQLite still wins in simple, single-row operations (1.0x to 1.6x faster), but margins are small.
- Performance advantages come from MVCC without locks, cost-based optimizer, and parallel execution.
- The Node.js driver supports async/sync APIs, prepared statements, transactions, and file-based persistence with WAL.
- Getting started: npm install @stoolap/node; pre-built binaries available for macOS, Linux, and Windows.
- Future plans include connection pooling helpers and streaming query support.
What Is a Database Transaction?
2 months ago
- Database transactions are sequences of actions performed as a single, atomic operation in SQL databases.
- Transactions in MySQL and Postgres begin with `begin;` and end with `commit;` or `rollback;`.
- Postgres uses multi-versioning of rows with `xmin` and `xmax` to manage consistent reads.
- MySQL uses an undo log to reconstruct past versions of rows for consistent reads.
- Isolation levels in databases include Serializable, Repeatable Read, Read Committed, and Read Uncommitted.
- Serializable is the strictest isolation level, ensuring transactions appear to run sequentially.
- MySQL handles conflicting writes with row-level locking, which can lead to deadlocks.
- Postgres uses Serializable Snapshot Isolation with predicate locks for optimistic conflict resolution.
- Phantom reads, non-repeatable reads, and dirty reads are phenomena that different isolation levels allow or prevent.
- Understanding transactions and isolation levels is crucial for effective database management.
SQL vs. NoSQL: How to Answer This Interview Question in 2026
2 months ago
- Modern databases like Postgres and Mongo are capable of handling serious traffic, flexible schemas, transactions, and replication.
- The SQL vs NoSQL debate is less about technical limitations and more about choosing the right tool for the workload and understanding the consequences.
- Common database issues often stem from bad access patterns, such as full table scans in hot paths or missing indexes, rather than the database itself.
- The key to answering SQL vs NoSQL questions lies in explaining workload requirements and potential failure modes without oversimplifying.
- Choosing distributed databases for 'future proofing' can add unnecessary complexity, cognitive load, and slower onboarding for small teams.
- Teams that invest in proper modeling, indexing, and understanding isolation levels can often scale a single relational database effectively.
- Most scaling problems are actually modeling problems disguised as database limitations.
- Senior engineers look for answers that demonstrate an understanding of tradeoffs related to business risk and team capacity.
- The real differentiator is sharp thinking that matches the workload and the honesty to choose simplicity when appropriate.
Frankensqlite a Rust reimplementation of SQLite with concurrent writers
2 months ago
- FrankenSQLite is a database engine with concurrent writers, self-healing pages, and compiler-enforced safety.
- It eliminates the SQLITE_BUSY bottleneck by allowing parallel writes with zero lock contention.
- Uses MVCC (Multi-Version Concurrency Control) for high throughput and correctness.
- Features adaptive indexing, time-travel queries, and page-level encryption.
- Built entirely in safe Rust with zero unsafe code, ensuring memory safety.
- Includes self-healing storage with automatic recovery from corruption.
- Supports full SQL with SQLite compatibility and an extension ecosystem.
- Provides transaction observability with detailed microsecond-level tracing.
- Uses structured concurrency and deterministic conflict resolution.
- Offers dual storage modes and a composable, modular architecture.
Rust zero-cost abstractions vs. SIMD
2 months ago
- Reduced full-text search query latency from 220ms to 47ms by optimizing Rust's 'zero-cost' iterators.
- Discovered that Rust's iterators were preventing vectorization, leading to high merge costs in queries with many filter values.
- Implemented batched iterators to amortize merge costs and enable SIMD vectorization, significantly improving performance.
- Highlighted the importance of mechanical sympathy and looking beyond abstractions for performance optimizations.
- Demonstrated the effectiveness of batched iterators, a decades-old database technique, in modern Rust applications.
How does B-tree make your queries fast?
2 months ago
- B-tree is a data structure that enables efficient searching through large amounts of data and is widely used in modern databases.
- B-tree differs from Binary Search Tree (BST) by allowing multiple values per node and being self-balancing, which optimizes performance.
- Hardware considerations like CPU caches, RAM, and disk storage influence the design of B-tree to minimize random access and maximize sequential access.
- Sequential access is significantly faster than random access, especially on HDDs, making B-tree's design advantageous for disk-based storage.
- B-tree reduces tree height by packing multiple values into nodes (pages), which decreases the number of disk reads required for queries.
- Postgres uses 8kB pages, allowing ~375 values per page, enabling efficient storage and retrieval even for large datasets (e.g., 1 billion rows in 4 levels).
- B-tree is self-balancing, ensuring consistent performance during insertions by splitting nodes and promoting values to parent nodes as needed.
- Postgres optimizes splits to minimize free space, enhancing storage efficiency.
- B-tree's design, focusing on hardware efficiency and self-balancing, has made it a durable and effective indexing structure for decades.
SQLite Release 3.52.0
2 months ago
- SQLite Release 3.52.0 fixes the WAL-reset database corruption bug.
- Enhancements to ALTER TABLE allow adding and removing NOT NULL and CHECK constraints.
- Introduction of the Query Result Formatter (QRF) library for improved readability of SQL query results.
- New built-in SQL functions: json_array_insert() and jsonb_array_insert().
- Major CLI renovations including enhanced .mode command and improved result formatting with QRF.
- New C-language interfaces and SQLITE_PREPARE_FROM_DDL option for safer SQL statement preparation.
- Query planner improvements for better performance in EXCEPT, INTERSECT, UNION operations and join order selection.
- Floating-point to text conversions now round to 17 significant digits by default.
- TEMP triggers can now modify and query tables in the main schema.
- Discontinuation of support for Windows RT.
Announcing DuckDB 1.5.0
2 months ago
- DuckDB 1.5.0, codenamed 'Variegata', is released with new features including a friendly CLI, VARIANT type, and built-in GEOMETRY type.
- The CLI has been reworked with a new color scheme, dynamic prompts, a pager, and convenience features like accessing the last result with '_'.
- A new PEG parser is introduced for better suggestions and error messages, currently experimental and opt-in.
- The VARIANT type is now supported, allowing storage of different types in the same column with better compression and query performance.
- New functions include read_duckdb for reading databases without attaching them and Azure writes for copying data to Azure Blob or ADLSv2 storage.
- An ODBC scanner extension is introduced, enabling querying of remote endpoints.
- Lakehouse formats (DuckLake, Delta Lake, Iceberg) have received updates, including VARIANT type support and table properties in CREATE TABLE statements.
- The network stack for the httpfs extension has changed from httplib to curl for better stability and security.
- Lambda syntax has been updated to Python-style (lambda x: x + 1), with the old arrow syntax (x -> x + 1) deprecated.
- The spatial extension has significant changes, including flipping axis order for consistency and moving GEOMETRY type into core DuckDB.
- Optimizations include non-blocking checkpointing for concurrent operations and improvements in aggregate functions.
- Distribution improvements include Python pip installation, a Windows install script (beta), and CLI support for Linux with musl libc.
- Extension sizes have been reduced, with some extensions seeing a 30-60% decrease in size.
PgAdmin 4 9.13 with AI Assistant Panel
2 months ago
- The Query Tool is a feature-rich environment for executing SQL commands and reviewing results.
- It allows ad-hoc SQL queries, editing updatable SELECT result sets, and saving data to CSV files.
- Users can view execution plans in text, graphical, or table formats.
- The tool includes an SQL Editor with syntax coloring and autocompletion.
- Query History panel tracks recent commands, execution time, and row counts.
- AI Assistant tab generates SQL from natural language when configured.
- Data Output panel displays query results in an editable table format.
- Explain Panel provides detailed query execution plans in JSON format.
- Graph Visualiser supports various chart types like Line, Bar, and Pie charts.
- Macros feature allows executing pre-defined SQL queries with shortcuts.
- Server-side cursors enable partial retrieval of large datasets.
Networking with agents: Put them in the right conversations with Tailscale
2 months ago
- Firetiger Database Agents can administrate Postgres, MySQL, and Clickhouse on autopilot.
- Most databases live on private networks, making it difficult for external systems like Firetiger to access them.
- Traditional solutions like VPC peering, AWS PrivateLink, VPNs, and bastion hosts have limitations and complexities.
- Firetiger Network Transports enable secure connections to private network resources, starting with Tailscale integration.
- Tailscale simplifies secure, end-to-end encrypted connections across different clouds and network configurations.
- Firetiger joins your Tailnet as an ephemeral device with permissions defined in Tailscale’s access controls.
- Steps to set up include granting Firetiger access, updating Tailscale ACLs, creating credentials, and configuring the transport.
- Agents can then operate on databases or other resources securely, even on private networks.
TigerFS – A Filesystem Backed by PostgreSQL
2 months ago
- TigerFS mounts a PostgreSQL database as a directory, treating every file as a real row with transactional writes.
- Provides full ACID guarantees for concurrent reads and writes by multiple agents or humans, locally or across machines.
- Eliminates the need for shared workspaces and custom coordination APIs, offering transactions, history, and structure.
- Supports file operations like writing markdown, organizing directories, and building lightweight apps with atomic writes and auto-versioning.
- Allows browsing and querying PostgreSQL data as if navigating directories, with support for filtering and chaining queries.
- Demonstrates practical use cases like atomic task claiming, browsing edit trails, and updating records via simple file operations.
- Supports bulk operations like CSV imports and complex queries through file system commands.
- Compatible with any PostgreSQL database, with special support for Tiger Cloud and Ghost, and works on Linux (FUSE) and macOS (NFS) without external dependencies.
Esqueleto Tutorial
2 months ago
- Persistent is used in Haskell for basic database operations, while Esqueleto allows for more complex, type-safe SQL queries.
- Esqueleto's new style syntax is safer and more sensible, becoming the default after a deprecation period of the legacy style.
- The tutorial demonstrates setting up a sandbox with Esqueleto, including connecting to a SQLite database and performing basic queries.
- Basic query operations include selecting arithmetic expressions, multiple values, and records from tables with sorting and limiting.
- Filtering data involves using `where_` clauses, string matching with `like`, combining conditions, and checking for set inclusion or nullity.
- Joins are a powerful feature in Esqueleto, allowing for cross-referencing tables with inner joins and combining multiple tables in queries.
- Aggregation functions like `count` and `groupBy` enable summarizing data, with `having` clauses for filtering grouped results.
- The tutorial emphasizes learning Esqueleto by translating SQL queries into Haskell, leveraging type safety and compiler feedback.
We give every user SQL access to a shared ClickHouse cluster
2 months ago
- TRQL (Trigger Query Language) is a SQL-style language designed to allow users to write arbitrary SQL queries against a shared multi-tenant analytical database without exposing other tenants' data or risking rogue queries.
- TRQL compiles to secure, tenant-isolated ClickHouse queries, ensuring tenant isolation is compiler-enforced and internal database details are hidden.
- Key reasons for building TRQL include: security by controlling operations via grammar, automatic tenant isolation, abstraction of internal database details, and adding features not native to ClickHouse.
- ClickHouse was chosen for its columnar storage, performance, rich SQL capabilities, and scalability.
- TRQL's compilation pipeline includes parsing with ANTLR, schema validation, tenant isolation, time restrictions, parameterization of values, and generating ClickHouse SQL.
- TRQL features virtual columns, column renaming, value transforms, and column metadata for rendering, enhancing usability and security.
- Custom functions like `timeBucket()` and `prettyFormat()` provide automatic time bucketing and rendering hints for computed expressions.
- The query editor offers schema-powered autocomplete and linting, using a dual-parser architecture for performance and correctness.
- Limits on concurrency, rows, time ranges, and query resources ensure system health and prevent abuse.
- TRQL powers dashboard widgets, SDK queries, and the REST API, forming the foundation for Trigger.dev's observability features.
The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
a month ago
- Postgres queries can be cancelled for various reasons, such as long execution time, missing indexes, or SQL mistakes.
- Cancelling a query in psql sends a CancelRequest over a new connection, identified by a process ID and a secret key.
- CancelRequests are sent unencrypted by default, posing a security risk, including potential Denial of Service attacks.
- Postgres 17 introduced support for encrypted CancelRequests, but psql still uses unencrypted messages due to architectural constraints.
- Protocol v3.2 allows for longer secret keys to prevent brute-force attacks, but requires explicit configuration.
- Unencrypted CancelRequests can be replayed by attackers to cancel future queries on the same connection.
- Elephantshark, a Postgres traffic monitor, initially struggled with concurrent connections and unencrypted CancelRequests.
- The solution involves mapping process IDs and secret keys to destination hostnames to handle CancelRequests correctly.
- Recommendations include using Postgres 18 with min_protocol_version=3.2, VPNs, and avoiding Ctrl-C in psql for security.
- Future updates may bring encrypted CancelRequests to psql, improving security.
Postgraphile v5 Released
a month ago
- PostGraphile V5 is a complete re-architecture with significant improvements in performance, customizability, and maintainability.
- Key features include the new Grafast execution engine, which replaces traditional resolvers with plan resolvers for better performance and optimization.
- PostGraphile V5 introduces executable schema exports, allowing users to 'eject' and maintain their schema independently.
- Native support for abstract types (interfaces and unions) is now available, enhancing flexibility in data modeling.
- Ruru, the new PostGraphiQL, offers enhanced capabilities, including visualizing Grafast query plans.
- A unified plugin and preset system (graphile-config) simplifies configuration and debugging across different usage modes.
- PostGraphile V5 is designed for long-term scalability, with improvements in database efficiency and customization options.
- Migration from V4 to V5 is supported with a V4 preset and migration guide, with codemods in development for plugin migration.
- PostGraphile V5 is already in production use, with extensive testing during alpha, beta, and RC phases.
- Sponsorship is encouraged to support the ongoing development of PostGraphile and related tools.
Joining databases across teams without copying data or running servers
a month ago
- Datahike uses immutable databases as values, allowing anyone with storage access to query without copying data or maintaining infrastructure.
- The system stores indices in immutable, content-addressed nodes in a persistent tree structure, enabling structural sharing between snapshots.
- Readers fetch only the branch head lazily and load nodes on demand, creating a distributed index space with no central coordination.
- Queries can join immutable databases from different teams, backends, or time points without moving data or running servers.
- The same model works in browsers via IndexedDB, with sync transmitting only changed nodes, making updates efficient.

first prev9next

About|Login

#database

Transforming a Clojure Database into a Library with GraalVM Native Image and FFI

Databases should contain their own Metadata – Use SQL Everywhere

Write Transactions Are a Footgun with Rust's SQLx and SQLite

pg_background: Make Postgres do the long work (while your session stays light)

Stoolap/Node: A Native Node.js Driver That's Surprisingly Fast

What Is a Database Transaction?

SQL vs. NoSQL: How to Answer This Interview Question in 2026

Frankensqlite a Rust reimplementation of SQLite with concurrent writers

Rust zero-cost abstractions vs. SIMD

How does B-tree make your queries fast?

SQLite Release 3.52.0

Announcing DuckDB 1.5.0

PgAdmin 4 9.13 with AI Assistant Panel

Networking with agents: Put them in the right conversations with Tailscale

TigerFS – A Filesystem Backed by PostgreSQL

Esqueleto Tutorial

We give every user SQL access to a shared ClickHouse cluster

The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

Postgraphile v5 Released

Joining databases across teams without copying data or running servers