Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL's Imperfections

medium.com

Post reply on HN
Print view View original

61–70 of 139 posts

Re: PostgreSQL's Imperfections

#61

Post by topspin »

DrJokepu wrote: post #31
topspin wrote: post #12

If 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…

A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.

Indeed. I too have resorted to implementing my own plan using temporary tables.

Easier to maintain though? I can't recall ever resorting to this technique and thinking it was a maintenance improvement. When what might have been a single query evolves into a facade to hide the temporary table gymnastics I always feel it's at least a maintenance setback.

Re: PostgreSQL's Imperfections

#62

Post by yardstick »

karyon wrote: post #57

There'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…

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 somewhere else, etc.

Re: PostgreSQL's Imperfections

#63

Post by karyon »

dragonwriter wrote: post #60
karyon wrote: post #57

There'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…

> Now, you'd want to set both values in the same transaction for obvious reasons. But, since Postgres uses MVCC, the two tuples that are added to the database both contain the same transaction ID (XID), so there's the connection between user and vote again. The simple solution is that at each change, you rewrite the whole election, not just the new votes, and clear out all outdated tuples (basically, that you make th…

Re "same business transaction": That would probably still allow for correlating the votes with the flags because they'll still be added with some XID, which are monotonically increasing. Of course, it would require more effort and probably even guesswork, but still :)

Rewriting the election in the same transaction sounds smarter than the periodic solution. I need to discuss that with the team, thanks!

Re: PostgreSQL's Imperfections

#64

Post by karyon »

yardstick wrote: post #62
karyon wrote: post #57

There'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…

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 database. When they're that far they could of course do all sorts of bad things, but most of those could probably be fixed with recreating the VM with a backup. Deanonymizing existing votes cannot be fixed :)

We also thought about hashing user IDs, but with any practical number of users in the database it would be possible for an attacker to just hash all existing user IDs and checking them all. So that would be more an obfuscation, making de-anonymization harder, but not impossible.

edit: we considered putting the user's password into that hash as well. that would also enable us to let the user edit their vote later, while still retaining anonymity. But then we'd need to ask them for their PW when voting, or we put a hash of the PW into their session data, and we'd need to restrict changing the PW until the election is over, and it seemed not worthwhile.

Re: PostgreSQL's Imperfections

#65

Post by [deleted] »

viraptor wrote: post #49
hibikir wrote: post #17

Earlier quoted context omitted.

While I won't say that the Postgres ideosyncrasies are correct here, I can tell you that your experience is not necessarily typical. In my career, hint abuse has always been rampant. From telcos to biotech companies, a high percentage of complex queries I had to interact with had hints in them. In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristic…

> In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers I don't get this point. If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? After all, the hints were there to overcome such issues already. It sounds like t…

[deleted]

Re: PostgreSQL's Imperfections

#66

Post by radiowave »

topspin wrote: post #61
DrJokepu wrote: post #31

Earlier quoted context omitted.

A series of smaller queries moving data through temporary tables is what I do when the query is too complicated for the query planner. It’s also easier to maintain than a giant query, with or without CTEs or hints.

Indeed. I too have resorted to implementing my own plan using temporary tables. Easier to maintain though? I can't recall ever resorting to this technique and thinking it was a maintenance improvement. When what might have been a single query evolves into a facade to hide the temporary table gymnastics I always feel it's at least a maintenance setback.

Yes, I regard temporary tables as a last-resort. Although, a sometimes very useful thing that this approach has going for it is that you can add indexes to those temporary tables which are much more specialized than what could be achieved by working solely with the indexes of the source tables, no matter how sophisticated the query plan.

Re: PostgreSQL's Imperfections

#67

Post by DeathArrow »

I'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.

Re: PostgreSQL's Imperfections

#68

Post by topspin »

hobs wrote: post #21
topspin wrote: post #12

If 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…

I see hint abuse constantly and everywhere in enterprise databases, it's really more about the lineage you work with.

> it's really more about the lineage you work with.

(disclaimer: I'm not entirely certain what 'lineage' means here, but I'll assume it refers to the nature of the products for which database schemas are created and the quality of the development process.)

In my case the lineage was two unrelated ERPs at consecutive employers and a variety of in-house applications. I don't recall ever seeing a hint in either ERP except in some "one off" upgrade operations. Both employers had development guidelines and peer review processes for bespoke work. They did not explicitly preclude hints but if you had been foolish enough to offer slapdash work -- abusive use of hints, for instance -- you wouldn't get very far.

That was my experience with hints. I don't doubt there are reckless people who abuse them. I just resent being denied an affordance because they exist.

Re: PostgreSQL's Imperfections

#69

Post by blattimwind »

orthoxerox wrote: post #56
topspin wrote: post #12

If 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…

I once couldn't convince Oracle planner to use the index at all. It was an index on the one-char status column and all the query had to do was to return the count of unauthorized rows. Almost always 100% of the rows were authorized, so no matter how many times you ran the analyzer on the table the planner remained convinced that the index was useless and opted for a full table scan instead, wasting minutes. I had to…

In postgres, queries involving aggregates on joins generally seem to result in join-then-aggregate, even if the join is entirely foreign-key-independent of the aggregate.

Re: PostgreSQL's Imperfections

#70

Post by foxhill »

topspin wrote: post #12

If 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…

so, i absolutely agree. i do find it odd that no one has mentioned pg_hint_plan[1]. we use it, and it is great.

[1]: https://pghintplan.osdn.jp/pg_hint_plan.html

Post reply on HN