I’m excited to announce the submission of a new experimental addon: PostgreSQL Enhanced, a high-performance database backend for Gramps that addresses many limitations of the current database options.
Why Another PostgreSQL Addon?
While Gramps already has a PostgreSQL addon, this enhanced version was developed to address several critical needs:
Modern Technology: Uses psycopg (v3), not the deprecated psycopg2
Performance at Scale: Successfully tested with 100,000+ person databases
Advanced Queries: Leverages PostgreSQL’s full power with JSONB storage
Better Architecture: Connection pooling, proper transaction handling, and dual storage format
Key Features
Dual Storage Format
Maintains pickle blobs for 100% Gramps compatibility
Stores JSONB for advanced SQL queries and analysis
Best of both worlds approach
Two Database Modes
Monolithic: All trees in one database with table prefixes (great for organizations)
Separate: Each tree gets its own database (complete isolation)
Performance Improvements (vs SQLite)
12x faster person lookups (6,135/sec vs 500/sec)
100x faster name searches using indexes
Handles 100,000+ persons effortlessly
Testing & Reliability
Extensively tested with real genealogical data
Successfully imported/managed 86,647 person GEDCOM
Full compatibility with all Gramps tools verified
Zero data loss in all test scenarios
Requirements
PostgreSQL 15 or higher
Python 3.9+
psycopg>=3 (pip install 'psycopg[binary]')
Current Status
This is version 0.1.0, marked as experimental and targeted at developers and advanced users. While thoroughly tested, I’m looking for community feedback before considering it stable.
Request for Testers
I’m particularly interested in feedback from:
Users with large databases (10,000+ persons)
Organizations managing multiple trees
Anyone needing better query capabilities
PostgreSQL database administrators
Questions?
Feel free to ask questions here or on the GitHub repository. I’m actively maintaining this addon and will respond to issues and feature requests.
Thank you for your consideration, and I look forward to your feedback!
Thanks! I’m glad to get this out the door. Not included here is mention of much more advanced features like vector and graph database capabilities,etc., which are further examined in the README on the submission.
Please give it a try, and I look forward to all feedback!!
Hi, Thanks for the note. I’ll look into that. Also be aware as such an experimental item I’ll be pushing out a version upgrade later today which covers significant shortcomings (database_is_open flag, and many performance improvements) I’ve already discovered. Before I push it out I’ll investigate your issue and resolve it as well.
Hello everyone, I’ve pushed the changes to this experimental/development backend I’ve been working on this week. When it’s committed this will push the version to 1.5.1 (thanks to my internal iterations):
Update PostgreSQL Enhanced Database Backend to v1.5.1
Overview
Major update to PostgreSQL Enhanced backend bringing critical fixes, performance improvements, and full GrampsWeb compatibility.
Critical Fixes in v1.5.1
VARCHAR(255) Truncation Fix: Converted all VARCHAR(255) columns to TEXT to prevent data loss
Automatic Migration: Existing databases are automatically migrated on first open
Schema Versioning: Added migration tracking system for future updates
Major Features & Improvements (v1.0.2 - v1.5.1)
Database Architecture
Dual Database Modes: Monolithic (multiple trees, one database) or Separate (one database per tree)
Dual Storage: Pickle blobs (compatibility) + JSONB (advanced queries)
Native PostgreSQL Undo: Database-native undo/redo using savepoints (replaces file-based)
Connection Pooling: Better resource management and stability
database_is_open Flag: Improved connection state tracking
Performance Optimizations
Optimized indexes on all secondary columns
GIN indexes for JSONB queries
Trigram indexes for fuzzy text search (when pg_trgm available)
Efficient batch operations
Reduced memory footprint for large databases
Improved GrampsWeb Compatibility (still hacked due to architectural limitations in current codebse)
Improved GrampsWeb API server support
Public metadata endpoints for tree information
Proper transaction handling for concurrent access
Fixed login loop issues
Improved session management
Enhanced Querying
Native PostgreSQL full-text search preparation
SQL queries on genealogical data via JSONB
Complex relationship queries
Advanced search capabilities
Is it viable to add a Guest mode where transaction are refused?
I have often thought that Gramps was too vulnerable to let “Aunt Martha” explore independently. It is easy to unwittingly click an Add, Remove or Re-ordering widget.
Those are certainly valid concerns, particularly when imagining the use of a concurrent-use system. I’m just trying to eliminate storage obstacles right now, but I also have serious interests in related transactions/concurrency/asynchronous and headless/CLI features which could also be implemented using the same exact approach. I don’t want to cause too many strokes at once, though.
But I do not see the corresponding Addon Manager listing for the addon in the actual addons-en.json file.
( I was looking for how this addon would be listed… its name and whether it was an “Expert” level. [It is STABLE status, DEVELOPER audience] And hoped to post a capture of the actual listing for it. See the .gpr.py file for Gramps Plugin Registration details.)
@Nick-Hall
Could you review the requires_mod=[], # psycopg3 requirement handled separately line of the registration?
Is that how the requires_mod should be used? That the plugin internally-validates the necessary module is available is good for preventing an operational failure. But isn’t that registration item for prohibiting premature installation of the plugin?
And does DEVELOPER audience require Gramps to be started with a configuration switch?
Should it be EXPERIMENTAL status and EXPERT audience instead?
Yes. You can use requires_mod to check the dependency at startup.
Unfortunately the automatic install only works for pure python modules on Windows and is not recommended for externally managed environments in Linux distributions.
The correct audience is EXPERT. The DEVELOPER option is only really intended for sample or incomplete code.
For status you probably want to use either BETA or STABLE. Using STABLE implies that the plugin should be safe from data loss, but in this case it is likely that people are aware of the risks.
Hmmm. That suggests an interesting edge case to test.
What happens if a prerequisite begins failing for a database add-on plug-in that has been used for Trees.
Say that an update to psycopg3 fails.
How gracefully would the “Remember last Family Tree” (General Preferences) and “Manage Family Trees” dialogs fail? And would the message be helpful in resolving the underlying issue of a missing prerequisite?
Would the Database backend preference selector choke?
Would the Convert button in the Family Trees Manager dialog be offered? (And what would Convert do if the plug-in wasn’t available?)
Can a PostGreSQL Tree be downgraded to SQLite when the add-on IS loading? Or do you need to export to XML with a working PostGreSQL enhanced as the active backend? And now that there are THREE PostGreSQL backends, how are the Trees differentiated for incompatibility?
Otherwise, both the old and new database versions would be displayed. Does it create a new handle folder and then delete the old folder? Or reuse the handle and delete the old database?
I can’t see anything in the convert that deletes either the old database or exported backup. The user would have to delete the old database as a separate step. The export file would have to be deleted manually.