Hasty Briefsbeta

All tags

#postgresql

112 stories total

Bilingual

Show HN: Cloud-Ready Postgres MCP Server
a year ago
- PG-MCP is a server implementation of the Model Context Protocol (MCP) for PostgreSQL databases.
- It provides a comprehensive API for AI agents to interact with PostgreSQL databases.
- Key features include multi-database support, rich catalog information, and query explanation tools.
- Built-in contextual information for PostgreSQL extensions like PostGIS and pgvector.
- Includes tools for connection management, query execution, and schema exploration.
- Supports read-only mode by default with secure connection handling.
- Can be deployed using Docker Compose or run directly with Python.
- Includes test scripts and a Claude-powered natural language to SQL conversion tool.
- Built on MCP, FastMCP, asyncpg, and YAML for extension context.
- Contributions welcome for additional extensions and features.
All Databases Are Just Files
a year ago
- SQLite and DuckDB are popular because they are simple files that can be easily copied and moved.
- PostgreSQL, while more complex, can also be simplified by understanding its components and file operations.
- Installing PostgreSQL via package managers often leads to treating it as a black box, causing detachment from the system.
- PostgreSQL's default setup includes a system user, data directory, binary, and default database, all named 'postgres', which can be confusing.
- PostgreSQL fundamentally operates by turning SQL queries into filesystem operations, with files stored in a data directory.
- Manually managing PostgreSQL (e.g., using `initdb`, custom config files) can simplify workflows and enhance understanding.
- Understanding PostgreSQL's file structure and processes empowers users to debug, optimize, and manage databases more effectively.
- This approach is ideal for development, prototyping, and testing, though not recommended for production environments.
- Gaining this knowledge helps build cleaner workflows and reduces reliance on treating databases as black boxes.
Memory Size Matters to PostgreSQL
a year ago
- PostgreSQL's shared buffer is a memory segment for managing data flow between data area and backends.
- PostgreSQL evolved from LRU to ARC, then to 2q, and finally to clock sweep algorithm for buffer management.
- Clock sweep algorithm manages buffer replacement via a circular buffer named nextVictimBuffer and a spinlock buffer_strategy_lock.
- Usage counters on buffers are incremented when pinned, affecting eviction decisions.
- Bulk operations like VACUUM or large sequential scans use a small ring buffer to avoid affecting the shared buffer.
- PostgreSQL recommends setting shared_buffer to 25% of system RAM, with an upper limit of 40%.
- Performance with large shared buffers depends on whether the data area fits within the shared buffer.
- Modern DDR4 memory throughput affects the time required for full shared buffer scans.
- A practical upper limit for shared_buffer is around 64GB to avoid performance regression.
- It's crucial to adjust the default shared_buffer setting for optimal performance in production environments.
Logical Replication from Postgres to Iceberg
a year ago
- Crunchy Data Warehouse integrates PostgreSQL with Iceberg for analytics.
- Logical replication from PostgreSQL to Iceberg is now available.
- Supports insert, update, delete, and truncate operations with low lag (<60 seconds).
- Automatic table creation, data copy, and compaction are included.
- Advanced features like row filters and failover slots are supported.
- Setup requires just two commands for quick deployment.
- Efficient merge-on-read method reduces computational costs.
- Replication maintains transaction boundaries and foreign key constraints.
- Available on Crunchy Bridge and soon for self-managed Kubernetes users.
- Aims to enhance PostgreSQL features for high-performance analytics with Iceberg.
PgDog is a transaction pooler logical replication manager can shard PostgreSQL
a year ago
- PgDog is a transaction pooler and logical replication manager for PostgreSQL, written in Rust.
- It supports sharding, managing hundreds of databases and thousands of connections.
- Quick installation options include Helm charts and Docker Compose.
- Features include load balancing, health checks, and failover mechanisms.
- PgDog can route queries to shards automatically and supports cross-shard queries.
- It includes a CSV parser for splitting COPY commands across shards.
- Logical replication allows sharding existing databases without downtime.
- Configuration is flexible with runtime adjustments possible.
- PgDog is open-source under AGPL v3, allowing internal use and private modifications.
- Early adopters are encouraged to try PgDog, with regular updates on feature stability.
Partitioning a large table in PostgreSQL with Rails
a year ago
- PostgreSQL table partitioning in Rails helps manage large datasets efficiently.
- Auditing system tracks changes with Active Record callbacks, storing data in an audits table.
- Large audits table caused high costs, operational risks, and degraded query performance.
- Hot-to-cold access pattern: 90% of queries target recent data (last 30 days).
- Archived older audits to Amazon S3 for cost-effective, durable storage.
- Declarative partitioning (range, list, hash) improves performance by pruning irrelevant partitions.
- Chose range partitioning by ID (correlated with created_at) for chronological data.
- Migration strategy: Attach existing table as a partition with CHECK constraints to avoid downtime.
- Handled out-of-range data using a default partition and inheritance as a temporary solution.
- Automated partition maintenance with Rails jobs for seamless inserts and cleanup.
Amazon RDS for PostgreSQL 17.4
a year ago
- PostgreSQL uses MVCC for transaction isolation, with 'Read Uncommitted' and 'Read Committed' both acting as Read Committed, 'Repeatable Read' providing Snapshot Isolation, and 'Serializable' ensuring Serializability.
- Amazon RDS for PostgreSQL is a managed service automating tasks like provisioning, replication, and backups, with Multi-AZ deployments enhancing fault tolerance through synchronous replication.
- RDS provides primary and reader endpoints; the primary supports all isolation levels, while secondaries do not support Serializable, limiting the strongest consistent level to Snapshot Isolation.
- Testing involved a Jepsen-adapted workload on RDS clusters, focusing on transactions over integer lists to detect isolation anomalies via dataflow dependencies.
- Amazon RDS for PostgreSQL exhibited G-nonadjacent cycles (violating Snapshot Isolation), including Long Fork anomalies, across all tested versions (13.15 to 17.4).
- The anomalies suggest RDS may offer Parallel Snapshot Isolation, weaker than single-node PostgreSQL's Strong Snapshot Isolation, potentially affecting transaction consistency.
- Users are advised to scrutinize transaction structures for Long Fork risks or restrict safety-critical operations to the writer endpoint to mitigate anomalies.
- Jepsen's findings are experimental, demonstrating bugs but not proving correctness, with no compensation received for the independent analysis.
Understanding transaction visibility in PostgreSQL clusters with read replicas
a year ago
- Jepsen report highlights transaction visibility behavior in Amazon RDS for PostgreSQL Multi-AZ clusters.
- Issue involves Long Fork anomaly, affecting visibility order between primary and replicas.
- No data loss or corruption; issue known to PostgreSQL community since 2013.
- Example scenario: Alice and Bob see different rankings due to visibility order differences.
- AWS is working with PostgreSQL community to propose a fix using Commit Sequence Numbers (CSN).
- Long Fork anomaly impacts distributed systems, query routing, and data synchronization.
- AWS commits to PostgreSQL's success, with a dedicated PostgreSQL Contributors Team.
- Recommendations: review application assumptions, use explicit synchronization, contact AWS Support.
- Further reading available on PostgreSQL community work and Aurora solutions.
Pg_parquet v0.4.0: Google Cloud Storage, HTTPS storage, and more
a year ago
- pg_parquet v0.4.0 released with new features including Google Cloud Storage and HTTPS support.
- New capabilities: COPY TO/FROM Google Cloud Storage, http(s) stores, stdin/stdout with FORMAT PARQUET.
- Supports Parquet UUID, JSON, and JSONB types for enhanced data handling.
- pg_parquet simplifies exporting and importing Parquet files within Postgres without third-party tools.
- Parquet is an open standard file format with columnar compression, ideal for data movement.
- Apache Iceberg recommended for full database cloud storage with metadata layer.
- Example SQL commands provided for copying data to/from Parquet files and cloud storage.
- Version 0.4 enhances data movement between Postgres and Parquet for analytics and lakehouse architectures.
- Lightweight and performant, leveraging Postgres native COPY command.
- Community contributions and feedback encouraged via GitHub.
Implementing State Machines in PostgreSQL (2017)
a year ago
- Finite-state machines (FSMs) are a powerful model for computation and can simplify business logic.
- FSMs can be represented as directed graphs, showing states and transitions via events.
- Implementing an FSM in PostgreSQL provides data integrity and advanced analytics capabilities.
- A PostgreSQL function can enforce FSM rules, ensuring valid state transitions.
- User-defined aggregates in PostgreSQL can recursively apply state transitions.
- Triggers can validate events against the FSM before insertion.
- FSMs in PostgreSQL enable advanced analytics, such as tracking state histories over time.
- Embedding business logic in the database can prevent race conditions and improve reliability.
- The approach is scalable, having been tested with billions of event rows.
Pgwatch: PostgreSQL Monitoring Solution
a year ago
- Run the latest demo Docker image with Grafana on port 3000, administrative web UI on port 8080, and internal database on port 5432.
- Use `docker run` command with specific ports and environment variables to start the container.
- Access Grafana as admin (admin/pgwatchadmin) to view or create dashboards.
- For production, use the cybertecpostgresql/pgwatch Docker image.
- Clone the repository and use Docker compose for quick setup with services like configuration database, monitoring agent, and Grafana.
- Add a test database for monitoring using WebUI or command line script.
- Emulate workload with `docker/compose.pgbench.sh` to generate test data and transactions.
- pgAdmin uses port 80; modify `docker/compose.pgadmin.yml` to change the port.
- Access pgAdmin4 at localhost with credentials [email protected]/admin to inspect the pgwatch database.
- Restart the pgwatch agent after code changes with a rebuild and relaunch command.
- Monitor logs in detached mode using `docker compose logs` commands.
- Feedback and contributions are welcome.
Postgres with data branching and PII anonymization
a year ago
- Xata relaunches as 'Postgres at scale', offering a new PostgreSQL platform with features like Copy-on-Write branching and PII anonymization.
- The platform is cloud-agnostic, supports BYOC (Bring Your Own Cloud), and separates storage from compute for better scalability and cost efficiency.
- Key features include instant data branching, data anonymization for developer branches, and compatibility with existing PostgreSQL services like AWS RDS and Aurora.
- Xata addresses organizational challenges at scale, including zero-downtime schema changes, realistic testing data without PII, and ephemeral developer environments.
- The platform integrates open-source projects pgstream (for replication and masking) and pgroll (for zero-downtime schema changes).
- Xata's storage system, powered by Simplyblock, enables features like instant Copy-on-Write branching and distributed storage over NVMe/TCP.
- Compute is managed via Kubernetes and CloudNativePG, ensuring high availability, scalability, and cloud-agnostic deployment.
- Xata offers significant cost savings (up to 80% compared to Aurora) with a simple pay-as-you-go pricing model for compute and storage.
- The Xata Agent, an LLM-powered tool, helps optimize and tune PostgreSQL instances.
- Existing Xata users can continue using Xata Lite, a free tier for smaller projects, while the new platform targets startups and enterprises.
UUIDv7 Comes to PostgreSQL 18
a year ago
- PostgreSQL 18 beta 1 is released with new features like UUIDv7, async I/O, and virtual generated columns.
- UUIDv7 is a timestamp-based UUID variant that improves sorting and index locality in databases.
- UUIDs are useful for distributed databases, unguessable public identifiers, and client-generated IDs.
- UUIDv7 addresses concerns with sorting and index locality but not the size of UUIDs.
- PostgreSQL 18 introduces the `uuidv7()` function to generate UUIDv7 values and supports timestamp extraction.
- The community is encouraged to test PostgreSQL 18 beta and report issues before the official release in September.
Show HN: BemiDB – Open-source data warehouse with zero-ETL
a year ago
- Zero-ETL Warehouse for Fast Teams allows cutting months of pipeline work by automatically connecting any data source and query with psql.
- Use cases include powerful analytics, centralized data store, BI and ad-hoc queries, and Postgres in-app analytics.
- Centralize data without pipelines by combining data from multiple sources into one unified store.
- Uncover insights with ease by building dashboards, running reports, and analyzing data with any Business Intelligence tools.
- Leverage Postgres without reverse ETL by querying analytics data directly in your application with seamless integration.
- Testimonials and community engagement highlight BemiDB as one of the fastest-growing open source data analytics projects.
- FAQ section available for any questions, with an invitation to see BemiDB in action today without a credit card required.
- Blog features posts on engineering topics like data analytics with PostgreSQL, Postgres indexing, and rethinking event sourcing.
Direct TLS can speed up your connections
a year ago
- Aurora DSQL connections were slower without VPN, specifically in AWS offices.
- Debugging revealed an extra non-Postgres connection was being made for each cluster connection.
- Cisco firewall was initiating a second TLS 1.2 handshake to inspect certificates due to TLS 1.3 encryption.
- Postgres 17 introduced direct TLS support, allowing TLS handshake without prior SSLRequest.
- Direct TLS in Postgres 17+ clients requires `sslmode=require` and `sslnegotiation=direct`.
- Aurora DSQL enforces TLS, making direct TLS a recommended feature for faster connections.
- Example connection strings provided for `psql` and `pdsql` with direct TLS parameters.
IDE for PostgreSQL in VS Code from Microsoft
a year ago
- Public preview of PostgreSQL extension for VS Code announced.
- Extension integrates PostgreSQL database management and development workflows.
- Features include schema visualization, database-aware GitHub Copilot, and context-aware IntelliSense.
- GitHub Copilot Chat agent mode provides AI-driven guidance for complex tasks.
- Simplified connection management for local and cloud-hosted PostgreSQL instances.
- Password-less authentication with Entra ID for streamlined security.
- Database Explorer for structured view and management of database objects.
- Query history and context-aware IntelliSense enhance productivity.
- Deep integration with Azure Database for PostgreSQL.
- Easy installation via VS Code Extensions Marketplace.
Big Problems From Big IN lists with Ruby on Rails and PostgreSQL
a year ago
- Big IN clauses with large lists of values can cause performance issues in relational databases.
- Active Record ORM methods like `includes` and `preload` can implicitly generate IN clause queries.
- Alternatives to IN clauses include using ANY or SOME operators, VALUES clauses, or restructuring queries to use joins.
- PostgreSQL 17 and 18 introduce improvements for handling IN clauses and grouping similar queries in pg_stat_statements.
- Testing query performance with EXPLAIN (ANALYZE, BUFFERS) is recommended to identify and optimize problematic queries.
Bridged Indexes in OrioleDB: architecture, internals and everyday use?
a year ago
- OrioleDB beta10 introduces bridged indexes to support non-B-tree indexes on OrioleDB tables.
- Bridged indexes are necessary because OrioleDB stores rows in a B-tree on the primary key and uses an undo log for MVCC, which is incompatible with PostgreSQL's Index Access Methods.
- The bridge index layer includes a virtual 'iptr' column that maps to primary-key values, allowing PostgreSQL indexes to work with OrioleDB.
- Bridged indexes automatically handle MVCC and logical updates/deletes, unlike PostgreSQL indexes which rely on VACUUM for cleanup.
- Users can create bridged indexes automatically or manually, with options to control the bridge layer for performance or testing purposes.
- Performance considerations include an extra lookup hop for bridged indexes and additional overhead for updates on columns involved in bridged indexes.
- Bridged indexes provide access to PostgreSQL's rich IndexAM ecosystem (GIN, GiST, etc.) without requiring extensions to be rewritten for OrioleDB.
- The feature allows users to balance performance and functionality, with future updates expected to enhance OrioleDB's capabilities.
Rethinking PostgreSQL Storage
a year ago
- Cloud storage was initially designed around the limitations of old hardware like HDDs.
- Modern NVMe SSDs offer 2.5 million IOPS for $600, compared to $1.3M/month for similar performance on Aurora.
- Local NVMe SSDs are faster, cheaper, and more reliable than network-attached storage.
- Cloud providers historically used network-attached storage for durability and scalability.
- NVMe SSDs connect directly over PCIe, reducing latency and enabling parallel data paths.
- Network-attached storage still offers elasticity and durability advantages.
- Ubicloud benchmarks show PostgreSQL performs 1.4x better on NVMe than Aurora and 4.6x better than RDS.
- TPC-H benchmarks show Ubicloud with NVMe is 2.42x faster than Aurora and 2.96x faster than RDS.
- Local NVMe SSDs are the future for cloud databases, especially for data-intensive applications like PostgreSQL.
The Art of SQL Query Optimization
a year ago
- SQL is a declarative language where only the query result is specified, and the DBMS determines the steps to produce it.
- The query optimizer finds the most efficient plan by generating and evaluating possible plans based on costs.
- Plan Explorer is a tool that visualizes PostgreSQL query optimizer decisions by iterating over a search space and generating diagrams.
- The tool can operate in standalone mode using WebAssembly PostgreSQL (PGlite) or in server mode to connect to real PostgreSQL installations.
- Plan Explorer generates visualizations of query plans, expected costs, actual execution times, and estimated vs. actual tuple counts.
- Example query analysis shows PostgreSQL's optimization, such as changing a LEFT JOIN to an INNER JOIN when filters eliminate NULL tuples.
- The tool helps identify mispredictions, like incorrect tuple count estimates, and aids in tuning cost models.
- Plan Explorer is open-source and available on GitHub, providing insights into PostgreSQL's query optimization decisions.

firstprev0next

About|Login

#postgresql

Show HN: Cloud-Ready Postgres MCP Server

All Databases Are Just Files

Memory Size Matters to PostgreSQL

Logical Replication from Postgres to Iceberg

PgDog is a transaction pooler logical replication manager can shard PostgreSQL

Partitioning a large table in PostgreSQL with Rails

Amazon RDS for PostgreSQL 17.4

Understanding transaction visibility in PostgreSQL clusters with read replicas

Pg_parquet v0.4.0: Google Cloud Storage, HTTPS storage, and more

Implementing State Machines in PostgreSQL (2017)

Pgwatch: PostgreSQL Monitoring Solution

Postgres with data branching and PII anonymization

UUIDv7 Comes to PostgreSQL 18

Show HN: BemiDB – Open-source data warehouse with zero-ETL

Direct TLS can speed up your connections

IDE for PostgreSQL in VS Code from Microsoft

Big Problems From Big IN lists with Ruby on Rails and PostgreSQL

Bridged Indexes in OrioleDB: architecture, internals and everyday use?

Rethinking PostgreSQL Storage

The Art of SQL Query Optimization