Why Application Developers Love Postgres | Ignite 2019 | Craig Kerstiens

Postgres is ranked as one of the “most-loved” databases by developers—and is the frequent choice for developers building new applications whether on-premises or in the cloud. We look at what makes the open source Postgres database unique among relational databases, from foreign data wrappers to rich indexes and datatypes to JSONB to Postgres extensions including PostGIS and Citus. And, we explore the types of modern frameworks Postgres is a good fit for, including Django and Rails. If you are looking to leverage Postgres and you don’t want to have to get a Ph.D. in database technologies, come learn why Postgres is leading the resurgence of relational databases among app developers.

Transcript

2. Postgres is well loved loved wanted DBMS of the Year
3. App Developers?
4. Some brief history
5. Postgres OPEN SOURCE DATABASE SAME ROOTS AS MANY OTHERS NO SINGLE OWNER RELEASES YEARLY WITH NEW MAJOR RELEASE
6. But why do people love it?
7. Why do developers love it?
8. What’s in the box Feature rich Data types Advanced indexing Full text search Geospatial support JSON Extensions Vast ecosystem
9. Datatypes
10. JSONB
11. INSERT INTO products (name, attributes) VALUES ( 'Geek Love: A Novel’, 'author => "Katherine Dunn", pages => 368, category => fiction’ );
12. INSERT INTO products (name, attributes) VALUES ( 'Geek Love: A Novel’, 'author => "Katherine Dunn", pages => 368, category => fiction’ ); SELECT name, attributes->'author' as author FROM products WHERE attributes->'category' = 'fiction'
13. Datatypes
14. Indexing
15. INSERT INTO products (name, attributes) VALUES ( 'Geek Love: A Novel’, 'author => "Katherine Dunn", pages => 368, category => fiction’ ); SELECT name, attributes->'author' as author FROM products WHERE attributes->'category' = 'fiction'
16. INSERT INTO products (name, attributes) VALUES ( 'Geek Love: A Novel’, 'author => "Katherine Dunn", pages => 368, category => fiction’ ); SELECT name, attributes->'author' as author FROM products WHERE attributes->'category' = 'fiction’; CREATE INDEX idx_products ON products USING gin(attributes);
17. Indexing
18. Extensions
19. Ecosystem
20. Why People Azure Database for PostgreSQL Global reach Security Scale up & out Built-in HA Compliance Intelligent performance Easy ecosystem integration Extension support Extensions JSONB Full text search Geospatial support Rich indexing
21. With Azure Phd not required
22. Postgres the TLDR;
23. Why do enterprises love it?
24. Reliable history
25. Skills you already have DBAs know how to administer Not another thing to learn, lower overhead to bring online Frameworks already work with it Again not a new thing to learn, already works with your tools Easy to hire for Hiring is hard enough, don’t make it harder
26. Reduced total cost of ownership
27. Leveraging Azure Database for PostgreSQL with OpenText
28. Leveraging Azure Database for PostgreSQL with OpenText
29. Postgres for lower TCO Open source meaning no expensive licensing SQL that works well to replace existing SQL applications Not a new shiny type of data store, you already have the skills to support
30. All these build on each other FOR DEVELOPERS: RICH FEATURE SET AND CONTINUAL INNOVATION FOR COMPANIES: ALREADY KNOWN TOOL WITH EXISTING SKILLS FOR ENTERPRISES: ABILITY TO REDUCE COST
31. One more thing
32. Hyperscale (Citus)
33. APPLICATION BEGIN; UPDATE SET WHERE COMMIT; campaigns start_date = '2018-03-17' company_id = 'Pat Co'; METADATA COORDINATOR NODE WORKER NODES W1 W2 W3 … Wn BEGIN; UPDATE Campaigns_2012 SET …; COMMIT; How Hyperscale (Citus) shards Postgres UPDATE
34. How Hyperscale (Citus) scales out Postgres APPLICATION SELECT FROM GROUP BY company_id, avg(spend) AS avg_campaign_spend compaigns company_id; METADATA COORDINATOR NODE WORKER NODES W1 W2 W3 … Wn SELECT company_id sum(spend), count(spend) … FROM campaigns_2009 … SELECT company_id sum(spend), count(spend) … FROM campaigns_2001 … SELECT company_id sum(spend), count(spend) … FROM campaigns_2017 … DISTRIBUTED AGGREGATE
35. On Azure
36. Thanks! FOR DEVELOPERS: RICH FEATURE SET AND CONTINUAL INNOVATION FOR EVERYONE: ALREADY KNOWN TOOL WITH EXISTING SKILLS FOR ENTERPRISES: ABILITY TO REDUCE COST
37. A good newsletter is like a good GIN index. Sign up for the Citus Newsletter