the postgresql conference - pgcon 2021
TRANSCRIPT
![Page 1: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/1.jpg)
&
![Page 2: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/2.jpg)
![Page 3: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/3.jpg)
![Page 4: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/4.jpg)
● More than 2,000,000,000 running instances
● More than 500,000 applications
The most widely used SQL engine
![Page 5: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/5.jpg)
Spin-off
![Page 6: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/6.jpg)
DependsOn
![Page 7: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/7.jpg)
WWPD
![Page 8: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/8.jpg)
http://www.sqlite.org/sqllogictest
● SQLite● MySQL● Oracle● SQL Server● PostgreSQL
Crashes and/or incorrect answers
Never crashed and always correct
![Page 9: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/9.jpg)
versus
Enterprise Data Depot Application File Format
Client and Server Serverless
Hidden Files
Scale DownScale UpScale Up
Single Disk File
Scale Down
Hidden Files Single Disk File
Dump and Restore Backwards Compatible
Multiple binaries One file of ANSI-C
![Page 10: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/10.jpg)
c,s,v
10011010010111
Pile offiles
<xml/>
![Page 11: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/11.jpg)
![Page 12: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/12.jpg)
SQLite as Application File Format● No parsing code to write and debug● Single-file documents● High-level query language● Accessible content● Cross-platform and cross-language● Atomic transactions● Incremental and continuous updating● Easily extensible● Multi-process and multi-thread safe● Improved performance
![Page 13: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/13.jpg)
Real Example: MicroStation
![Page 14: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/14.jpg)
Real Example: Adobe Lightroom
![Page 15: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/15.jpg)
What If....OpenDocument was an SQLite databaseInstead of a ZIP archive of XML files...
● Fast and low-I/O save of small changes● Fast startup● Reduced memory usage (no need to hold the
entire presentation in memory at once)● No need for “recovery” after a crash● No need to “file save”● Undo across sessions● Large searchable database of slides
![Page 16: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/16.jpg)
What If....ePub was an SQLite databaseInstead of a ZIP archive of XML files...
● Full text search● Faster open of large documents● Multiple image resolutions
![Page 17: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/17.jpg)
SQLite Archiver
● http://www.sqlite.org/sqlar● Size similar to ZIP● Transactional● Concurrent & random access
CREATE TABLE sqlar( name TEXT PRIMARY KEY, -- name of the file mode INT, -- access permissions mtime INT, -- last modification time sz INT, -- original file size data BLOB -- compressed content );
![Page 18: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/18.jpg)
SQLAR is 0.47% larger than ZIP
-rw-r--r-- 1 drh drh 9677708 May 17 12:44 pgcon2014.odp-rw-r--r-- 1 drh drh 9722880 May 17 12:46 pgcon2014.sqlar
![Page 19: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/19.jpg)
What If....Git stored content in an SQLite database insteadof in a pile-of-files under the “.git” folder.
● Advanced queries for a richer user interface● Proof against crashes● Wiki and Tickets● Concurrent access● Coding errors less likely to corrupt repository● On-the-fly repository compression● Single-file repository
![Page 20: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/20.jpg)
versus
Enterprise Data Depot Application File Format
Client and Server Serverless
Hidden Files
Scale DownScale UpScale Up
Single Disk File
Scale Down
Hidden Files Single Disk File
Dump and Restore Backwards Compatible
Multiple binaries One file of ANSI-C
![Page 21: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/21.jpg)
versus
Enterprise Data Depot Application File Format
Client and Server Serverless
Hidden Files
Scale DownScale UpScale Up
Single Disk File
Scale Down
Hidden Files Single Disk File
Dump and Restore Backwards Compatible
Multiple binaries One file of ANSI-C
“SQLite is not a replacement for PostgreSQL.
SQLite is a replacement for fopen().”
“SQLite is not a replacement for PostgreSQL.
SQLite is a replacement for fopen().”
![Page 22: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/22.jpg)
versus
CREATE TABLE abc(xyz INT);INSERT INTO abc VALUES('Hello!');NO! OK
CREATE TABLE abc(xyz TEXT);INSERT INTO abc VALUES(123);
CREATE TABLE abc(xyz INT);INSERT INTO abc VALUES('123');
OK
OK
OK
OK
![Page 23: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/23.jpg)
versus
CREATE TABLE abc(xyz BLOB);INSERT INTO abc VALUES(x'FEDC');
NO!
OK
CREATE TABLE abc(xyz ANY);
CREATE TABLE abc(xyz);
OK
OKNO!
CREATE TABLE abc(xyz TEXT); OKOK
NO!
![Page 24: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/24.jpg)
United by SQL
● The SQL front-end is the secret sauce● Most widely known programming language● Let the engine figure out the algorithm● Representation is the essence of programming
![Page 25: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/25.jpg)
“Representation is the essenceof computer programming.”
"Show me your flowcharts andconceal your tables, and I shallcontinue to be mystified. Showme your tables, and I won't usuallyneed your flowcharts; they'll beobvious."
- Fred Brooks, The Mythical Man-Month, pp102-103
![Page 26: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/26.jpg)
"Data dominates. If you've chosenthe right data structures andorganized things well, the algorithmswill almost always be self-evident.Data structures, not algorithms, arecentral to programming."
- Rob Pike: Rule of Programming #5
![Page 27: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/27.jpg)
"Bad programmers worry aboutthe code. Good programmersworry about data structures andtheir relationships."
- Linus Torvalds, on the Git mailing list, 2006-06-27
![Page 28: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/28.jpg)
United by SQL
● The SQL front-end is the secret sauce● Most widely known programming language● Let the engine figure out the algorithm● Representation is the essence of programming
Key/value and “eventually consistent”data stores are a passing fad.
SQL is here for the long term.
Key/value and “eventually consistent”data stores are a passing fad.
SQL is here for the long term.
![Page 29: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/29.jpg)
● Absence of objective truth
● Queries return opinions rather than facts
PostmodernDatabases
![Page 30: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/30.jpg)
PostgreSQLSQLiteOracle
SQL Server
CassandraMongoDBCouchDBBigTable
![Page 31: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/31.jpg)
There was another big cultural shift at Google...The SQL-based analytics system, Dremel... made a lot of SQL converts at Google. People realized it is incrediblypowerful to just push the semantics of your query down intothe storage system and let it figure out what to do.
NoSQL databases that only have weak consistencyare enforcing a broadly applied premature optimizationon the entire system.
Alexander Lloyd at Berlin Buzzwords, 2012
![Page 32: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/32.jpg)
![Page 33: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/33.jpg)
������������������������������������� �������������������������� ����������������������������������� ������������������������������������������������������������������� ����������������������������������������� ������������������������������������������������������ ������������������������� ����������������������������������� ������������������������������������������������������������������� ����������������������������������������� �������������������������������������������������������� �������������������������������������������� ����������������������������������� ������������������������������������������������������������������ ������������������������������������������������� ������������������������������������ ����������������������������� ������������������������������������������������������������������������������������������������������������������������� �������������������������� ����������������������������������� ������������������������������������������������������������������� ����������������������������������������� ������������������������������������������������������ ������������������������� ����������������������������������� ������������������������������������������������������������������� ����������������������������������������� �������������������������������������������������������� �������������������������������������������� ����������������������������������� ������������������������������������������������������������������ ������������������������������������������������� ������������������������������������ ������������������������ ����������������������������������������������������������������������������������������������������������� ����������������������������������
![Page 34: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/34.jpg)
![Page 35: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/35.jpg)
SELECT blob.rid AS blobRid, uuid AS uuid, datetime(event.mtime) AS timestamp, coalesce(ecomment, comment) AS comment, coalesce(euser, user) AS user, blob.rid IN leaf AS leaf, bgcolor AS bgColor, event.type AS eventType, (SELECT group_concat(substr(tagname,5), ', ') FROM tag, tagxref WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid AND tagxref.rid=blob.rid AND tagxref.tagtype>0) AS tags, tagid AS tagid, brief AS brief, event.mtime AS mtime FROM event CROSS JOIN blobWHERE blob.rid=event.objid AND NOT EXISTS(SELECT 1 FROM tagxref WHERE tagid=5 AND tagtype>0 AND rid=blob.rid) AND event.type='ci' AND event.mtime>=2451693.500000 ORDER BY event.mtime ASC LIMIT 8
![Page 36: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/36.jpg)
![Page 37: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/37.jpg)
http://www.sqlite.org/postgresql
![Page 38: The PostgreSQL Conference - PGCon 2021](https://reader031.vdocuments.us/reader031/viewer/2022012103/616a134311a7b741a34e88ee/html5/thumbnails/38.jpg)
Concluding Thoughts
● Think of PostgreSQL as a programming language
● PostgreSQL is the best available reference platform for SQL
● Use PostgreSQL as an enterprise data depot, and use SQLite for application files.