Wow this article and this thread is so full of useful information for getting started to ventur into DB management/engineering.
PostgreSQL's Imperfections
81–90 of 139 posts
Re: PostgreSQL's Imperfections
#82Can I bounce this idea off you guys. Would there be a market for a dba to charge maybe 100-200. Just comes in, listens to your DB use cases, and recommends various config/setting changes, hardware, etc? It seems so much better than having a team of programmers study Postgres settings for a week. That was my last experience with it at least.
Why wouldn't an automated interface be able to give similar recommendations ?
Re: PostgreSQL's Imperfections
#83Earlier quoted context omitted.
Interesting issue, never thought about xid being used in that way before! Rather than store the user ID, can you store a bcrypt hash of it? What’s the attack vector you are addressing? I guess access is given to the database for outside auditing? I’m just thinking if the db or app server is compromised anyway then someone could install a trigger to log the table changes, or change the app logic to log the vote somewh…
The attack vector would be an attacker getting their hands at a DB dump including XIDs. Without any particular measures, they'd be able to de-anonymize every vote ever made, which is kind of a worst case scenario for an anonymity-focused app. Granted, since usually there are no dumps including XIDs lying around (e.g. backups don't contain them), this would effectively require an attacker to gain access to the databas…
[deleted]
Re: PostgreSQL's Imperfections
#84If I could have one thing on that list fixed it would be #9 - no planner hints. I used Oracle (6 through 11) for both bespoke applications and to back large third party systems. I never saw widespread abuse of hints. Yet they were immensely helpful during development and troubleshooting. I put perhaps two queries into production with hints over 10+ years. No one ever had a reason to complain about either. There are n…
Few years back we had nation-wide panic that Parliament election results were unavailable for hours after closing polls due to “IT issue”. Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late. Automatic query planner just failed and all the smartest experts in country could do was to wait until it self-heals. I…
> Analysis showed that everything was right, load tested, cached etc, except real life situation of added few million rows and few thousands queries a second late
...then in no useful sense was it load tested.
Re: PostgreSQL's Imperfections
#85> The on-disk binary format is incompatible across major versions This is my major bugbear. If Postgres were able to upgrade its datastore on the fly (optionally, of course) that would make a massive difference. Instead I’ve had heart-in-mouth moments when Homebrew has decided that it wants to upgrade Postgres. (Yes, I do now use brew pin, until I transition off Homebrew for good.) #2 for me is inefficient enum stora…
Re upgrades, have you tried pg_upgrade for upgrades in place? Re enums, we had a similar thing and simply went with a smallint column instead of enum.
> Re upgrades, have you tried pg_upgrade for upgrades in place?
This only works when you have both versions available at the same time, which seems likely to break when a package manager jumps major versions, and also doesn't work when running postgres in Docker (https://github.com/docker-library/postgres/issues/37).
Re: PostgreSQL's Imperfections
#86Anyone got postgres (or general db) tips for a CoW-fs? I'm using postgres on zfs.
Ensure that the dataset containing your postgres data is configured with record size equal to postgres page size or close enough (Lots of places use 8kB ZFS records for 4kB pages). This will reduce write amplification due to excessive read-modify-write cycles.
So, with the major caveat that I am not an expert and your mileage will vary: After some playing around with it, I intentionally reverted our postgres datasets back to the default ZFS size (EDIT: 128K) because we weren't super performance sensitive and the smaller pages killed compression. Obviously compression ratio vs speed is going to depend very heavily on exactly what you're doing, but it seems to have been a good trade for us.
Re: PostgreSQL's Imperfections
#87There's also one inherent data privacy problem in MVCC that I've been running into. Suppose you have an app that lets people anonymously vote or comment on stuff, but only once. The vote in the DB must not have any connection to the person. So, you give the person a flag whether or not they voted already, and store the vote separately. Now, you'd want to set both values in the same transaction for obvious reasons. Bu…
The other problem is that PostgreSQL doesn't rewrite data in place, so an attacker can determine the order of the user votes from the physical order of data in the database, and the order of issue votes from constantly scraping your website, thus allowing to deanonimize everything.
You need to use another database for this, specifically one designed to always overwrite data in place, and erase the WAL immediately after commit: it should be easy to write it yourself, assuming the dataset fits in RAM and so you don't need any data structures on disk other than a simple array of records.
Also you need to ensure that higher storage layers don't keep snapshots and don't do copy-on-write.
Could also look into a cryptography-based solution, although not sure if there is a feasible one.
Re: PostgreSQL's Imperfections
#88Criticism is valid, but he talks about cases of millions connections to a single db, that is a significant scale many companies will never see. In addition to that, probably no database can serve under significant load without careful tuning, preferably with understanding of DB internals and knowing compromises DB authors took when designin it. PostgreSQL is constantly improving. At least some of the problems with sc…
I'll trust postgresql more when it can support a few thousand connections without resorting to running middleware (pgbouncer) all over. That was his point. PostgreSQL is just abysmally bad in this area.
The process per connection model works great for "my first rails project" so every developer brings it to $dayjob. Then they are caught off guard when they start getting real traffic. It's terrifying to watch a couple hundred connections take a moderately sized server (~100 threads) down the native_queued_spin_lock_slowpath path to ruin. That's just sad.
Re: PostgreSQL's Imperfections
#89I'm a humble web developer and I'm not very knowledgeable about databases. I am glad I deal with an ORM for both personal and work projects instead relying on database specifics. That way, the app is DB agnostic and I can switch the database with ease. If your resource are limited, I think that is good. When you have the resources, it's better to hire an architect and a DBA to tell you what DB to use and maintain it.
I can switch the database with ease
In my experience an organisation is far, far more likely to switch operating systems or hardware platforms or programming languages than they are the database. But no programmer bothers to code in a clever but restricted syntax that would be a valid program in both C# and Java. Or restricts themselves to a core set of OS features or hardware instructions just in case. It really is quite bizarre to watch.
Re: PostgreSQL's Imperfections
#90Earlier quoted context omitted.
Pg uses 8kb pages by default. You do really want your fs/db page size to match though except in very very specific scenarios.
Why doesn't pg query the fs for the default scenario I wonder
Because it's a compile time flag, not a init or config parameter.
I agree it would be nice if the page size was more adaptive to just not have FS page size alignment issues.