131–140 of 159 posts
Earlier quoted context omitted.
I recently started a ML project using text data and the choice was between MySQL and Postgres. Having looked at the respective features and pros and cons, the choice was immediately obvious. Also, with pgvector and https://postgresml.com available, the choice for Postgres was even easier.
Why not MongoDB?
Does anyone know when the update allowing more concurrent connections is dropping, so we can stop using pgbouncer?
That'll likely need conversion from process per connection, so not any time soon
Earlier quoted context omitted.
I would absolutely use another backup utility (additionally if you want) if I were you (barman, pgbackrest, etc). You are just wrapping pgdump, which is not a full featured backup solution. Great for a snapshot... Use some of the existing tools and you get point-in-time recovery, easy restores to hot standbys for replication, a good failover story, backup rotations, etc.
do you still need it? haven't managed pg for a while, but shouldn't pg17 have some solution for backups?
For example, pgBackRest solves real problems with features like block level incremental backups that drastically reduce storage and transfer times for many workloads, automated backup retention policies, multiple repository support for offsite backup redundancy, encryption support, point in time recovery for granular restoration capabilities, and tooling to build standby servers very quickly and efficiently. These features handle edge cases and reduce operational overhead compared to managing scripts around pg_basebackup and WAL archiving yourself. In many environments, those features are required (e.g. encryption).
Earlier quoted context omitted.
> if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly. Is there a page anywhere that collects these sorts of "turn the whole hardware security layer off" switches that can be flipped to get better throughput out of modern x86 CPUs, when your system has no real attack surface to speak of (e.g. air-gapped single-tenant HPC)?
On the kernel side there's a boot parameter for all of them: mitigations=off Software that was compiled with additional fences may have to be recompiled to remove them. https://www.kernel.org/doc/html/latest/admin-guide/kernel-pa...
smap is an OS security measure, and so does not get disabled by mitigations=off. smap can be pretty draining for certain IO performance though. IMO it should be more well-known or covered by a more obvious option.
Linux kernel developers are really bad at defining and naming options like this.
I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…
I noticed this with bandwidth. AWS price for bandwidth: $90.00/TB after 0.1TB/month. Price everywhere else (low cost VPSes): $1.50/TB after 1-5TB/month. Price some places (dedicated servers): $0.00/TB up to ~100TB/month, $1.50/TB after. You pay 60 times the price for the privilege of being on AWS. Bandwidth is just their most egregious price difference. The servers are more expensive too. The storage is more expensiv…
https://aws.amazon.com/cloudfront/pricing/?nc=sn&loc=3
Were you trying to route S3 directly out to the internet?
Earlier quoted context omitted.
Maybe they are. With NVMe hat, you get decent IO performance
It's still moderately bad. Raspberry pi is limited to 2 gen3 pcie lanes which is ~4-8x slower than the drive (and you will likely be further limited by cpu speed)
Are there good performance comparisons between postgres, mariadb and percona? I'm really curious at this point in which case each of those database shine.
Remember that Postgres's feature set is far larger than those alternatives. If you can use a range with an exclusion constraint, an unnest with an array, or the like, you'll be seeing Postgres leave the alternatives in the dust.
Imagine writing a benchmark comparing programming languages, but the benchmark only includes idioms that all tested languages shared in common. Wouldn't be a fair comparison, would it?
Earlier quoted context omitted.
Why not MongoDB?
The question should be the other way around: why mongodb? It’s not ACID compliant so has major down sides…
https://www.mongodb.com/resources/products/capabilities/acid...
Are there good performance comparisons between postgres, mariadb and percona? I'm really curious at this point in which case each of those database shine.
Apples to apples, Postgres might lose, but that'd be tying both hands behind its back first. Remember that Postgres's feature set is far larger than those alternatives. If you can use a range with an exclusion constraint, an unnest with an array, or the like, you'll be seeing Postgres leave the alternatives in the dust. Imagine writing a benchmark comparing programming languages, but the benchmark only includes idiom…
Earlier quoted context omitted.
PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of pages/blocks). Indexes (including primary key indexes) use B-trees (specifically B+ trees). When you query a table via an index, the B-tree index points to locations in the heap file InnoDB uses a clustered index approach. The primary key index is a B-tree. The ac…
Indexes that point directly to the disk column are also significantly faster to access; it is a persistent pain point for OLAP on InnoDB that all secondary indexes are indirect. You can work around it by adding additional columns to the index to make your lookups covering, but it's kludgy and imprecise and tends to bloat the index even further. (The flip side is that if you have tons of indexes, and update some unrel…