Thank you so much but the developers need to rebuild has code to make port 5000 for read and 50001 for write
Читать полностью…You can (and should) still use a connection pool or something similar in between. The easiest solution I know is Patroni with HAProxy (because Patroni comes with a ready-to-use haproxy config.
Or you define your read-write datasource with targetServerType=primary
(again assuming Java/JDBC). If your primary goes down, your connections will be lost anyway...
Some thoughts:
* Pgpool parses each statement to determine if it is read-only or read-write. Do you really need that?
* In transaction mode, the poolers send commands to reset the backend session after each transaction. Do you really need that?
* How many parallel client requests do you really expect?
* Maybe at the time you breach 60k TPS (that is *a lot*!), CPUs will be faster or pgbouncer will be multithreaded?
there are alternative load balancers to Pgpool and HAProxy for managing PostgreSQL traffic
Because when using this
Application ---=pgbouncer -----=pgpool -----postgres and make a bench mark tps are 60000 I make 4 instances for pgbouncer , and the pgbouncer and pgpool on same server with pgbouncer without pgpool the tps are 109000 tps
is anyone familiar with LLM/RAG in postgres and can navigate me where I should study this from?
Читать полностью…Oh, so NOT breaking a sweat.
Yeah, you could put the second PgBouncer on the same machine.
(But I don't know the exact steps)
I am NOT a linux Guru. But as long as they are configured from 2 different directories, and using 2 different ports.
I would also play with pgbench and running 2 sessions at the same time.
One using PgBouncer and one using Direct to PG.
Just to see the impact.
I was thinking firing up another machine.
But because PgBouncer is Single Threaded. A new instance should also be Single Threaded.
You could test on the same machine ONLY IF CPU is not consistently over 40% while PgBouncer is running.
otherwise the CPU will become your bottleneck.
How many TPS do you need to meet the requirements?
If you can handle 500 direct connections to the DB.
Why, exactly, use PgBouncer? How many transactions are working hard?
It works, that's what I can say. Even across datacenters, networks etc., which tends to become quite an issue at some point (ever had a discussion with a network person about ARP cache timeouts? 😏).
Читать полностью…Either use some database (dns, etcd, ldap, ...) or map IP addresses/ports (NAT, carp, kubernetes network management, ...)
Читать полностью…Define a second datasource (assuming Java) and send all reading statements to that.
It's not trivial. But so isn't using pgpool or any other "automatic" solution. Imagine you write a stored function that writes something. You have to tell pgpool that "SELECT myfunc('abc');" is a writing statement, because pgpool thinks that "SELECT" is a read-only call and will send it to a replica. Etc. pp.
Your application on the other hand already knows that you want to write something, so "all you need to do" is send the SELECT to the r/w datasource...
I have used pgvector extension to PostgreSQL to store embeddings from LLM. You can use RAG with pgvector. https://github.com/pgvector/pgvector
Читать полностью…DataGrip, I believe has this feature.
I wrote a Python script for this (that I cannot share) that does just this.
This is how we parse out and store everything in SVN so we can track
changes over time.
The only challenge we ran into is the Overloaded method names for procedures/functions.
That required creating filenames for SOME functions like: my_func(int,int,float).ddl and my_func(float, float, int).ddl
I parsed the SQL output. If I had to do it over again, I would strongly consider opening the binary toc.dat file
And walking through that.
HTH...
Friends, do we have any command or option to export DB objects as file per object...
Читать полностью…I don't know what that means, I think in terms of Percentage... Is that 50%
And is that while it is busy?
You get the point.
But it is probably easier to create a second pgbouncer machine (vm even).
And divide the workload using 2 pgbench runs.
Anyways, that's where I would experiment.
AGAIN, I don't know the REQUIREMENT you are trying to hit in terms of TPS.
This aint bad. Keep in mind, you add a TRIP for that data to PgBouncer.
To then send that data back to the client. As the size of the queries go up, the slowdown will be worse.
Oh, if it is separate, I would fire up a SECOND pgBouncer.
And split the traffic. If that gives you closer to 6k tps...
Then I think you have your answer.
In our environment, we choose to have a SUBSET of users connect directly to PG.
And only the Web Pages use PgBouncer.
I want to achieve 107000
My hardware for pgbouncer are 16core CPU , ram 64GB , hard 1TB ssd , and postgresql the same