13 ms·

PostgreSQL's Imperfections

331 points by chmaynard 7y ago

osrec 7y ago
Not sure if this makes sense, however, I've always preferred MariaDB to Postgres because it feels lighter. And I haven't really come up against any significant limitations in MariaDB that would make me want to switch to Postgres.
- philliphaydon 7y ago
  Does MariaDB have documentation?
  - osrec 7y ago
    https://mariadb.com/kb/en/documentation/ https://mariadb.com/kb/en/documentation/
    - philliphaydon 7y ago
      Ahh under server documentation. The site could use some TLC. Not easy to navigate. Thanks.
- edoceo 7y ago
  Heavy writes, mulitstep transactions is where PG has always won for me vs Maria and MySQL - it was even more pronounced when I decided I'm all in on PG c2003
  - redis_mlc 7y ago
    1) 2003 was 17 years ago. 2) PG has a write amplification problem with multiple indexes that MySQL Innodb doesn't have.
    - asah 7y ago
      I'm curious how innodb solves this, given that the theory of indices pretty much demands write amplification by definition. Postgres' WA is worse?
      - edoceo 7y ago
        Its from an Uber post - more story and follow up in this post https://dba.stackexchange.com/questions/226879/did-postgresql-write-amplification-reduction-warm-efforts-get-released-in-10-1 https://dba.stackexchange.com/questions/226879/did-postgresq...
        djd20 7y ago
        Yeah - they indexed every single column.... also - what may not have worked for uber, will be just fine for 99% of usecases. How often do you work with that kind of load. What they moved to isn't so much mysql, rather mysql used as a key value store with 0 relational database usage. Not really a postgres issue, rather one of being too big for relational databases.
    - edoceo 7y ago
      2003 was when I picked PG as preferred. Since then I've continues to work with DBs and of course evaluated Maria at least every 18 months (or a client was paying me to do this). PG continues to win for nearly every case I throw at it. Like when NoSQL was a fad (why tho!?) but PG had native json, then jsonb types. I didn't even have to look at that silly Mongo, PG baba!
    - djd20 7y ago
      You have a problem if you're trying to index every column...
- stickfigure 7y ago
  Transactional DDL. I for the life of me cannot understand how anyone survives in production without this. Half-applied migrations will prevent you from both deploying new code and rolling back to the old. Expect significant downtime.
jeffdavis 7y ago
I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The next point, on planner hints: it's really just something that hasn't been done. If a few engineers made plans to tackle the problem, a lot could be done in a couple releases' worth of work. In the mean time, people are getting by with various half-measures anyway, such as extensions[1], planner tunables[2], and statistics tweaks[3]. The only dogma is that a half-baked solution isn't wanted. It's got a lot of architectural impact and long-term supportability implications. And a lot of different use cases that need to be considered that may drive different technological solutions. "Make plans stable/managable" is a different use case than "I know something the planner doesn't" which is different from "Make the planner do this thing because I said so". [1] https://github.com/ossc-db/pg_hint_plan/blob/master/doc/pg_hint_plan.html https://github.com/ossc-db/pg_hint_plan/blob/master/doc/pg_h... [2] https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... [3] https://www.postgresql.org/docs/12/sql-createstatistics.html https://www.postgresql.org/docs/12/sql-createstatistics.html
- DiabloD3 7y ago
  Hardware corruption can happen anywhere to anything. If you care about your data: Use ECC, and use a checksumming filesystem like ZFS, and also on top of this all, export your WALs to a second machine.
  - jeffdavis 7y ago
    Well, I'm asking specifically if the author saw corruption that snuck past the checksumming features that postgres has. Extra layers are always good, but since I was one of the main authors of checksums in Postgres, I'd like to know if there's room for improvement. (Aside: the page checksum is only 16 bits, so if you have frequent corruption it's entirely believeable that a few sneak past. But I haven't seen it personally.)
- ptman 7y ago
  Also, pg has initdb --data-checksums
deleted 7y ago
[deleted]
Mountain_Skies 7y ago
Anyone else getting a 500 error for the link?
- andy_ppp 7y ago
  Hopefully a database problem :-/
mamcx 7y ago
Ok, I have mine: When declare a custom type with a check, the error not show the row/table that cause the problem, only that something happen: CREATE DOMAIN TEXTN AS TEXT CONSTRAINT non_empty CHECK (length(VALUE) > 0); However, doing the check inline show the error in full. This cause me to rewrite all the tables, twice (one adding the new type thinking will help, once again inlining everything).
topspin 7y ago
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 no perfect query planners. There is no perfect analysis. The notion that one must submit entirely to the mercy of PostgreSQL's no-hints dogma causes me to harbor some resentment. Fortunately you can frequently abuse CTEs to achieve a desired access pattern because (until recently) CTEs were an "optimization fence." But I've also resorted to creating functions and other hacks. So I conclude the policy is simply wrong headed; there is no legitimate reason to fear hint abuse and the premise that hints aren't necessary is false.
- hibikir 7y ago
  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 characteristics had little to do with the previous servers, a vast majority of hinted queries were very wrong, leading to months of developers rebuilding every query, despite having no schema changes. So yes, a lot of people in the industry have been burned by hints to make the Postgres perspective understandable. At the same time, this doesn't make it good: Postgres' default settings over the years have lead to specific kinds of tables requiring extra love and care to make the query planner not do silly things. The most traditional failure case being a transaction table with an always increasing timestamp, where a vast majority of queries only care about today: The traditional thing to do was to convince Postgres that yes, this table needs very frequent stats recalculation, as to make it learn that there are more than 3 rows today, so nested loops will not do. Whether the Postgres quirks are better or worse than hint hell, I am still not sure of: A competent engineer can handle things either way.
  - raverbashing 7y ago
animalnewbie 7y ago
Anyone got postgres (or general db) tips for a CoW-fs? I'm using postgres on zfs.
- p_l 7y ago
  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.
  - Tostino 7y ago
    Pg uses 8kb pages by default. You do really want your fs/db page size to match though except in very very specific scenarios.
    - animalnewbie 7y ago
      Why doesn't pg query the fs for the default scenario I wonder
      - Tostino 7y ago
        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.
    - p_l 7y ago
      Ahh, I didn't know it used 8kB page - that makes it even better, because it removes any read-modify-write cycle if you run with record size of 8k.
  - yjftsjthsd-h 7y ago
    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.
phamilton 7y ago
Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.
- MuffinFlavored 7y ago
  How much do you pay a month for what size?
  - phamilton 7y ago
    Cost is complicated because we autoscale from 2-3 readers up 10+ readers during peak traffic. Instead of running 10 replicas 24/7, we can spin one up and within about 10 minutes it's handling reads. So we have a few instances that only run for about 3 hours a day and others that run maybe 6 hours. That is a big cost savings over running them all 24 hours a day. We couldn't bring up new replicas like that when we were on normal Postgres RDS. Pricing is also surprising compared to vanilla Postgres RDS because reader nodes double as spare writers. A multi-az deployment of Postgres RDS plus two single-az replicas is more expensive than a 3 node (1 writer and two readers) Aurora cluster. E.g. on 2xlarge instances, this Aurora setup is $3.48/hour vs $4/hr on RDS for similar effective hardware and fault tolerance. Running directly on EC2 is going to be much cheaper obviously. $1.51/hr for three 2xlarge instances(if you want to failover to an active replica) or $2.01/hr for 4 if you want a dedicated failover instance (like RDS does).
    - agacera 7y ago
      Aurora is indeed amazing. However, my experience with it regarding costs is that main cost you have running it is not compute, but IO ($0.2 per million requets [1]). This cost is kind of hidden since to estimate this in the early stages of a project is an art. In one project on my team the IO cost is about 8x more than cost of instances. But imo it is still worth and I never actually calculated how much we would pay if we were running on RDS + provisioned IOPS.
      - 7y ago
rb808 7y ago
Its scary that my company is replacing old Oracle DBs and teams of specialist DBAs with developers running their own PostSQL instances. Sure it saves money but its gonna blow one day soon...
- Tostino 7y ago
  Getting rid of Oracle, fine... But getting rid of specialists is probably not the smartest move. I don't think Postgres will have anything to do with the problems your company may face.
- skyde 7y ago
  is it one postSQL per app replacing many app on a single Oracle instance ? moving away from some sharing fix most of the problem DBA are hired to mitigate
rishav_sharan 7y ago
As a not very tech savvy person, object/hierarchical data querying in pg is horrible. The query syntax is alien and just feels tacked on.
- asah 7y ago
  What do you consider natural? It's not hard to add something else.
- Tostino 7y ago
  Mind giving an example?
KingOfCoders 7y ago
The 'best database' article recently linked here, says "PostgreSQL picks a method of concurrency control that works best for high INSERT and SELECT workloads. [...] tracking overhead for UPDATE and DELETE." This one says "INSERT and UPDATE operations create new copies (or “row versions”) of any modified rows, leaving the old versions on disk until they can be cleaned up." I do think this one is wrong, but this is my wild guess as I am no expert in any way. INSERT should be fine. Or how would INSERT "create new copies"? [Edit] See authors comment.
- rbranson 7y ago
  Author here. This should not have included INSERT. I updated the post to reflect that. Thanks for picking this up.
  - KingOfCoders 7y ago
    Thanks for your fast reply, much appreciated.
jedberg 7y ago
I agree with everything on this list. Been bit by most of these at one point or another. XID wraparound was the worst. Ironically we avoided a lot of the replication bugs by accidentally deciding to use logical replication from the start, but that of course brought in a whole different set of bugs instead. I'm surprised there wasn't a complaint about the vacuumer. That was probably my biggest single pain of running a large active cluster. There was never a good time to vacuum, but if you skipped it, it eventually happened automatically, usually at the worst possible time, when the database was most active. To be fair I haven't managed postgres since 8.3, so maybe that got better?
- Tostino 7y ago
  It's pretty darn different than 8.3 today.
Doctor_Fegg 7y ago
> 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 storage. Each value takes up 4 bytes. A single-byte enum would vastly reduce my database size.
- yardstick 7y ago
  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.
  - yjftsjthsd-h 7y ago
    > 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 https://github.com/docker-library/postgres/issues/37).
  - nasalgoat 7y ago
    I've found that it's incompatible with the PostGIS extension due to version differences. I'm still on 9.5 because I can't afford the downtime to properly migrate.
- sho 7y ago
  Homebrew added an extremely useful `brew postgresql-upgrade-database` option which will handle that. You do have to remember to run it separately. By the way, I used to have your encyclopaedia of all world knowledge
- int_19h 7y ago
  This sounds like an issue with Homebrew, to some extent. On Debian, every major Postgres version gets its own package.
deleted 7y ago
[deleted]
rossmohax 7y ago
Criticism 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 scaling with number of connections have more to do with locking rather than process-per-connection architecture, it is being worked on with impressive results doubling number of transactions per second for 200 connections: https://www.postgresql.org/message-id/20200301084638.7hfktq4sh2mcnuen%40alap3.anarazel.de https://www.postgresql.org/message-id/20200301084638.7hfktq4...
- scurvy 7y ago
  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.
  - anarazel 7y ago
    > 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. Depending on your workload it's entirely possible to run PG with 2000 connections. The most important thing is to configure postgres / the operating system to use huge pages, that gets rid of a good bit of the overhead. If the workload has a lot of quick queries it's pretty easy to hit scalability issues around snapshots (the metadata needed to make visibility determinations). It's not that bad on a single-socket server, but on 2+ sockets with high core counts it can be significant. We're working on it (I'm polishing the patch right now, actually :)). Here's an example graph https://twitter.com/AndresFreundTec/status/1234621534364241920 https://twitter.com/AndresFreundTec/status/12346215343642419... My local 2 socket workstation doesn't have enough cores to show the problem to the same degree unfortunately, so the above is from an azure VM. The odd dip in the middle is an issue with slow IPIs on azure VMs, and is worse when the benchmark client and server run on the same machine. > 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. Which spinlock was that on? I've seen a number of different ones over time. I've definitely hit ones in various drivers, and in both the generic parts of the unix socket and tcp stacks.
gregn610 7y ago
I'm surprised that no-one is complaining about the lack of Active Directory integration for authorization. #disclaimer - Author of an AD integration solution that never got off the ground. https://github.com/gregn610/padnag https://github.com/gregn610/padnag
- LunaSea 7y ago
  I think it's a bad idea for a database to start implementing third-part vendor related features. That's the type of feature that should be implemented as a plugin.
  - nijave 7y ago
    You could just call it "ldap authentication". AD comes with an LDAP interface.
    - jtdev 7y ago
      Yes, and that AD LDAP interface is riddled with MS specific deviations that require complying with MS’s way of doing LDAP.
    - fanf2 7y ago
      Or Kerberos authentication (which AD also supports) https://www.postgresql.org/docs/current/gssapi-auth.html https://www.postgresql.org/docs/current/gssapi-auth.html
    - anarazel 7y ago
      Postgres does have ldap based auth, and also can authenticate against AD using sspi/gssapi. The problem with that is that it requires users to have been created inside postgres first, and that you can't manage group membership inside AD.
      - gregn610 7y ago
        yup, that's what I meant by authorisation, keeping the roles and groups in pgsql up to date.
karyon 7y ago
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. 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. There seems to be no way to instruct postgres to "clean" those XIDs in any way. What we're doing now is periodically and manually updating every tuple in each affected table with dummy changes, essentially duplicating all tuples with all new XIDs, and then running VACUUM to delete the tuples with the old, potentially-deanonymizing XIDs. We haven't found anything easier...
- rossmohax 7y ago
  > There seems to be no way to instruct postgres to "clean" those XIDs in any way There is, VACUUM FREEZE
  - karyon 7y ago
    Sadly, since 9.4, that does not clear the XID anymore, see that blue box here: https://www.postgresql.org/docs/9.4/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND https://www.postgresql.org/docs/9.4/routine-vacuuming.html#V...
    - rossmohax 7y ago
      Thanks, that was news to me. Interestingly replacing XID with frozen one still makes a valid tuple, so it might be just a few lines patch for you to get what you want.
- dragonwriter 7y ago
  > 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 the "periodic and manual" process you are currently doing automatic and integrated with the "real" transactions rather than additional side process.) Alternatively, you don't do the changes in the same database transaction but in the same business domain transaction which is managed outside the database, and where any database artifacts related to the management of the business transaction are deleted and vacuumed after the transaction is completed.
DeathArrow 7y ago
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.
- gizmondo 7y ago
  If your app can be DB agnostic, then what DB you use doesn't matter, so you will never need to switch it. Also you don't need an "architect" and a "DBA" to know how to use databases properly.
  - pinkfoot 7y ago
    Er, once you know how to use databases properly, you are a DBA.
    - vp8989 7y ago
      They are 2 distinct skillsets: #1 - The administration of a database system #2 - Being able to write code that uses said system effectively If you don't have skillset #2, you are going to design and build bad systems and eventually a DBA will need to bail you out. The trend is that companies are reducing the number of DBAs on the payroll because of things like AWS Aurora, so you had better get skillset #2. And why wouldn't you want it? It's like knife skills for a chef. You should know your tools inside out.
  - hacker_newz 7y ago
    You definitely need a DBA to optimally set up a production database.
- goatinaboat 7y ago
  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.
SigmundA 7y ago
Great list I would add one thing to it: No query plan caching not even for sprocs or functions. Was surprised by this one, looks like the optimizer is much simpler than other db's so it usually take less time to create to the plan but the overhead is still there. This is why you see the recommendation to use prepared statements and many client libraries try to automatically, but a prepared statement cannot be shared between sessions so its only good if your repeating the same statement over and over on the same connection. If your app calls the same statements over and over from different connections which most apps tend to do it can save significant overhead and reduce response times. It was pretty much mandatory to make sure you where using parameterised SQL or sprocs back in the day to make sure it was using a cached plan properly.
mrfusion 7y ago
Can 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.
- codegladiator 7y ago
  Why wouldn't an automated interface be able to give similar recommendations ?
  - no-s 7y ago
    >Why wouldn't an automated interface be able to give similar recommendations ? you being sarcastic? If only we had an automated interface to make decisions about what code to write, then we wouldn't need programmers. Look how well that turned out IRL. We don't really need many assembly language programmers any more, but now we have all these nifty new programming languages...
    - codegladiator 7y ago
      I am under the impression that all the combinations of all configurations would still be small enough to present neatly maybe in a wizard.
- pas 7y ago
  Percona does that, and they grew to a pretty decent size. If you can find enough clients and visit them every few weeks/months it can work.
codegladiator 7y ago
Wow this article and this thread is so full of useful information for getting started to ventur into DB management/engineering.
jakearmitage 7y ago
Does anyone know any good book with PostgreSQL tips and tricks and cool snippets? Coming from MySQL, I'm always finding exciting things: checks, date ranges and GIST, to_tsvector(title) @@ websearch_to_tsquery('foobar')...
mulander 7y ago
> Every time an on-disk database page (4KB) needs to be modified by a write operation, even just a single byte, a copy of the entire page, edited with the requested changes, is written to the write-ahead log (WAL). Physical streaming replication leverages this existing WAL infrastructure as a log of changes it streams to replicas. First, the PostgreSQL page size is 8KB and has been that since the beginning. The remaining part. According to PostgreSQL documentation[1] (on full page writes which decides if those are made), a copy of the entire page is only written fully to the WAL after the first modification of that page since the last checkpoint. Subsequent modifications will not result in full page writes to the WAL. So if you update a counter 3 times in sequence you won't get 3*8KB written to the WAL, instead you would get a single page dump and the remaining two would only log the row-level change which is much smaller[2]. This is further reduced by WAL compression[3] (reducing the segment usage) and by increasing the checkpointing interval which would reduce the amount of copies happening[4]. This irked me because it sounded like whatever you touch produces an 8KB copy of data and it seems to not be the case. [1] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-FULL-PAGE-WRITES https://www.postgresql.org/docs/11/runtime-config-wal.html#G... [2] - http://www.interdb.jp/pg/pgsql09.html http://www.interdb.jp/pg/pgsql09.html [3] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-WAL-COMPRESSION https://www.postgresql.org/docs/11/runtime-config-wal.html#G... [4] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT https://www.postgresql.org/docs/11/runtime-config-wal.html#G...
- anarazel 7y ago
  That is correct. And neither is a full page write logged if the page is initialized from scratch. And even without WAL compression, the "hole" in the middle of the page if the page is not full, is "compressed" out. That's not to say that FPWs are not a problem. The increase in WAL volume they can cause can be seriously problematic. One interesting thing is that they actually can often very significantly increase streaming replication / crash recovery performance. When replaying the incremental records the page needs to be read from the os/disk if the page is not in the postgres' page cache. But with FPWs we can seed the page cache contents with the page image. For the pretty common case where the number of pages written between two checkpoints fits into the cache, that can be a very serious performance advantage.
thayne 7y ago
why does postgresql use a process per connection? Is there some advantage to doing this over using threads for each connection (or even asynchronous connection handling)?
- int_19h 7y ago
  Postgres dates back to the era when multiple processes were the normal way of doing this kind of thing on Unix-like OSes, and POSIX threads were often poorly supported on free ones.

331 points by chmaynard 7y ago

Not sure if this makes sense, however, I've always preferred MariaDB to Postgres because it feels lighter. And I haven't really come up against any significant limitations in MariaDB that would make me want to switch to Postgres.

philliphaydon 7y ago
Does MariaDB have documentation?
- osrec 7y ago
  https://mariadb.com/kb/en/documentation/ https://mariadb.com/kb/en/documentation/
  - philliphaydon 7y ago
    Ahh under server documentation. The site could use some TLC. Not easy to navigate. Thanks.
edoceo 7y ago
Heavy writes, mulitstep transactions is where PG has always won for me vs Maria and MySQL - it was even more pronounced when I decided I'm all in on PG c2003
- redis_mlc 7y ago
  1) 2003 was 17 years ago. 2) PG has a write amplification problem with multiple indexes that MySQL Innodb doesn't have.
  - asah 7y ago
    I'm curious how innodb solves this, given that the theory of indices pretty much demands write amplification by definition. Postgres' WA is worse?
    - edoceo 7y ago
      Its from an Uber post - more story and follow up in this post https://dba.stackexchange.com/questions/226879/did-postgresql-write-amplification-reduction-warm-efforts-get-released-in-10-1 https://dba.stackexchange.com/questions/226879/did-postgresq...
      - djd20 7y ago
        Yeah - they indexed every single column.... also - what may not have worked for uber, will be just fine for 99% of usecases. How often do you work with that kind of load. What they moved to isn't so much mysql, rather mysql used as a key value store with 0 relational database usage. Not really a postgres issue, rather one of being too big for relational databases.
  - edoceo 7y ago
    2003 was when I picked PG as preferred. Since then I've continues to work with DBs and of course evaluated Maria at least every 18 months (or a client was paying me to do this). PG continues to win for nearly every case I throw at it. Like when NoSQL was a fad (why tho!?) but PG had native json, then jsonb types. I didn't even have to look at that silly Mongo, PG baba!
  - djd20 7y ago
    You have a problem if you're trying to index every column...
stickfigure 7y ago
Transactional DDL. I for the life of me cannot understand how anyone survives in production without this. Half-applied migrations will prevent you from both deploying new code and rolling back to the old. Expect significant downtime.

jeffdavis 7y ago

I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The next point, on planner hints: it's really just something that hasn't been done. If a few engineers made plans to tackle the problem, a lot could be done in a couple releases' worth of work. In the mean time, people are getting by with various half-measures anyway, such as extensions[1], planner tunables[2], and statistics tweaks[3]. The only dogma is that a half-baked solution isn't wanted. It's got a lot of architectural impact and long-term supportability implications. And a lot of different use cases that need to be considered that may drive different technological solutions. "Make plans stable/managable" is a different use case than "I know something the planner doesn't" which is different from "Make the planner do this thing because I said so". [1] https://github.com/ossc-db/pg_hint_plan/blob/master/doc/pg_hint_plan.html https://github.com/ossc-db/pg_hint_plan/blob/master/doc/pg_h... [2] https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... [3] https://www.postgresql.org/docs/12/sql-createstatistics.html https://www.postgresql.org/docs/12/sql-createstatistics.html

DiabloD3 7y ago
Hardware corruption can happen anywhere to anything. If you care about your data: Use ECC, and use a checksumming filesystem like ZFS, and also on top of this all, export your WALs to a second machine.
- jeffdavis 7y ago
  Well, I'm asking specifically if the author saw corruption that snuck past the checksumming features that postgres has. Extra layers are always good, but since I was one of the main authors of checksums in Postgres, I'd like to know if there's room for improvement. (Aside: the page checksum is only 16 bits, so if you have frequent corruption it's entirely believeable that a few sneak past. But I haven't seen it personally.)
ptman 7y ago
Also, pg has initdb --data-checksums

deleted 7y ago

[deleted]

Mountain_Skies 7y ago

Anyone else getting a 500 error for the link?

andy_ppp 7y ago
Hopefully a database problem :-/

mamcx 7y ago

Ok, I have mine: When declare a custom type with a check, the error not show the row/table that cause the problem, only that something happen: CREATE DOMAIN TEXTN AS TEXT CONSTRAINT non_empty CHECK (length(VALUE) > 0); However, doing the check inline show the error in full. This cause me to rewrite all the tables, twice (one adding the new type thinking will help, once again inlining everything).

topspin 7y ago

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 no perfect query planners. There is no perfect analysis. The notion that one must submit entirely to the mercy of PostgreSQL's no-hints dogma causes me to harbor some resentment. Fortunately you can frequently abuse CTEs to achieve a desired access pattern because (until recently) CTEs were an "optimization fence." But I've also resorted to creating functions and other hacks. So I conclude the policy is simply wrong headed; there is no legitimate reason to fear hint abuse and the premise that hints aren't necessary is false.

hibikir 7y ago
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 characteristics had little to do with the previous servers, a vast majority of hinted queries were very wrong, leading to months of developers rebuilding every query, despite having no schema changes. So yes, a lot of people in the industry have been burned by hints to make the Postgres perspective understandable. At the same time, this doesn't make it good: Postgres' default settings over the years have lead to specific kinds of tables requiring extra love and care to make the query planner not do silly things. The most traditional failure case being a transaction table with an always increasing timestamp, where a vast majority of queries only care about today: The traditional thing to do was to convince Postgres that yes, this table needs very frequent stats recalculation, as to make it learn that there are more than 3 rows today, so nested loops will not do. Whether the Postgres quirks are better or worse than hint hell, I am still not sure of: A competent engineer can handle things either way.
- raverbashing 7y ago

animalnewbie 7y ago

Anyone got postgres (or general db) tips for a CoW-fs? I'm using postgres on zfs.

p_l 7y ago
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.
- Tostino 7y ago
  Pg uses 8kb pages by default. You do really want your fs/db page size to match though except in very very specific scenarios.
  - animalnewbie 7y ago
    Why doesn't pg query the fs for the default scenario I wonder
    - Tostino 7y ago
      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.
  - p_l 7y ago
    Ahh, I didn't know it used 8kB page - that makes it even better, because it removes any read-modify-write cycle if you run with record size of 8k.
- yjftsjthsd-h 7y ago
  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.

phamilton 7y ago

Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.

MuffinFlavored 7y ago
How much do you pay a month for what size?
- phamilton 7y ago
  Cost is complicated because we autoscale from 2-3 readers up 10+ readers during peak traffic. Instead of running 10 replicas 24/7, we can spin one up and within about 10 minutes it's handling reads. So we have a few instances that only run for about 3 hours a day and others that run maybe 6 hours. That is a big cost savings over running them all 24 hours a day. We couldn't bring up new replicas like that when we were on normal Postgres RDS. Pricing is also surprising compared to vanilla Postgres RDS because reader nodes double as spare writers. A multi-az deployment of Postgres RDS plus two single-az replicas is more expensive than a 3 node (1 writer and two readers) Aurora cluster. E.g. on 2xlarge instances, this Aurora setup is $3.48/hour vs $4/hr on RDS for similar effective hardware and fault tolerance. Running directly on EC2 is going to be much cheaper obviously. $1.51/hr for three 2xlarge instances(if you want to failover to an active replica) or $2.01/hr for 4 if you want a dedicated failover instance (like RDS does).
  - agacera 7y ago
    Aurora is indeed amazing. However, my experience with it regarding costs is that main cost you have running it is not compute, but IO ($0.2 per million requets [1]). This cost is kind of hidden since to estimate this in the early stages of a project is an art. In one project on my team the IO cost is about 8x more than cost of instances. But imo it is still worth and I never actually calculated how much we would pay if we were running on RDS + provisioned IOPS.
    - 7y ago

rb808 7y ago

Its scary that my company is replacing old Oracle DBs and teams of specialist DBAs with developers running their own PostSQL instances. Sure it saves money but its gonna blow one day soon...

Tostino 7y ago
Getting rid of Oracle, fine... But getting rid of specialists is probably not the smartest move. I don't think Postgres will have anything to do with the problems your company may face.
skyde 7y ago
is it one postSQL per app replacing many app on a single Oracle instance ? moving away from some sharing fix most of the problem DBA are hired to mitigate

rishav_sharan 7y ago

As a not very tech savvy person, object/hierarchical data querying in pg is horrible. The query syntax is alien and just feels tacked on.

asah 7y ago
What do you consider natural? It's not hard to add something else.
Tostino 7y ago
Mind giving an example?

KingOfCoders 7y ago

The 'best database' article recently linked here, says "PostgreSQL picks a method of concurrency control that works best for high INSERT and SELECT workloads. [...] tracking overhead for UPDATE and DELETE." This one says "INSERT and UPDATE operations create new copies (or “row versions”) of any modified rows, leaving the old versions on disk until they can be cleaned up." I do think this one is wrong, but this is my wild guess as I am no expert in any way. INSERT should be fine. Or how would INSERT "create new copies"? [Edit] See authors comment.

rbranson 7y ago
Author here. This should not have included INSERT. I updated the post to reflect that. Thanks for picking this up.
- KingOfCoders 7y ago
  Thanks for your fast reply, much appreciated.

jedberg 7y ago

I agree with everything on this list. Been bit by most of these at one point or another. XID wraparound was the worst. Ironically we avoided a lot of the replication bugs by accidentally deciding to use logical replication from the start, but that of course brought in a whole different set of bugs instead. I'm surprised there wasn't a complaint about the vacuumer. That was probably my biggest single pain of running a large active cluster. There was never a good time to vacuum, but if you skipped it, it eventually happened automatically, usually at the worst possible time, when the database was most active. To be fair I haven't managed postgres since 8.3, so maybe that got better?

Tostino 7y ago
It's pretty darn different than 8.3 today.

Doctor_Fegg 7y ago

> 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 storage. Each value takes up 4 bytes. A single-byte enum would vastly reduce my database size.

yardstick 7y ago
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.
- yjftsjthsd-h 7y ago
  > 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 https://github.com/docker-library/postgres/issues/37).
- nasalgoat 7y ago
  I've found that it's incompatible with the PostGIS extension due to version differences. I'm still on 9.5 because I can't afford the downtime to properly migrate.
sho 7y ago
Homebrew added an extremely useful `brew postgresql-upgrade-database` option which will handle that. You do have to remember to run it separately. By the way, I used to have your encyclopaedia of all world knowledge
int_19h 7y ago
This sounds like an issue with Homebrew, to some extent. On Debian, every major Postgres version gets its own package.

deleted 7y ago

[deleted]

rossmohax 7y ago

Criticism 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 scaling with number of connections have more to do with locking rather than process-per-connection architecture, it is being worked on with impressive results doubling number of transactions per second for 200 connections: https://www.postgresql.org/message-id/20200301084638.7hfktq4sh2mcnuen%40alap3.anarazel.de https://www.postgresql.org/message-id/20200301084638.7hfktq4...

scurvy 7y ago
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.
- anarazel 7y ago
  > 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. Depending on your workload it's entirely possible to run PG with 2000 connections. The most important thing is to configure postgres / the operating system to use huge pages, that gets rid of a good bit of the overhead. If the workload has a lot of quick queries it's pretty easy to hit scalability issues around snapshots (the metadata needed to make visibility determinations). It's not that bad on a single-socket server, but on 2+ sockets with high core counts it can be significant. We're working on it (I'm polishing the patch right now, actually :)). Here's an example graph https://twitter.com/AndresFreundTec/status/1234621534364241920 https://twitter.com/AndresFreundTec/status/12346215343642419... My local 2 socket workstation doesn't have enough cores to show the problem to the same degree unfortunately, so the above is from an azure VM. The odd dip in the middle is an issue with slow IPIs on azure VMs, and is worse when the benchmark client and server run on the same machine. > 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. Which spinlock was that on? I've seen a number of different ones over time. I've definitely hit ones in various drivers, and in both the generic parts of the unix socket and tcp stacks.

gregn610 7y ago

I'm surprised that no-one is complaining about the lack of Active Directory integration for authorization. #disclaimer - Author of an AD integration solution that never got off the ground. https://github.com/gregn610/padnag https://github.com/gregn610/padnag

LunaSea 7y ago
I think it's a bad idea for a database to start implementing third-part vendor related features. That's the type of feature that should be implemented as a plugin.
- nijave 7y ago
  You could just call it "ldap authentication". AD comes with an LDAP interface.
  - jtdev 7y ago
    Yes, and that AD LDAP interface is riddled with MS specific deviations that require complying with MS’s way of doing LDAP.
  - fanf2 7y ago
    Or Kerberos authentication (which AD also supports) https://www.postgresql.org/docs/current/gssapi-auth.html https://www.postgresql.org/docs/current/gssapi-auth.html
  - anarazel 7y ago
    Postgres does have ldap based auth, and also can authenticate against AD using sspi/gssapi. The problem with that is that it requires users to have been created inside postgres first, and that you can't manage group membership inside AD.
    - gregn610 7y ago
      yup, that's what I meant by authorisation, keeping the roles and groups in pgsql up to date.

karyon 7y ago

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. 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. There seems to be no way to instruct postgres to "clean" those XIDs in any way. What we're doing now is periodically and manually updating every tuple in each affected table with dummy changes, essentially duplicating all tuples with all new XIDs, and then running VACUUM to delete the tuples with the old, potentially-deanonymizing XIDs. We haven't found anything easier...

rossmohax 7y ago
> There seems to be no way to instruct postgres to "clean" those XIDs in any way There is, VACUUM FREEZE
- karyon 7y ago
  Sadly, since 9.4, that does not clear the XID anymore, see that blue box here: https://www.postgresql.org/docs/9.4/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND https://www.postgresql.org/docs/9.4/routine-vacuuming.html#V...
  - rossmohax 7y ago
    Thanks, that was news to me. Interestingly replacing XID with frozen one still makes a valid tuple, so it might be just a few lines patch for you to get what you want.
dragonwriter 7y ago
> 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 the "periodic and manual" process you are currently doing automatic and integrated with the "real" transactions rather than additional side process.) Alternatively, you don't do the changes in the same database transaction but in the same business domain transaction which is managed outside the database, and where any database artifacts related to the management of the business transaction are deleted and vacuumed after the transaction is completed.

DeathArrow 7y ago

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.

gizmondo 7y ago
If your app can be DB agnostic, then what DB you use doesn't matter, so you will never need to switch it. Also you don't need an "architect" and a "DBA" to know how to use databases properly.
- pinkfoot 7y ago
  Er, once you know how to use databases properly, you are a DBA.
  - vp8989 7y ago
    They are 2 distinct skillsets: #1 - The administration of a database system #2 - Being able to write code that uses said system effectively If you don't have skillset #2, you are going to design and build bad systems and eventually a DBA will need to bail you out. The trend is that companies are reducing the number of DBAs on the payroll because of things like AWS Aurora, so you had better get skillset #2. And why wouldn't you want it? It's like knife skills for a chef. You should know your tools inside out.
- hacker_newz 7y ago
  You definitely need a DBA to optimally set up a production database.
goatinaboat 7y ago
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.

SigmundA 7y ago

Great list I would add one thing to it: No query plan caching not even for sprocs or functions. Was surprised by this one, looks like the optimizer is much simpler than other db's so it usually take less time to create to the plan but the overhead is still there. This is why you see the recommendation to use prepared statements and many client libraries try to automatically, but a prepared statement cannot be shared between sessions so its only good if your repeating the same statement over and over on the same connection. If your app calls the same statements over and over from different connections which most apps tend to do it can save significant overhead and reduce response times. It was pretty much mandatory to make sure you where using parameterised SQL or sprocs back in the day to make sure it was using a cached plan properly.

mrfusion 7y ago

Can 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.

codegladiator 7y ago
Why wouldn't an automated interface be able to give similar recommendations ?
- no-s 7y ago
  >Why wouldn't an automated interface be able to give similar recommendations ? you being sarcastic? If only we had an automated interface to make decisions about what code to write, then we wouldn't need programmers. Look how well that turned out IRL. We don't really need many assembly language programmers any more, but now we have all these nifty new programming languages...
  - codegladiator 7y ago
    I am under the impression that all the combinations of all configurations would still be small enough to present neatly maybe in a wizard.
pas 7y ago
Percona does that, and they grew to a pretty decent size. If you can find enough clients and visit them every few weeks/months it can work.

codegladiator 7y ago

Wow this article and this thread is so full of useful information for getting started to ventur into DB management/engineering.

jakearmitage 7y ago

Does anyone know any good book with PostgreSQL tips and tricks and cool snippets? Coming from MySQL, I'm always finding exciting things: checks, date ranges and GIST, to_tsvector(title) @@ websearch_to_tsquery('foobar')...

mulander 7y ago

> Every time an on-disk database page (4KB) needs to be modified by a write operation, even just a single byte, a copy of the entire page, edited with the requested changes, is written to the write-ahead log (WAL). Physical streaming replication leverages this existing WAL infrastructure as a log of changes it streams to replicas. First, the PostgreSQL page size is 8KB and has been that since the beginning. The remaining part. According to PostgreSQL documentation[1] (on full page writes which decides if those are made), a copy of the entire page is only written fully to the WAL after the first modification of that page since the last checkpoint. Subsequent modifications will not result in full page writes to the WAL. So if you update a counter 3 times in sequence you won't get 3*8KB written to the WAL, instead you would get a single page dump and the remaining two would only log the row-level change which is much smaller[2]. This is further reduced by WAL compression[3] (reducing the segment usage) and by increasing the checkpointing interval which would reduce the amount of copies happening[4]. This irked me because it sounded like whatever you touch produces an 8KB copy of data and it seems to not be the case. [1] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-FULL-PAGE-WRITES https://www.postgresql.org/docs/11/runtime-config-wal.html#G... [2] - http://www.interdb.jp/pg/pgsql09.html http://www.interdb.jp/pg/pgsql09.html [3] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-WAL-COMPRESSION https://www.postgresql.org/docs/11/runtime-config-wal.html#G... [4] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

anarazel 7y ago
That is correct. And neither is a full page write logged if the page is initialized from scratch. And even without WAL compression, the "hole" in the middle of the page if the page is not full, is "compressed" out. That's not to say that FPWs are not a problem. The increase in WAL volume they can cause can be seriously problematic. One interesting thing is that they actually can often very significantly increase streaming replication / crash recovery performance. When replaying the incremental records the page needs to be read from the os/disk if the page is not in the postgres' page cache. But with FPWs we can seed the page cache contents with the page image. For the pretty common case where the number of pages written between two checkpoints fits into the cache, that can be a very serious performance advantage.

thayne 7y ago

why does postgresql use a process per connection? Is there some advantage to doing this over using threads for each connection (or even asynchronous connection handling)?

int_19h 7y ago
Postgres dates back to the era when multiple processes were the normal way of doing this kind of thing on Unix-like OSes, and POSIX threads were often poorly supported on free ones.