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

1. View Slide

2. Postgres is well loved
   loved
   wanted DBMS of the Year
   

   View Slide

3. App Developers?
   

   View Slide

4. Some brief history
   

   View Slide

5. Postgres
   OPEN SOURCE
   DATABASE
   SAME ROOTS AS MANY
   OTHERS
   NO SINGLE OWNER RELEASES YEARLY WITH
   NEW MAJOR RELEASE
   

   View Slide

6. But why do people
   love it?
   

   View Slide

7. Why do developers love it?
   

   View Slide

8. What’s in the box
   Feature
   rich
   Data types
   Advanced indexing
   Full text search
   Geospatial support
   JSON
   Extensions
   Vast ecosystem
   

   View Slide

9. Datatypes
   

   View Slide

10. JSONB
    

    View Slide

11. INSERT INTO products (name, attributes) VALUES (
    'Geek Love: A Novel’,
    'author => "Katherine Dunn",
    pages => 368,
    category => fiction’
    );
    

    View Slide

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'
    

    View Slide

13. Datatypes
    

    View Slide

14. Indexing
    

    View Slide

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'
    

    View Slide

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);
    

    View Slide

17. Indexing
    

    View Slide

18. Extensions
    

    View Slide

19. Ecosystem
    

    View Slide

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
    

    View Slide

21. With Azure
    Phd not
    required
    

    View Slide

22. Postgres the TLDR;
    

    View Slide

23. Why do enterprises love it?
    

    View Slide

24. Reliable history
    

    View Slide

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
    

    View Slide

26. Reduced total cost of ownership
    

    View Slide

27. Leveraging Azure Database for PostgreSQL with
    OpenText
    

    View Slide

28. Leveraging Azure Database for PostgreSQL with
    OpenText
    

    View Slide

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
    

    View Slide

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
    

    View Slide

31. One more thing
    

    View Slide

32. Hyperscale (Citus)
    

    View Slide

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
    

    View Slide

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
    

    View Slide

35. On Azure
    

    View Slide

36. Thanks!
    FOR DEVELOPERS: RICH FEATURE
    SET AND CONTINUAL INNOVATION
    FOR EVERYONE: ALREADY KNOWN
    TOOL WITH EXISTING SKILLS
    FOR ENTERPRISES: ABILITY TO
    REDUCE COST
    

    View Slide

37. A good newsletter is like a good GIN index.
    Sign up for the
    Citus Newsletter
    

    View Slide