Postgres as GraphDB: high volume of data

[jorgemgomes]

Hi everyone,

First things first, I am new to graph databases. On a daily basis, I have approximately 1TB of data stored in S3 in .csv format (edges and nodes) that I need to insert into a graph database every 10 minutes (let's say 7GB every 10 minutes).

I would like to know:

1. Is a PostgreDB a good choice to act as a graph database for this use case?

2. What would be the best way to achieve this in terms of performance? In other words, what kind of application do I need to build to send the data to the PostgreDB every 10 minutes?

Sample of data:
Nodes.csv:

~id,~label,site_code:string,timestamp:Int
s:18c5a7347f2x781af33f,session_long,kukul,1712679237
s:18bfcbd9bfcx7552158f,session_long,kukul,1712679237

Edges.csv:

~id,~from,~to,~label,site_code:string,timestamp:Int
u:15455914-tracked_by-s:186894e7b25x11eedd42,u:15455914,s:186894e7b25x11eedd42,tracked_by,kukul,1712657408
u:undefined-tracked_by-s:18ec1b07588x32cad503,u:undefined,s:18ec1b07588x32cad503,tracked_by,kukul,1712657408

3. How can I test it locally for a sample of 1GB?

Thanks for your input!

[markgomer]

You can still apply some optimization techniques you would do in PostgreSQL, like batch processing and parallel insertion...

On your local app for test, you could use PG's performance monitoring tools, like the pg_stat_statements module or pgAdmin and keep comparing execution times then adjust some logic in your application...

@jrgemignani @rafsun42 do you have any insight on this?

[rafsun42]

@markgomer @jorgemgomes
This documentation on importing CSV in Apache AGE might be helpful. https://age.apache.org/age-manual/master/intro/agload.html

[HackettJP]

1. how big are the files ? one big file or many small ?
2. Is it consistent 7GB per 10 mins or does it spike say double , growth surge etc... you haven't said show long the data to be held for ? 1 year 3 months ? for ever
3. when you want to be able to view the data that has been ingested, immediately or some time later
4. if 'agload' is not quick enought you going to have to different path.

---

7. build and old-school OLTP batch ingestion process partioned to ensure fast ingestion ,
8. with mutltple ingestion handlers into ingestion table no rollback otherwise you be writing alot logs
9. then once a file has been ingested pushed or pull into a OLTP table. (partioned and index(if required)
10. push pull into DWH type system that be apache age you could do a remote table ingestion
11. really all depeneds how much data to want to hold , if it couple of days easy.....
12. very much like an ELT ETL process off cource you going to need app logic some additonal ingestion tables so the system can recover from system failure and continue from a file was not ingested say half through and wasn't copied ingested fully. tthat way you building an ACID compliant system