PostgreSQL at the university: some lessons learned
What PostgreSQL have done for us:
We switched from Oracle to PostgreSQL in 2018
PostgreSQL's cliché :
Feedback: not true (with some reserve on #3)
A “production” PostgreSQL server in a university is quite atypical (from a DBA perspective)
Feedback: Students in CS are “lame power users”
Feedback: size does not matter
Feedback: a small instance with many dbs and users
select datname, tup_inserted/10^6 AS tup_ins_M, tup_deleted/10^3 AS tup_del_K, pg_database_size(datname)/10^6 as db_size_M from pg_stat_database order by db_size_M DESC LIMIT 5; -- datname | tup_ins_m | tup_del_k | db_size_m -- ------------+-----------+-----------+------------ -- pg11809287 | 3.192844 | 1.726 | 403.378847 -- pg11711637 | 24.509523 | 7.13 | 373.961375 -- pedago | 0.928755 | 0.186 | 267.743903 -- pg11709217 | 2.5619 | 9.165 | 264.598175 -- pg11511220 | 3.816837 | 2.813 | 257.053343
Feedback: no one tried to fill the volume!
Feedback: Oracle was a pain i. t.. a..
Feedback: Users’ accounts is a key point.
(3rd year) advanced databases and foundations
feedback: transition was almost transparent BUT the some labs still need extra refactoring
(4th year) Web Data Management
feedback: no issue at all, stopped installing Oracle as it was bad advertisement for the product
Other smaller classes and labs
Feedback: documentation is GREAT,
COPY is fast, extensions are cool!
Proficient use of
psql is still a challenge
\setenv PSQL_EDITOR 'nano'
\setenv PAGER 'less -S'
Feedback: we asked the IT to install DBeaver…
How to tame the
Feedback: good practice: delete the public schema?
Some (classical?) pitfalls:
Feedback: nothing difficult, but the experience is worth sharing with academics
More proficient DBA
Feedback: need some time, but it’s worth it and it gonna be fun
“A lot has changed since SQL-92” (Markus Winand)
WITHQueries (Common Table Expressions)
Feedback: @l_avrot put pressure on me to do so!
PostgreSQL features and extensions
Feedback: IMHO a convincing way to promote PostgreSQL!
Feel free to ask questions!
Keep in touch :
romuald.thion [at] univ-lyon1.fr