Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Why Application Developers Love Postgres | Ignite 2019 | Craig Kerstiens

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.

Azure Database for PostgreSQL

November 06, 2019
Tweet

More Decks by Azure Database for PostgreSQL

Other Decks in Technology

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