Transcript PostgreSQL - DrupalCamp MSK 2014

PostgreSQL
Bye, the dolphin. Hello, the elephant.
Kate Marshalkina
[email protected]
@kalabro
PostgreSQL
Пост-Грес-Кью-Эль или просто Постгрес
Faster, better, stronger
• PostgreSQL 100500x faster that MySQL and Oracle DB
• 200k times more reliable
• 13 times more secure
• 221 times cheaper
• ...∞
Faster, better, stronger
• PostgreSQL 100500x faster that MySQL and Oracle DB
• 200k times more reliable
• 13 times more secure
• 221 times cheaper
• ...∞
???
Well, I don’t know.
> whoami
Kate Marshalkina, Senior Web Developer (not a DBA!)
6 year of PHP, 3 years 4 months * with Drupal + Postgres
https://www.drupal.org/u/kalabro and @kalabro
[email protected]
* According to Drupal.org. 5 years 27 weeks according to Drupal.ru.
Agenda
• About PostgreSQL
• Install Drupal + PostgreSQL
• Discover why it works (Drupal DBAL)
• The most important slide #3
• Who you are in Drupal dev world and what to do with it?
• Links
Who uses PostgreSQL
PostgreSQL
• Object-relational
• True open source
• Follows standards (ANSI-SQL, ACID)
• MVCC
• Sophisticated query planner/optimizer
• Replication
PostgreSQL (advanced)
• Inherited tables
• Huge amount of build-in data types + custom types
• Advanced indexes (compound, unique, partial, functional)
• Stored procedures
• Full text search (FTS)
• JSON(B)
• Extensions (hstore, PostGIS, dblink, …)
http://www.postgresql.org/docs/9.3/static/contrib.html
PostgreSQL Feature Matrix
http://www.postgresql.org/about/featurematrix/
Latest stable: 9.3
9.4 RC1 released
JSON?
Simple Key=Value (hstore) supported since 8.3
Full JSON (jsonb) with indexes — in PostgreSQL 9.4!
Thanks to dev team:
Олег Бартунов, Александр Коротков, Федор Сигаев
PostgreSQL grows fast
http://momjian.us/main/blogs/pgblog/2014.html#November_11_2014
Install PostgreSQL + Drupal
1. Install Postgres
The most difficult part: pg_hba.conf
2. Run pgtune (optionally)
3. Install php5-pgsql
4. Install Drupal
Manage PostgreSQL
CLI: psql
UI: pgAdmin, phppgadmin
First time vocabulary
• User postgres
sudo -u postgres psql
• Templates for databases
• Schema
• Autovacuum
• ~/.pgpass
SA-CORE-2014-005
Database abstraction layer
standard, vendor-agnostic abstraction layer for accessing database servers
Example: schema support in pgsql/schema.inc
public function queryTableInformation($table) {
// Generate a key to reference this table's information on.
$key = $this->connection->prefixTables('{' . $table . '}');
if (!strpos($key, '.')) {
$key = 'public.' . $key;
}
// ….
Database API & EFQ works with any DB
PostgreSQL driver problems
1. MySQL
MySQL vs PostgreSQL Drupal benchmarks?
Well-tuned MySQL is often quicker in synthetic tests.
http://posulliv.github.io/2012/06/29/mysql-postgres-bench/
«Certainly, from a performance point of view, I see no issues with using
PostgreSQL with Drupal. In fact, for Drupal sites using the Views module
(probably the majority), I would say PostgreSQL is probably even a better
option than MySQL due to its more advanced optimizer and execution
engine.»
So, what’s next for you?
For Project Architect / Team Lead
PostgreSQL is an open source level BDMS with
huge amount of features.
Consider PostgreSQL for quick start and continuous
growth.
It competes proprietary giants like Oracle DB and
smells like Enterprise for your business.
For students / fellow developers
PostgreSQL is a great choice if you’ve just finished
your Database course. It respects standards and has
a lot of cool features for your night time.
As PostgreSQL market is full of highload projects
and continuously grows.
For Drupal Developers
Avoid MySQLisms.
• db_query()/addExpression()/…
• Use Database API in contribs
• Learn new staff by contributing to both Drupal and Postgres
• INSTALL IT ON DEV! It’s simple.
For PostgreSQL DBA
• Drupal dislikes logic outside of itself.
• Optimize queries and cache.
• Integrate DB views with Drupal API.
PostgreSQL meets Drupal
• http://planet.postgresql.org/
• http://www.meetup.com/postgresqlrussia/
• http://postgresmen.ru/articles/postgresql-users-russia-2014
• http://prodrupal.ru/ru/node/129
• https://www.drupal.org/node/555514
• http://pgconf.ru/
sudo apt-get install postgresql
by Kate Marshalkina
[email protected]
@kalabro