Update PG16 prior to 1.7.0 part 2 (#2375)
* Add index on id columns (#2117)
- Whenever a label will be created, indices on id columns will be
created by default. In case of vertex, a unique index on id column
will be created, which will also serve as a unique constraint.
In case of edge, a non-unique index on start_id and end_id columns
will be created.
- This change is expected to improve the performance of queries that
involve joins. From some performance tests, it was observed that
the performance of queries improved alot.
- Loader was updated to insert tuples in indices as well. This has
caused to slow the loader down a bit, but it was necessary.
- A bug related to command ids in cypher_delete executor was also fixed.
* Fix possible memory and file descriptors leaks (#2258)
- Used postgres memory allocation functions instead of standard ones.
- Wrapped main loop of csv loader in PG_TRY block for better error handling.
* Restrict age_load commands (#2274)
This PR applies restrictions to the following age_load commands -
load_labels_from_file()
load_edges_from_file()
They are now tied to a specific root directory and are required to have a
specific file extension to eliminate any attempts to force them to access
any other files.
Nothing else has changed with the actual command formats or parameters,
only that they work out of the /tmp/age directory and only access files
with an extension of .csv.
Added regression tests and updated the location of the csv files for
those regression tests.
modified: regress/expected/age_load.out
modified: regress/sql/age_load.sql
modified: src/backend/utils/load/age_load.c
* Fix and improve index.sql regression test coverage (#2300)
NOTE: This PR was created with AI tools and a human.
- Remove unused copy command (leftover from deleted agload_test_graph test)
- Replace broken Section 4 that referenced non-existent graph with
comprehensive WHERE clause tests covering string, int, bool, and float
properties with AND/OR/NOT operators
- Add EXPLAIN tests to verify index usage:
- Section 3: Validate GIN indices (load_city_gin_idx, load_country_gin_idx)
show Bitmap Index Scan for property matching
- Section 4: Validate all expression indices (city_country_code_idx,
city_id_idx, city_west_coast_idx, country_life_exp_idx) show Index Scan
for WHERE clause filtering
All indices now have EXPLAIN verification confirming they are used as expected.
modified: regress/expected/index.out
modified: regress/sql/index.sql
* Fix and improve index.sql addendum (#2301)
NOTE: This PR was created with the help of AI tools and a human.
Added additional requested regression tests -
*EXPLAIN for pattern with WHERE clause
*EXPLAIN for pattern with filters on both country and city
modified: regress/expected/index.out
modified: regress/sql/index.sql
* Replace libcsv with pg COPY for csv loading (#2310)
- Commit also adds permission checks
- Resolves a critical memory spike issue on loading large file
- Use pg's COPY infrastructure (BeginCopyFrom, NextCopyFromRawFields)
for 64KB buffered CSV parsing instead of libcsv
- Add byte based flush threshold (64KB) matching COPY behavior for memory safety
- Use heap_multi_insert with BulkInsertState for optimized batch inserts
- Add per batch memory context to prevent memory growth during large loads
- Remove libcsv dependency (libcsv.c, csv.h)
- Improves loading performance by 15-20%
- No previous regression tests were impacted
- Added regression tests for permissions/rls
Assisted-by AI
---------
Co-authored-by: Aleksey Konovkin <alkon2000@mail.ru>
Co-authored-by: John Gemignani <jrgemignani@gmail.com>Apache AGE is an extension for PostgreSQL that enables users to leverage a graph database on top of the existing relational databases. AGE is an acronym for A Graph Extension and is inspired by Bitnine's AgensGraph, a multi-model database fork of PostgreSQL. The basic principle of the project is to create a single storage that handles both the relational and graph data model so that the users can use the standard ANSI SQL along with openCypher, one of the most popular graph query languages today.
Since AGE is based on the powerful PostgreSQL RDBMS, it is robust and fully featured. AGE is optimized for handling complex connected graph data. It provides plenty of robust database features essential to the database environment, including ACID transactions, multi-version concurrency control (MVCC), stored procedure, triggers, constraints, sophisticated monitoring, and a flexible data model (JSON). Users with a relational database background who require graph data analytics can use this extension with minimal effort because they can use existing data without going through migration.
There is a strong need for cohesive, easy-to-implement multi-model databases. As an extension of PostgreSQL, AGE supports all the functionalities and features of PostgreSQL while also offering a graph model to boot.
Apache AGE is :
Refer to our latest Apache AGE documentation to learn about installation, features, built-in functions, and Cypher queries.
Install the following essential libraries according to each OS. Building AGE from the source depends on the following Linux libraries (Ubuntu package names shown below):
yum install gcc glibc glib-common readline readline-devel zlib zlib-devel flex bison
dnf install gcc glibc bison flex readline readline-devel zlib zlib-devel
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison
Apache AGE is intended to be simple to install and run. It can be installed with Docker and other traditional ways.
You will need to install an AGE compatible version of Postgres, for now AGE supports Postgres 11, 12, 13, 14, 15 & 16. Supporting the latest versions is on AGE roadmap.
You can use a package management that your OS provides to download PostgreSQL.
sudo apt install postgresql
You can download the Postgres source code and install your own instance of Postgres. You can read instructions on how to install from source code for different versions on the official Postgres Website.
Clone the github repository or download the download an official release. Run the pg_config utility and check the version of PostgreSQL. Currently, only PostgreSQL versions 11, 12, 13, 14, 15 & 16 are supported. If you have any other version of Postgres, you will need to install PostgreSQL version 11, 12, 13, 14, 15 or 16.
pg_config
Run the following command in the source code directory of Apache AGE to build and install the extension.
make install
If the path to your Postgres installation is not in the PATH variable, add the path in the arguments:
make PG_CONFIG=/path/to/postgres/bin/pg_config install
docker pull apache/age
docker run \ --name age \ -p 5455:5432 \ -e POSTGRES_USER=postgresUser \ -e POSTGRES_PASSWORD=postgresPW \ -e POSTGRES_DB=postgresDB \ -d \ apache/age
docker exec -it age psql -d postgresDB -U postgresUser
For every connection of AGE you start, you will need to load the AGE extension.
CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
To create a graph, use the create_graph function located in the ag_catalog namespace.
SELECT create_graph('graph_name');
To create a single vertex, use the CREATE clause.
SELECT * FROM cypher('graph_name', $$ CREATE (n) $$) as (v agtype);
To create a single vertex with the label, use the CREATE clause.
SELECT * FROM cypher('graph_name', $$ CREATE (:label) $$) as (v agtype);
To create a single vertex with label and properties, use the CREATE clause.
SELECT * FROM cypher('graph_name', $$ CREATE (:label {property:value}) $$) as (v agtype);
To query the graph, you can use the MATCH clause.
SELECT * FROM cypher('graph_name', $$ MATCH (v) RETURN v $$) as (v agtype);
You can use the following to create an edge, for example, between two nodes.
SELECT * FROM cypher('graph_name', $$ MATCH (a:label), (b:label) WHERE a.property = 'Node A' AND b.property = 'Node B' CREATE (a)-[e:RELTYPE]->(b) RETURN e $$) as (e agtype);
To create an edge and set properties.
SELECT * FROM cypher('graph_name', $$ MATCH (a:label), (b:label) WHERE a.property = 'Node A' AND b.property = 'Node B' CREATE (a)-[e:RELTYPE {property:a.property + '<->' + b.property}]->(b) RETURN e $$) as (e agtype);
Example
SELECT * FROM cypher('graph_name', $$ MATCH (a:Person), (b:Person) WHERE a.name = 'Node A' AND b.name = 'Node B' CREATE (a)-[e:RELTYPE {name:a.name + '<->' + b.name}]->(b) RETURN e $$) as (e agtype);
Starting with Apache AGE is very simple. You can easily select your platform and incorporate the relevant SDK into your code.
Join the AGE community for help, questions, discussions, and contributions.
Apache AGE Viewer is a user interface for Apache AGE that provides visualization and exploration of data. This web visualization tool allows users to enter complex graph queries and explore the results in graph and table forms. Apache AGE Viewer is enhanced to proceed with extensive graph data and discover insights through various graph algorithms. Apache AGE Viewer will become a graph data administration and development platform for Apache AGE to support multiple relational databases: https://github.com/apache/age-viewer.
This is a visualization tool. After installing AGE Extension, you may use this tool to get access to the visualization features.
You can also get help from these videos.
You can improve ongoing efforts or initiate new ones by sending pull requests to this repository. Also, you can learn from the code review process, how to merge pull requests, and from code style compliance to documentation by visiting the Apache AGE official site - Developer Guidelines. Send all your comments and inquiries to the user mailing list, users@age.apache.org.