Postgre SQL Bye the dolphin Hello the elephant
Postgre. SQL Bye, the dolphin. Hello, the elephant. Kate Marshalkina marshalkina@licel. ru @kalabro
Th Faster, better, stronger em os t i #2 m po rta • Postgre. SQL 100500 x faster that My. SQL and Oracle DB • 200 k times more reliable • 13 times more secure • 221 times cheaper • . . . ∞ nt sli de
Th Faster, better, stronger em os t i #2 m po rta • Postgre. SQL 100500 x faster that My. SQL and Oracle DB • 200 k times more reliable • 13 times more secure • 221 times cheaper • . . . ∞ ? ? ? Well, I don’t know. nt sli de
> 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 marshalkina@licel. ru * According to Drupal. org. 5 years 27 weeks according to Drupal. ru.
Agenda • About Postgre. SQL • Install Drupal + Postgre. SQL • 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 Postgre. SQL
Postgre. SQL • Object-relational • True open source • Follows standards (ANSI-SQL, ACID) • MVCC • Sophisticated query planner/optimizer • Replication
Postgre. SQL (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, Post. GIS, dblink, …) http: //www. postgresql. org/docs/9. 3/static/contrib. html
Postgre. SQL Feature Matrix http: //www. postgresql. org/about/featurematrix/
Latest stable: 9. 3 9. 4 RC 1 released
JSON? Simple Key=Value (hstore) supported since 8. 3 Full JSON (jsonb) with indexes — in Postgre. SQL 9. 4! Thanks to dev team: Олег Бартунов, Александр Коротков, Федор Сигаев
Postgre. SQL grows fast http: //momjian. us/main/blogs/pgblog/2014. html#November_11_2014
Install Postgre. SQL + Drupal 1. Install Postgres The most difficult part: pg_hba. conf 2. Run pgtune (optionally) 3. Install php 5 -pgsql 4. Install Drupal
Manage Postgre. SQL CLI: psql UI: pg. Admin, 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 query. Table. Information($table) { // Generate a key to reference this table's information on. $key = $this->connection->prefix. Tables('{'. $table. '}'); if (!strpos($key, '. ')) { $key = 'public. '. $key; } // ….
Database API & EFQ works with any DB
Postgre. SQL driver problems 1. My. SQL
Th em os t i #3 : ) m po rta nt sli de
My. SQL vs Postgre. SQL Drupal benchmarks? Well-tuned My. SQL 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 Postgre. SQL with Drupal. In fact, for Drupal sites using the Views module (probably the majority), I would say Postgre. SQL is probably even a better option than My. SQL due to its more advanced optimizer and execution engine. »
So, what’s next for you?
For Project Architect / Team Lead Postgre. SQL is an open source level BDMS with huge amount of features. Consider Postgre. SQL for quick start and continuous growth. It competes proprietary giants like Oracle DB and smells like Enterprise for your business.
For students / fellow developers Postgre. SQL 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 Postgre. SQL market is full of highload projects and continuously grows.
For Drupal Developers Avoid My. SQLisms. • db_query()/add. Expression()/… • Use Database API in contribs • Learn new staff by contributing to both Drupal and Postgres • INSTALL IT ON DEV! It’s simple.
For Postgre. SQL DBA • Drupal dislikes logic outside of itself. • Optimize queries and cache. • Integrate DB views with Drupal API.
Postgre. SQL 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 marshalkina@licel. ru @kalabro
- Slides: 32