PostgreSQL and ZFS is a marriage made in heaven. Block aligns to disk behaviour, snapshots make great low latency db dumps trivial. Never regretted making the combo
PostgreSQL is the worlds’ best database
51–60 of 365 posts
Re: PostgreSQL is the worlds’ best database
#52PostgreSQL is great if you: a) are OK with using SQL (this is not obvious) b) do not need a distributed database I've spent a lot of time on looking at database solutions recently, reading through Jepsen reports and thinking about software architectures. The problem with PostgreSQL is that it is essentially a single-point-of-failure solution (yes, I do know about various replication scenarios and solutions, in fact I…
This is why I'm always looking at the new cool databases on the block before going back to postgres.
The hard truth is that postgres is more annoying to operate than a lot of the modern alternatives when you need HA.
Re: PostgreSQL is the worlds’ best database
#53PostgreSQL is great if you: a) are OK with using SQL (this is not obvious) b) do not need a distributed database I've spent a lot of time on looking at database solutions recently, reading through Jepsen reports and thinking about software architectures. The problem with PostgreSQL is that it is essentially a single-point-of-failure solution (yes, I do know about various replication scenarios and solutions, in fact I…
So what do you suggest then? Do you really care that much about the language? Shouldn't you care more about your data? If you are not OK with SQL there are abstractions available.
[deleted]
Re: PostgreSQL is the worlds’ best database
#54Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-…
> I haven't seen anybody maintain the grants correctly in production for non-admin read-only users.
Your opinion against mine, but in my last project I successfully did this. I didn't even have to think about it; the client asked me if I could introduce a role for pure-read-only access and it was done in a few minutes. It helped that I'd already set up all objects, roles, and grants for non-admin access.
OTOH, the privileges system in postgres has always worked when and how I've wanted it to work, unlike my experience with MySQL.
----
> Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things.
> Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO
From the docs:
7.1 to 9.2: "COPY naming a file is only allowed to database superusers, since it allows writing on any file that the backend has privileges to write on." (Note that there was no support for PROGRAM in these versions, which was introduced in 9.3, and thus the docs changed to ...)
9.3 to 10: "COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access." (and after this your request for grantable control was, well, granted ...)
11, 12: "COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access."
And, of course, there's the classic combo of functions with SECURITY DEFINER and EXECUTE grants. For which, again, the doc has always advised: "For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it."
----
> Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid.
I'm afraid I couldn't find any info about this thing you mention; and frankly, it's a pretty bold claim. Could you elaborate?
Again, your opinion against mine (and many others'), but the "vaunted" engineering (and _design_) behind Postgres is solid on many fronts, from our experience of running it and using it in many contexts, including security.
Re: PostgreSQL is the worlds’ best database
#55I’m assuming anyone who has to make that decision already knows this but, while PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale, or to train or store your machine learning features. It works, but it’s not great at scale.
You can get away with having a scheduled pg_dump early on, some reports on that, while you figure out an ETL/Messaging process — but picking something that handles concurrent large-scale queries will matter fast.
Re: PostgreSQL is the worlds’ best database
#56Having migrated a postgres database to dynamodb, I would say that I'm never going to use postgres again.
The problem with all SQL databases is that they are too easy to query and use. You add all kinds of select queries, joins and foreign keys and when traffic hits scramble to make it scale. NoSQL is hard to design but you can atleast be sure that once traffic hits, you don't have to redesign the schema to make it scale.
Re: PostgreSQL is the worlds’ best database
#57This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.
The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new…
Do these satisfy the compression requirement?
https://stackoverflow.com/questions/1369864/does-postgresql-...
Re: PostgreSQL is the worlds’ best database
#58I’m assuming anyone who has to make that decision already knows this but, while PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale, or to train or store your machine learning features. It works, but it’s not great at scale. You can get away with having a scheduled pg_dump early on, some reports on that, while you figure out an ETL/Messaging process — but picki…
Could you name some better alternatives for analytical db?
Re: PostgreSQL is the worlds’ best database
#59Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-…
Grant model is little outdated in some aspects, but with right tools it is doable. On some level you just accept that you need to read documentation and follow it. It is just another thing that you need to care about. In case if you need it.
I can little imagine case when you have access to the system and install dumps from untrusted sources. Probably it is world of untrusted PHP scripts from russian forums with stollen software. It is the world full of whole specter of pains. But I’m not agree that it should be considered as weakness. You never should restore dumps from untrusted sources ever. Such dumps can contains stored procedures that can contain code in pl/python that can do a lot of shady things. Is it a weakness or advantage of having freedom of using of python? In the world of script kiddies it is, but it is not problem of the Postgres.
Re: PostgreSQL is the worlds’ best database
#60Having migrated a postgres database to dynamodb, I would say that I'm never going to use postgres again. The problem with all SQL databases is that they are too easy to query and use. You add all kinds of select queries, joins and foreign keys and when traffic hits scramble to make it scale. NoSQL is hard to design but you can atleast be sure that once traffic hits, you don't have to redesign the schema to make it sc…
> You add all kinds of select queries, joins and foreign keys and when traffic hits scramble to make it scale. NoSQL is hard to design but you can atleast be sure that once traffic hits, you don't have to redesign the schema to make it scale.
Surely this depends on how you set up your SQL database to begin with? I'm not familiar with NoSQL, so can you explain why "schemas" aren't necessary and scaling happens automatically?