PostgreSQL Web Projects:
From Start to Finish
Simon Riggs
2nd Quadrant
...
PostgreSQL
 Open Source Relational Database
− 11 year project, 21 years since inception
...
Stunning Features
 Visual EXPLAIN
 Procedural Language Debugger
 (Amazing) Index Su...
Principles
 “It just works”
 “Failure is not an Option”
 All features, Great and Sm...
Sponsors (VC Startups)‫‏‬
© 2ndQuadrant Limited 2007
Sponsors (Hosting Providers)‫‏‬
© 2ndQuadrant Limited 2007
Sponsors (Services)‫‏‬
© 2ndQuadrant Limited 2007
Sponsors (Major Vendors)‫‏‬
© 2ndQuadrant Limited 2007
Sponsors (Major Users)‫‏‬
© 2ndQuadrant Limited 2007
Sponsors
© 2ndQuadrant Limited 2007
UK Users
 Web Infrastructure
 Independent Service Providers
 Major Telcos and Telec...
International Users
 Afilias
 Skype
 NTT
 ...
 25% market...
Mailing List Postings
© 2ndQuadrant Limited 2007
Use Cases
 OLTP
 Web Applications
 Data Warehouse
 Audit & Review
...
Web Project Evolution
 Startup
 Success
 The Big Time
© 2ndQuadrant Limited 2007
Get Started Quickly
 Zero Licence Cost
− Greater % of money goes to Development
...
Basic Web Features
 SQL:2003
 UTF-8
 XML Support
 National Language Su...
Works the way you do
 Multiple client interfaces
− Java, perl, python, PHP, odbc, .net, nat...
Security
 Security by default
 Multi-layer security model
 Full encryption
...
Cool Web Features
 It just works!
 Arrays
 Text > 2000, Autocompression
...
Enhance Incrementally
 Advanced Features
 Extensible Features
 Transactional DDL
...
Major Extensions
 Full Text Search
− Snowball
− GIN – inverted search ind...
Data Availability
 No block locking
 COPY (load...)‫‏‬
 VACUUM
 CREATE...
High Availability
 Hot Backup
 Point in Time Recovery
 Warm Standby
© 2ndQuadra...
Replication
 “Postgres doesn't have replication”
 True: Not part of the core package...
Scale Up, VLDB
 SMP scalability
− 16 cores verified by Unisys Scalability Lab
 ...
MySQL v PostgreSQL Scalability
© 2ndQuadrant Limited 2007
MySQL v PostgreSQL Scalability
http://jamonation.com/
node/734
© 2ndQuadrant Limited 2007
Scale Out
 Result Cache
− Memcached, pgmemcache
 Multi-node scalability
...
8.3 Features
 HOT, Distributed Checkpoint, Autotuning BgWriter
 Further Scalability improvements...
PostgreSQL
© 2ndQuadrant Limited 2007
of 31

PoPostgreSQL Web Projects: From Start to FinishStart To Finish

Published on: Mar 4, 2016
Published in: Technology      Business      
Source: www.slideshare.net


Transcripts - PoPostgreSQL Web Projects: From Start to FinishStart To Finish

  • 1. PostgreSQL Web Projects: From Start to Finish Simon Riggs 2nd Quadrant © 2ndQuadrant Limited 2007
  • 2. PostgreSQL  Open Source Relational Database − 11 year project, 21 years since inception − BSD Licence  Database − Server-based SQL:2003 compliant − Highly concurrent − Fast, Robust, Secure © 2ndQuadrant Limited 2007
  • 3. Stunning Features  Visual EXPLAIN  Procedural Language Debugger  (Amazing) Index Support  Cost Base Optimizer, Histograms  SQL:2003 © 2ndQuadrant Limited 2007
  • 4. Principles  “It just works”  “Failure is not an Option”  All features, Great and Small  Respect Published Standards  Build on the work of others  Peer Review of Everything  Good documentation is fundamental  Solid, Simple Code (...works at 4am)‫‏‬  Improvement is always possible © 2ndQuadrant Limited 2007
  • 5. Sponsors (VC Startups)‫‏‬ © 2ndQuadrant Limited 2007
  • 6. Sponsors (Hosting Providers)‫‏‬ © 2ndQuadrant Limited 2007
  • 7. Sponsors (Services)‫‏‬ © 2ndQuadrant Limited 2007
  • 8. Sponsors (Major Vendors)‫‏‬ © 2ndQuadrant Limited 2007
  • 9. Sponsors (Major Users)‫‏‬ © 2ndQuadrant Limited 2007
  • 10. Sponsors © 2ndQuadrant Limited 2007
  • 11. UK Users  Web Infrastructure  Independent Service Providers  Major Telcos and Telecom Equipment Vendors  Gaming, Gambling, Media  Local Government  University Projects  The City © 2ndQuadrant Limited 2007
  • 12. International Users  Afilias  Skype  NTT  ...  25% market share in Japan  4,000+ user group members © 2ndQuadrant Limited 2007
  • 13. Mailing List Postings © 2ndQuadrant Limited 2007
  • 14. Use Cases  OLTP  Web Applications  Data Warehouse  Audit & Review © 2ndQuadrant Limited 2007
  • 15. Web Project Evolution  Startup  Success  The Big Time © 2ndQuadrant Limited 2007
  • 16. Get Started Quickly  Zero Licence Cost − Greater % of money goes to Development − No licence planning, no vendor negotiations − Faster Time To Market − Faster Time To Profit  Install and Go − Runs everywhere, with everything − Easy setup − SQL:2003 Database − Graphical Admin & Design tools − Stack Integration © 2ndQuadrant Limited 2007
  • 17. Basic Web Features  SQL:2003  UTF-8  XML Support  National Language Support  Great documentation  Responsive, Busy Community © 2ndQuadrant Limited 2007
  • 18. Works the way you do  Multiple client interfaces − Java, perl, python, PHP, odbc, .net, native  Multiple stack integrations − LAMP, Java, .Net, Rails  Users, Path, environment variables  Multiple Server side languages − PL/pgSQL, PL/java, PL/perl, PL/R, PL/PHP, PL/Ruby  regex © 2ndQuadrant Limited 2007
  • 19. Security  Security by default  Multi-layer security model  Full encryption  Enterprise security infrastructure integration  Full disclosure of vulnerabilities  Security bug fix SLA © 2ndQuadrant Limited 2007
  • 20. Cool Web Features  It just works!  Arrays  Text > 2000, Autocompression  Datatypes − enum, uuid, cidr, macaddr  SET parameters  Asynchronous Commit © 2ndQuadrant Limited 2007
  • 21. Enhance Incrementally  Advanced Features  Extensible Features  Transactional DDL  Production Tuning facilities − Logging, profiling, server-side debugging  CREATE INDEX CONCURRENTLY © 2ndQuadrant Limited 2007
  • 22. Major Extensions  Full Text Search − Snowball − GIN – inverted search index − GIST – high concurrency  Geographical Information System (GIS)‫‏‬ − Full implementation − R-Tree indexes − Custom statistics functions © 2ndQuadrant Limited 2007
  • 23. Data Availability  No block locking  COPY (load...)‫‏‬  VACUUM  CREATE INDEX CONCURRENTLY © 2ndQuadrant Limited 2007
  • 24. High Availability  Hot Backup  Point in Time Recovery  Warm Standby © 2ndQuadrant Limited 2007
  • 25. Replication  “Postgres doesn't have replication”  True: Not part of the core package  False: There's loads of replication alternatives  Slony  Warm Standby  Bucardo  Londiste © 2ndQuadrant Limited 2007
  • 26. Scale Up, VLDB  SMP scalability − 16 cores verified by Unisys Scalability Lab  VLDB − Partitioning − Many Terabyte references worldwide − Frequently used in the 100GB+ range © 2ndQuadrant Limited 2007
  • 27. MySQL v PostgreSQL Scalability © 2ndQuadrant Limited 2007
  • 28. MySQL v PostgreSQL Scalability http://jamonation.com/ node/734 © 2ndQuadrant Limited 2007
  • 29. Scale Out  Result Cache − Memcached, pgmemcache  Multi-node scalability − PgBouncer, pgpool − PL/Proxy © 2ndQuadrant Limited 2007
  • 30. 8.3 Features  HOT, Distributed Checkpoint, Autotuning BgWriter  Further Scalability improvements  Asynchronous Commit  Synchronous Scanning  XML Datatypes  Warm Standby, Replication improvements © 2ndQuadrant Limited 2007
  • 31. PostgreSQL © 2ndQuadrant Limited 2007

Related Documents