2830
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
Nope, I don't use any LLM when I work with databases and share information about it, only my knowledge. This explanation was included in my previous message.
But I'm not happy to see clown emoji on my message because it looks like unpollite behaviour from Ilya. I think he never did that kind of task on rds, else this issue wasn't raised.
There is no any deleted messages. I joined to this channel a long ago, just have inconvenient timezone +8 for europe. It's why I answer quite rare here.
When I see question I can answer fast and in my primetime, I do it.
Guys asked about pg_repack on rds, if they use pg_repack I suppose they know how to work with it. Only one question is left in this context, how to run pg_repack on rds instance, so I shared how I do it. Sometimes it's difficult to find all required steps for your task, I showed only direction not more.
For example, right now you will not find how to setup pgbouncer on rds with SCRAM-SHA-256 hashes, because aws closed all ways to find it, described way in pgbouncer docs doesn't work too.
not me, but i'm now curious because I run PostgreSQL on RDS
Читать полностью…
Okay, first of all, if you need to run VACUUM at such a great extent, check why you accumulated that bloat. Usually AUTOVACUUM should take care of bloat before it gets too bad.
Then, VACUUM FREEZE will surely create more locks that a simple vacuum and as such you have to be careful if writes are happening on the table at the same time. You will get a lot of extra wal (more replication lag, possibly) and in general high load on your server (depending if your bottleneck is cpu or storage, you might have or not slowdowns)
If you need absolutely no locks taken or downtime, you might be interested in pg_repack that unlike VACUUM does not take locks (but it forces you to have a pk on every table): https://reorg.github.io/pg_repack/
#job #dba #postgresql #blockchain #database #remote
Job: PostgreSQL DBA (Blockchain)
Company: Lagrange Labs
Salary range: $4,500–$6,500
Work arrangement: remote, full-time
Responsibilities:
— Administration, optimization, and scaling of high-load PostgreSQL databases.
— Designing storage architectures for massive arrays of indexed data from various blockchain networks.
— Tuning the performance of complex SQL queries and optimizing data schemas to minimize latency.
— Configuring and monitoring backup, replication, and disaster recovery systems.
— Monitoring database health, identifying bottlenecks, and preventing incidents.
— Working with PostgreSQL extensions and integrating with analytical tools.
Requirements:
— At least 4 years of experience as a Database Administrator.
— Expert knowledge of PostgreSQL (internal architecture, vacuuming, partitioning, indexing).
— Experience working with high-load systems and terabyte-scale data sets.
— Skills in automating routine tasks (Python/Bash) and working with infrastructure in Docker/Kubernetes.
— An understanding of the specifics of blockchain data (immutability, block and transaction structure) would be a significant plus.
Contact: Gorskines
As I understand, the simplest way to convert sqlite db to postgresql db is to use pgloader?
Читать полностью…
(Well, in my own practice, frequent checkpoints with moderate completion_target like 0.25 are generally fine, they distribute writes evenly when some heavy-writing begins and don't pause server for a long at checkpoint.
So, the general statement "they cause issues" is not universally true and probably is not directly concludes from the official docs).
Why not?
Also because they can.
If to be seriouser — OK, go on, set up a reproducible example to have something to talk about.
Without that, without even defining "issues" — this is just gibberish.
Then you could either find reasons yourself, or ask us to help to find them...
https://www.postgresql.org/docs/current/wal-configuration.html
Читать полностью…
Which postgresql parameters control check point behavior? Could someone please help me with this question
And why do frequent aggressive checkpoints cause performance issues?Could someone please help me with this question
I think the question is, did you use any LLM to write that answer?
Читать полностью…
The second one probably was a mythclick.
Just clicking on /command posts it immediately in most clients.
And I'm (I really typed it) not really requesting ban, just kind of pointing out that non-verified neuroslop is generally disgusting, misleading and better not to be posted.
@tzirechnoy , @worlber64bit , why do you request a ban pls?
Читать полностью…
to use pg_repack on rds need to do few additional steps.
• install or build appropriate binaries what fits to your version of pg_repack extension. I have to build it for macos, for linux I think you can find correct package.
• use pg_repack with flag -k, skip superuser checks in client.
• run pg_repack on server/laptop with access to your rds database.
All other steps are the same like you got used to do.
Lock type is same ShareUpdateExclusiveLock, only io would be more.
Читать полностью…
Hi everyone,
I have a large table (~660 GB) and I need to run VACUUM (FREEZE, VERBOSE) on it.
I wanted to ask:
• Would this require downtime, or can it be done safely while the system is live?
• What kind of performance impact (IO, latency, replication lag) should I expect?
• Do you have any best practices for running this on a table of this size (e.g. throttling, scheduling, or breaking it into smaller steps)?
Thanks in advance 🙏
The simplest ever is to export using the .dump command. It'll generate a sql file you can import using \i in psql. Remember to adapt the types so you actually have correct ones.
Those questions look very much like interview/assesment form questions.
So, if you don't know what a checkpoint is, how to configure the behaviour and why they can be problematic, you're probably not the right candidate for the job.
Also, e.g. the answer to question #2 would include full page writes, and chances are high that you don't know about those either. So you'd fail if you get to the second round.
Hence we saved you and the prospect employer time & money... you're welcome.
The answers are in the page that I've linked to you. If you still have a question, make a specific question that points at what you're not understanding.
Читать полностью…