As for number 5 - Postgres already does that. This feature is called "constraints". Such constraints can be applied to multiple columns and are used to build better query plans (as well as limiting DML operations)
Even a single rare rollback can kill a prod DB. We had a situation when a bug in our code caused a series of huge UPDATEs in Oracle DB and LOCKed some critical tables. After ~2 hours we found this session and killed it.…
There is not suck thing as "NoSQL store". NoSQL is just a buzzword that is used to describe dozens (hundreds?) of very different solutions.
Google "Postgres BDR". It is new, but it allows you to replicate changes on master to another master. With some configuration it can even change replicated data.
Any SQL DBMS is able to store and handle NUMERIC data type. It is a binary decimal with any precision you may want. Some of the DBMS's also have dedicated currency types.
Backup: http://www.postgresql.org/docs/current/static/app-pgdump.htm... In short: pg_dump -Fc mydb > db.dump Restore: http://www.postgresql.org/docs/current/static/app-pgrestore.... In short: pg_restore -C -d postgres…
Easy. Just use dblink or foreign table to execute part of the statement inside other transaction. dblink to the same server/db is often used as way to create an "AUTONOMOUS_TRANSACTION"
Why `nohup postgres 2>&1 > postgres.log &` instead of `pg_ctl start`? You could also use `pg_ctl -w start` to wait for proper server startup instead of `sleep 2`
"modern development frameworks can handle data integrity stuff". Wrong. They cant. If you have 2+ applications working with the same db, there will be problems with data integrity that no "modern development framework"…
"one view per user" - you can have a single VIEW, that filters rows based on user name ("owner_user_name" column in the base table). Or use some kind of mapping table for "user_name" <-> "table PK id". It gets more…
There are `security_barrier` VIEWs. They give a "way of restricting a given connection/user to only rows with the right foreign key in a table". Still you have to create them all (not a simple task for complex DB…
Postgres has transactional DDL and DML. If anything fails - everything is rolled back.
Hm. "rows actually not being written to the db during normal circumstances". A database that acts like /dev/null ? Like BLACKHOLE MySQL storage engine? I can not imagine a real use case, where it will be needed. If you…
There is Asynchronous Commit feature in postgres, that allows you to gain performance by allowing postgres to lose some commited transactions. It can be controlled on transaction level and is exactly :"Go ahead, lose…
phppgadmin?
> 3) Better backup options. Already exist as "Continuous Archiving". Was done by third party tools in version 8, part of the core in 9. Details: http://www.postgresql.org/docs/current/static/continuous-arc... > 4)…
Did you use default postgres configuration? By default postgres uses very small amount of RAM and is forced to store hash maps on disk -> very significant slowdown.
As for number 5 - Postgres already does that. This feature is called "constraints". Such constraints can be applied to multiple columns and are used to build better query plans (as well as limiting DML operations)
Even a single rare rollback can kill a prod DB. We had a situation when a bug in our code caused a series of huge UPDATEs in Oracle DB and LOCKed some critical tables. After ~2 hours we found this session and killed it.…
There is not suck thing as "NoSQL store". NoSQL is just a buzzword that is used to describe dozens (hundreds?) of very different solutions.
Google "Postgres BDR". It is new, but it allows you to replicate changes on master to another master. With some configuration it can even change replicated data.
Any SQL DBMS is able to store and handle NUMERIC data type. It is a binary decimal with any precision you may want. Some of the DBMS's also have dedicated currency types.
Backup: http://www.postgresql.org/docs/current/static/app-pgdump.htm... In short: pg_dump -Fc mydb > db.dump Restore: http://www.postgresql.org/docs/current/static/app-pgrestore.... In short: pg_restore -C -d postgres…
Easy. Just use dblink or foreign table to execute part of the statement inside other transaction. dblink to the same server/db is often used as way to create an "AUTONOMOUS_TRANSACTION"
Why `nohup postgres 2>&1 > postgres.log &` instead of `pg_ctl start`? You could also use `pg_ctl -w start` to wait for proper server startup instead of `sleep 2`
"modern development frameworks can handle data integrity stuff". Wrong. They cant. If you have 2+ applications working with the same db, there will be problems with data integrity that no "modern development framework"…
"one view per user" - you can have a single VIEW, that filters rows based on user name ("owner_user_name" column in the base table). Or use some kind of mapping table for "user_name" <-> "table PK id". It gets more…
There are `security_barrier` VIEWs. They give a "way of restricting a given connection/user to only rows with the right foreign key in a table". Still you have to create them all (not a simple task for complex DB…
Postgres has transactional DDL and DML. If anything fails - everything is rolled back.
Hm. "rows actually not being written to the db during normal circumstances". A database that acts like /dev/null ? Like BLACKHOLE MySQL storage engine? I can not imagine a real use case, where it will be needed. If you…
There is Asynchronous Commit feature in postgres, that allows you to gain performance by allowing postgres to lose some commited transactions. It can be controlled on transaction level and is exactly :"Go ahead, lose…
phppgadmin?
> 3) Better backup options. Already exist as "Continuous Archiving". Was done by third party tools in version 8, part of the core in 9. Details: http://www.postgresql.org/docs/current/static/continuous-arc... > 4)…
Did you use default postgres configuration? By default postgres uses very small amount of RAM and is forced to store hash maps on disk -> very significant slowdown.