[HN Gopher] In Praise of PostgreSQL
___________________________________________________________________
In Praise of PostgreSQL
Author : TangerineDream
Score : 211 points
Date : 2021-08-05 15:36 UTC (7 hours ago)
(HTM) web link (drewdevault.com)
(TXT) w3m dump (drewdevault.com)
| smnscu wrote:
| If anyone is looking for the Alpine article linked in the first
| paragraph, it's only available via Gemini, or via a proxy like
| here
| https://portal.mozz.us/gemini/drewdevault.com/2021/05/06/Pra...
| __app_dev__ wrote:
| > all of its competitors are essentially obsolete
|
| For most companies I've worked at I've used either SQL Server or
| Oracle and from what I've seen when looking at job posts recently
| the demand is only increasing. Although I live in Los Angeles and
| not Silicon Valley.
|
| Additionally Facebook and other large tech companies still use
| MySQL and outside of silicon valley many large companies still
| run IBM databases.
|
| I don't see the other major databases being obsolete at all.
|
| I've used PostgresSQL before for learning (~3 years ago) but
| didn't really like the web based UI compared to SQL Server or
| Oracle query tools.
|
| Does anyone have recommended PostgresSQL query interfaces?
| brightball wrote:
| Regarding SQL Server at least (I can't speak to Oracle),
| they've created something of a network effect.
|
| SQL Server is really expensive. But, the more Microsoft stack
| you have in house the more discounts you get. The more
| Microsoft certified staff you have, the more discounts you get.
| This biases companies to hiring more developers with Microsoft
| certs, which leads to more developers getting Microsoft certs,
| which leads to more discounts and a feeling of investment at
| higher levels of the company that lead them to be unwilling to
| switch.
|
| I spent 5 years at a company that was using SQL Server as the
| backbone of it's flagship product. They pushed it as far as
| they could, but ultimately the biggest issues with the product
| at the company revolved around either limitations of SQL
| Server, lack of features with SQL Server or licensing being
| prohibitive of different types of beneficial scaling
| strategies. I pushed for them to make the switch to PostgreSQL
| for probably my last 3 years there as part of the architecture
| group, including migration and training plans, etc but it was
| like talking to a wall. Nobody could acknowledge the SQL Server
| issues. Everyone who had a part in considering solutions was
| too invested as far as I could tell. Eventually they tried
| moving to Couchbase (NoSQL) before finally making the switch to
| PostgreSQL from what I heard from people I know who still work
| there.
|
| As far as query interfaces go, you can use just about any tool
| you like. I prefer Navicat myself but I know a lot of people
| like DBeaver and DataGrip.
| tomjen3 wrote:
| Lots of older companies got Oracle and aren't changing that. I
| seem to recall that SQL server works really well with .net, in
| a way that most others don't.
|
| I think that explains the jobs. Then you have the people who
| make suboptimal choices, such as buying Oracle today, because
| nobody got fired for doing that.
| merb wrote:
| > I seem to recall that SQL server works really well with
| .net, in a way that most others don't.
|
| my company uses postgresql and dotnet efcore and it works
| really really really well. npgsql/efcore.pg is really awesome
| and has stuff built-in that works. kudos to roji which
| basically built it (as far as I know does he work for
| microsoft to bring npgsql forward). but maybe it works
| equally well, because microsoft bought citus and thus many
| psql developers.
| mixmastamyk wrote:
| Obsolete as in, postgres is free/open/libre and as good or
| better than other options for 95% of projects, and growing.
| It's a similar situation to the old adage about "skating to
| where the puck is going," rather than where it is now.
|
| I don't use Web DB UIs, but I like dbeaver and pgcli a lot. You
| may find them useful.
| funcDropShadow wrote:
| I assume you are referring to pgAdmin, which isn't even a part
| of PostgreSQL. I work usually with a well configured psql cli
| tool, or IntelliJ IDEAs database console. They even turned it
| into its own product. But conflating a database UI/console with
| the database is big misunderstanding. Nobody pays for Oracle DB
| because of SQL Developer. The strength of PostgreSQL is in the
| flexibility and maturity of the features of the database core
| and its extensibility. At least those are the reason, why I
| have recommended PostgreSQL in the past.
| in9 wrote:
| Are you talking about a client application? PgAdmin is the goto
| for postgres administration. But some ppl like DBeaver and I
| really like Datagrip.
|
| However, I also really like using clis when they are available.
| In that sense psql is quite good and pgcli is really cool.
| beermonster wrote:
| How does it compare to MariaDB?
| rubenv wrote:
| It's better.
| beermonster wrote:
| This [1] seems to be a more discerning answer
|
| [1] https://hevodata.com/learn/mariadb-vs-postgresql/
| Tostino wrote:
| FYI, the section on partitioning is wrong there.
|
| Postgres supports table partitioning perfectly fine[1].
|
| 1. https://www.postgresql.org/docs/current/ddl-
| partitioning.htm...
| nelgaard wrote:
| They are both very impressive systems. I have done many
| projects in both PostgreSQL and MariaDB/MySQL
|
| I do tend to get attached to the DB of my last project because
| I discover new cool things. And there are some differences.
|
| But I cannot think of a PostgreSQL project that would not also
| have worked great in MySQL or a MySQL project that could not
| have been done with PostgreSQL.
|
| I have re-implemented systems that used non open-opensource
| DBMS's on Windows and the convenience and performance (on the
| same hardware) were just amazing when using PostgreSQL or
| MariaDB.
| 0xdeadbeefbabe wrote:
| Less West Side Story more Jungle Book.
| ploxiln wrote:
| Postgres has always prioritized correctness, and more
| sophisticated datatype and query features ... mysql/mariadb
| have had more consistent performance and easier/more-flexible
| replication. I'm thinking about auto-vacuum and logical
| replication to upgraded versions... I've had much less trouble
| with mysql/mariadb around these topics many years ago, than
| I've had with Postgres relatively recently. But also, I don't
| care so much about fancy data types and query features, I stick
| to the basics.
| flowerlad wrote:
| > _For a start, Postgres is simply an incredibly powerful,
| robust, and reliable piece of software, providing the best
| implementation of SQL._
|
| Why is it better than MySQL? This article wasted my time. Is
| there a better article which explains why Postgres is better than
| other free and opensource SQL implementations?
| CabSauce wrote:
| Better yet, there's a website that allows you to find other
| websites that answer your questions.
| flowerlad wrote:
| Sadly the first page of results is all similar to this site,
| in terms of quality. HN readers are more knowledgeable than
| websites that can find other websites.
| reilly3000 wrote:
| For me it's their extensive selection of foreign data wrappers
| and extensions which allow for it to be incredibly integrated
| and work with novel paradigms. It was the first by several
| years to support JSON fields and window functions, but I was
| pleased to see those in MySQL 8. For extremely write-heavy use
| cases I think MySQL wins as Postgres tends to want every
| materialized view current before completing an insert. It's
| license has never been in question, where MySQL, Percona, and
| MariaDb have been a source of concern/confusion over the years.
|
| https://wiki.postgresql.org/wiki/Foreign_data_wrappers IMAP,
| GitHub, BigQuery, Faker and CSV have been awesome for me, but
| don't tend to be 100% supported by public cloud managed DB
| services. I've heard some can be flaky.
|
| INSERT INTO public.people SELECT ssn, name, phone_number FROM
| faker.people;
|
| That is a way to generate a ton of fake records for integration
| and load testing. Very elegant!
| indymike wrote:
| MySQL and MariaDB are REALLY great software. The real
| difference is not one of quality (both are proven performers,
| and both have survived the test of time, both are used at scale
| by some pretty giant customers), it is one of quantity.
| Postgres simply has more features by a pretty wide margin than
| does MySQL. For many DBAs and developers, Postgres is a more
| flexible choice due to the additional capability.
| threeseed wrote:
| > Postgres simply has more features
|
| And yet lacks the most important one. A natively supported
| clustering and HA solution.
|
| Especially important with CitusData being acquired by
| Microsoft and its future now at risk.
| whitepoplar wrote:
| pg_auto_failover is pretty great.
| threeseed wrote:
| a) It's maintained by CitusData which was acquired by
| Microsoft and so is now both unsupported and based on
| history at risk of being unmaintained.
|
| b) Is not part of the core PostgreSQL codebase and so you
| are forever having to worry about compatibility.
|
| c) PostgreSQL still lacks multi-master HA which is needed
| when you want to HA but continue to scale out.
| whitepoplar wrote:
| FWIW, pg_auto_failover was released post-acquisition and
| is most certainly _not_ unsupported; it 's actively
| developed and new feature releases have been solid. It
| would be nice for this functionality to be built into
| Postgres, though.
| Twisell wrote:
| Ahem there is also https://repmgr.org
|
| An actively developed FOSS solution backed by EnterpriseDB
| also one of the main company supporting PostgreSQL.
|
| But err... And ahem ahem... there are more https://www.post
| gresql.org/download/products/3-clusteringrep...
|
| It's a choice of the PostgreSQL development team to only
| include low level api for HA in core project. But it's
| fairly harsh to say there is no active support for HA. Many
| maintainers of the core are also invested in HA related
| project build around PostgreSQL.
| Tostino wrote:
| I love me some partial indexes, exclusion constraints, range
| types, etc... Not even mentioning the GIS capability.
| johncalvinyoung wrote:
| Transactional DDL changes is a huge one. Postgres allows me to
| modify the table schema in a transaction, and rollback changes
| as if it was a mere data transaction, if something fails mid-
| migration. Makes maintaining production databases through
| upgrades and schema changes much less stressful.
| WJW wrote:
| I've always wondered about this in the scope of REALLY big
| tables. Migrating a busy table with a few billion rows will
| take 10+ hours, doesn't the redo log fill up? The amount of
| work that needs to be undone could easily take another 10+
| hours, is there enough storage space for that?
|
| Every busy production db I've ever seen has had big problems
| when dealing with 10-hour transactions, and I don't see how
| Postgres could deal with these kind of problems as they're
| fundamental in the data model.
| andruby wrote:
| What kind of DDL migration are you thinking about that
| would take 10+ hours? Adding a column is instant, even if
| your table is multiple TB's large. Same for dropping and
| renaming columns.
| funcDropShadow wrote:
| Adding a column can be instant on multi TiB tables, but
| that depends on the details. If is a non-null column with
| a non-stable default value, like the current time, it
| will require a full rewrite of all rows of the table.
| WJW wrote:
| Indeed, i was talking about multi-TB tables. From the
| postgres v13 docs
| (https://www.postgresql.org/docs/current/sql-
| altertable.html):
|
| > Adding a column with a volatile DEFAULT or changing the
| type of an existing column will require the entire table
| and its indexes to be rewritten. As an exception, when
| changing the type of an existing column, if the USING
| clause does not change the column contents and the old
| type is either binary coercible to the new type or an
| unconstrained domain over the new type, a table rewrite
| is not needed; but any indexes on the affected columns
| must still be rebuilt. Table and/or index rebuilds may
| take a significant amount of time for a large table; and
| will temporarily require as much as double the disk
| space.
|
| Clearly there are workarounds where you make a new empty
| column that you then slowly backfill with the correct
| value and eventually transactionally rename the new
| column to the old one, but the magic "oh postgres does
| DDL in a transaction so it's magic and we never have to
| worry" is pretty much gone at that point.
| bostonvaulter2 wrote:
| Yes, but generally that could be handled in a multi-stage
| process. First you would create the new column with no
| constraint, then periodically start updating the rows,
| once nearly all the rows are updated with the new
| default, in a transaction you update the rest of the
| rows, update the table with the constraint, and set the
| default. Not very much downtime is required with this
| approach (although of course it is more involved).
| jayknight wrote:
| >doesn't the redo log fill up?
|
| It's not a redo/undo log. It's more like (simplifying
| things here) having multiple copies of things and different
| sessions/connections "see" different sets of data. That's
| why you need to vacuum the database periodically, to
| garbage collect old rows that are no long accessible to any
| sessions/transactions.
|
| See https://devcenter.heroku.com/articles/postgresql-
| concurrency
|
| >is there enough storage space for that?
|
| You do need to make sure you aren't too close to filling
| your storage, postgres needs some breathing room for this
| reason.
| stepbeek wrote:
| Transactional DDL changes cannot be understated - it's the
| most outrageously useful database feature out there.
|
| I have to work with oracle for a client right now and I'm
| gobsmacked that they don't offer it at the pricepoint.
| threeseed wrote:
| One is not better they are just different.
|
| If you value more expressive data types and constructs to
| read/write your data then PostgreSQL is better. If you don't
| need those but do need to be able to scale to very large data
| volumes then MySQL is better.
|
| I don't understand why people keep wanting to make this a
| competition. It's been going for decades and it was stupid back
| then as it is today. The databases are both popular, both high
| quality but serve different needs.
| bogomipz wrote:
| Could anyone recommend a good and current book on Postgres?
| VWWHFSfQ wrote:
| This one's great:
|
| https://theartofpostgresql.com/
| claytonjy wrote:
| As a counterpoint, I did not like this book and learned much
| more efficiently from the postgres docs
| bogomipz wrote:
| Can you say what you didn't like about it? It's a little
| pricey so I would be curious to hear your feedback.
| mixmastamyk wrote:
| I liked the o'Reilly book, _Postgres: Up and Running_
| approachable if you have some db experience. It is short and to
| the point.
| [deleted]
| fabian2k wrote:
| I'll second the praise for the Postgres documentation. I learned
| a lot about Postgres simply by looking through the documentation
| and reading the parts that looked interesting.
| funcDropShadow wrote:
| It is great that you can switch on any page in the
| documentation to the documentation of another release of
| PostgreSQL. That is extremely helpful if you are working with
| different versions.
| p_l wrote:
| Not just about postgres internals, but generally about SQL in
| my case.
|
| Interestingly enough the competition for "biggest impact on my
| SQL Knowledge"... Is a book about MS Access 97 which taught me
| enough about how SQL actually works that I could understand
| Postgres docs.
| djk447 wrote:
| Same here. I feel like I built several years of my career on
| reading that documentation and knowing where to find things
| when other folks didn't, so they'd come and ask me and I'd just
| send them to the right place.
| mjw1007 wrote:
| Parts of the documentation are very good.
|
| For example https://www.postgresql.org/docs/13/wal-
| configuration.html is done properly: it describes what that
| part of the program actually does, with the settings that
| affect it introduced at the appropriate point, separately from
| the reference documentation which lists the same settings in
| alphabetical order.
|
| But not all aspects of Postgres get this quality of
| documentation. It would be good to see a similar treatment of
| the checks the planner or executor performs to decide whether
| to permit access to a particular table and column and row,
| rather than having to deduce it from the separate descriptions
| of the security features.
|
| (For example, the basic fact that a VIEW accesses a table using
| the privileges of the view's owner, not those of the user
| executing the query, is very hard to find in the
| documentation.)
| codesections wrote:
| > PostgreSQL has taken a complex problem and _solved_ it to such
| an effective degree that all of its competitors are essentially
| obsolete, perhaps with the exception of SQLite.
|
| I realize that this isn't the main point, but that aside strikes
| me as high praise for SQLite -- which has also always struck me
| as exceptionally well built software.
| pcr910303 wrote:
| SQLite is an exceptional piece of software as well...
|
| It's lightweight, reliable, knows what it's tasked for, does
| exactly wat it does, and easy to extend. I personally reach
| SQLite first in so many cases, including web backends. That the
| DB is a file that's literally a cp away to backup is super-
| useful in development and debugging.
|
| Really the only complaint is that type enforcement is
| lacking... which I wish I could have said not a big deal, but
| it turns out it is. I wish I had a embedded DB that's as
| reliable and trustful, lightful as SQLite, but with types
| enforced. :-(
| password4321 wrote:
| Does https://duckdb.org enforce types?
| suprfsat wrote:
| Javascript does exactly wat it does but I don't see anybody
| rushing to praise it
| names_are_hard wrote:
| Agreed on all points. I'd also add that I miss table-valued
| functions in sqlite.
| DemocracyFTW wrote:
| you can write your own table-valued, aggregate and window
| functions when you use better-sqlite3 (see
| https://github.com/JoshuaWise/better-
| sqlite3/blob/master/doc... see Database#function(),
| Database#aggregate(), Database#table()) in JavaScript.
| open-source-ux wrote:
| It's worth taking a look at the Firebird relational database.
|
| Firebird can be used as an embedded DB or an client/server
| DB. It supports a wide variety of data types, is open source
| and cross-platform too.
|
| It's a database that's rarely discussed here (a mystery why
| not). It certainly deserves wider attention.
|
| _Firebird features_ : https://firebirdsql.org/en/features/
| kstrauser wrote:
| I worked with a company a long time ago that was using
| Interbase as the backend for a few websites. It had the
| _interesting_ property that if you were using the command
| line client to connect to the server, and you used ^Z to
| pause the client, then the server would hang _for all
| clients_ until you resumed it.
|
| I know it's completely unreasonable to judge Firebird today
| for Interbase 20 years ago, but admit that's still the
| first thing that pops to my mind.
|
| Do you feel Firebird offers something SQLite doesn't?
| open-source-ux wrote:
| Nothing beats the nimbleness, simplicity and speed of
| SQLite, but it is not a client/server database engine
| (although it is often used for that purpose due to its
| speed).
|
| Firebird can be used as a proper client/server database
| engine. It supports static data types and stored
| procedures (unlike SQLite). It sits somewhere between
| SQLite and PostgreSQL. So it may be worth evaluating if
| you don't want the heft of PostgreSQL but need features
| missing from SQLite.
| beermonster wrote:
| SQLite is literally on billions of devices and most people
| (even tech folk) are none the wiser.
| pedrocr wrote:
| I'm always amazed at the amazing reviews SQLite gets,
| particularly in a thread about PostgreSQL. I get the angle of
| SQLite competing with fopen(). If what you need is a file
| format you can do a lot worse than SQLite. But I've worked on
| multiple desktop apps where the only reason SQLite gets used
| for the internal database is that PostgreSQL doesn't have a
| mode where you can ship it as part of the app easily and asking
| the user to setup a database is a non-starter. If not for that
| the performance benefits of the swap would be very significant.
| I suspect if PostgreSQL shipped such a mode a very large
| percentage of SQLite uses would switch over.
| Tostino wrote:
| Completely agreed. I would love to be able to use an embedded
| version of Postgres. I have no interest in SQLite though
| because of the dynamic typing, reduced feature set, lack of
| advanced features, etc...
| [deleted]
| hitekker wrote:
| The "mode" you wish for is a rearchitecture of PostgreSQL.
| Embedded and client-server DBs are two distinct systems.
| 7952 wrote:
| Why though? Naively it would be possible to bundle postgres
| and have your app connect on localhost. What would be wrong
| with doing that?
| handrous wrote:
| You could do it, but you'd be paying (in complexity,
| resource use, and bundle size) for a bunch of features
| and optimizations you don't need, and it'd be tuned for a
| totally different use case than occasional access by one
| local process.
| marcosdumay wrote:
| On Linux you don't actually need to install Postgres. For
| Windows there is this: https://garethflowers.dev/postgresql-
| portable/
|
| I agree it could be better packaged, but it's actually not
| impossible to ship a Postgres with your program.
| anarazel wrote:
| There's a decent bit of optimizations that sqlite can do by
| virtue of effectively being single-writer that postgres can't
| without compromising a lot of other workloads. That's e.g.
| one of (but not the only) the contributing factors to why the
| per-row overhead in sqlite is smaller than in PG. And of
| course something like PG won't be embeddable into the address
| space of an application, which invariably leads to higher
| latency (even if it still can be low).
|
| WRT "embeddable postgres":
|
| Postgres relies on having multiple processes that collaborate
| on making database access fast. And it requires there to be
| only a single instance of postgres that can access the data.
| There's some inherent increase in difficulty of embedding
| something like that compared to something with sqlite's
| architecture.
|
| Until recently PG didn't have a way to provide non-tcp access
| on windows, which made embedding on windows a bit more
| problematic. But since Win 10 unix domain sockets are
| available on windows, so things have gotten better.
|
| I'd guess that after that the fact that a postgres
| installation consists out of many files, instead of a shared
| library or two, is the biggest difficulty. Those files are
| relocatable at least, but it's still far less convenient.
|
| I don't know how much of a relevant factor the layout of the
| "data directory" is - for some database embedding scenarios
| it sure is convenient to only have to deal with a file or
| two. But moving a directory around isn't that much harder...
|
| My guess is that somebody with interest could improve the
| situation measurably within a reasonable timeframe...
| donarb wrote:
| On the Mac at least, there is PostgresApp. A double-clickable
| application that wraps a PostgreSQL server. Installation
| takes a minute or two without having to use the command line
| for compilation.
|
| https://postgresapp.com
| chungy wrote:
| PostgreSQL occupies the domain of, and competes with,
| relational databases in a server-client model, especially those
| with extremely large datasets. Think of databases to track
| inventory, manage user accounts on a web domain, and so forth.
|
| SQLite occupies the domain of embedded (possibly in-memory and
| on low-resource systems) databases in which the subset of SQL
| unrelated to access control is all that's needed. Think of
| databases that take the place of application configuration,
| your browser bookmarks and cookies, a music library database.
|
| They are both excellent pieces of software, yes, but they don't
| even live in the same problem space despite both having "SQL"
| in their names.
| srcreigh wrote:
| This point is echoed by the creator of SQLite, Dr Richard
| Hipp, in his excellent talk "SQLite: The Database at the Edge
| of the Network". [1] "SQLite doesn't compete with client-
| server databases, it competes with fopen"
|
| (I highly recommend to watch it, if only to get a sense of
| the smart and funny energy that Dr Richard Hipp puts off!)
|
| [1]: https://www.youtube.com/watch?v=Jib2AmRb_rk
| quietbritishjim wrote:
| > they don't even live in the same problem space despite both
| having "SQL" in their names.
|
| That's true in theory, but I do find that they end up
| competing in practice when you're looking for something in
| between the two extremes.
|
| I often seem to find myself in a position where I need a
| database, but it's only for one day's data, which is not huge
| but not tiny (say 100's of MB). It may be helpful to have a
| couple of processes accessing it, but probably only one will
| be a writer, and it's very likely (but not certain) that
| those processes will be on the same computer. It useful to
| have that data in a single file for archiving. Those are
| situations where you currently face a geniune choice between
| PostgreSQL and SQLite - more because both of them are a bit
| wrong, rather than because they're both perfect for it.
|
| I wish there was something in the middle: a separate program
| you could spin up, so it's a client-server database like
| PostgreSQL, but it's just a single binary that doesn't need
| installing. And then it operates on a single file that it
| creates on the fly (or can open an existing one of course), a
| lot like SQLite. Hmm, as I think about it, it actually
| wouldn't be hard to make program that does that by making a
| thin wrapper around SQLite with e.g. gRPC.
| laurencerowe wrote:
| It's possible to run SQLite on an NFS share (with caveats:
| file locking must work correctly, WAL mode does not work at
| all). AWS EFS Elastic Filesystem specifically calls out its
| compatibility with SQLite. https://aws.amazon.com/about-
| aws/whats-new/2017/03/amazon-el...
|
| I would definitely not try using this in a write heavy
| workload but could be interesting to consider in a write
| occasionally scenario.
| marcosdumay wrote:
| I did once run SQLite on a Windows SMB share. It works
| unreasonably well.
|
| At the time I was able to push it into something like 10
| simultaneous write accesses (transparently serialized by
| SQLite). It was an adaptation of a MS Access file that
| would fail at approximately 5 people opening it at the
| same time (independently of their actual usage). Luckily
| I got access to a Postgres installation shortly
| afterwards so I didn't have to push SQLite any further.
| slapfrog wrote:
| It's also possible to put a two-stroke engine on a
| mountain bike and ride it on a limited access highway.
| But that's really not what the bike is meant for, and
| would almost never be the right way of going about
| things.
| bobthebuilders wrote:
| We use H2 Database (JVM only) at work for that. It solves
| the problem really well of a bunch of stuff that needs
| access to a single file DB, while still being able to embed
| the DB engine.
| [deleted]
| 411111111111111 wrote:
| H2 is a jdbc only in-memory database, so that's only an
| option for volatile data from/to Java.
|
| I think it's a pretty different use case to what the
| parent wanted
| mdaniel wrote:
| It most certainly is not just in-memory; it _can_ do in-
| memory, but also has file and server options: https://h2d
| atabase.com/html/features.html#connection_modes
|
| Exact same story for its HyperSQL and Derby friends:
| http://hsqldb.org/doc/2.0/guide/running-
| chapt.html#rgc_hsqld...
| https://db.apache.org/derby/#What+is+Apache+Derby%3F
| amedvednikov wrote:
| SQLite is perfect except for dynamic types.
| valbaca wrote:
| > but that aside strikes me as high praise for SQLite
|
| It's intended to be and is deserved.
| doctor_eval wrote:
| Nailed it - except one massive feature missed IMO is pl/pgsql.
|
| As a language it's a bit clunky* but once you learn it, and you
| start moving database logic into the database, suddenly
| everything is 25% LOC, often literally 100x faster, and easier to
| understand.
|
| As an added bonus, when I've moved very complex data transforms
| into pl/pgsql, the non-programmer technical people can now read
| the logic and have more than once pointed out problems (with
| solutions).
|
| * it helps a lot if you write your SELECT and other statements in
| lowercase. Feels barbaric I know, but reading shoutycode all day
| gives me a headache.
| zz865 wrote:
| I used Sybase and SQL Server for a long time. PostgresSQL I found
| much tougher than expected to figure out. Just seems built for
| functionality over ease of use. I guess I got to get used to it.
| openthc wrote:
| Versus Sybase!? Not my experience (but its so long ago now). I
| feel this may be a just a comfort-zone switch for you. Like,
| Sybase is known and therefore already figured out and "better".
| All big systems take some ramp-up / comfort-building time. As
| others have said, the documentation, the community (still on
| IRC and email!) are excellent. And heck, digging in the code
| for information is not so terrible either (and it's huge, and C
| (and I don't do either of those things) and I still found what
| I needed). I'm sure you'll be used to it in short order. I've
| already poured you some Kool-Aid :)
| mixmastamyk wrote:
| I found the o'Reilly book, _Postgres: Up and Running_
| approachable if you have some db experience.
| andrewmcwatters wrote:
| I want to use PostgreSQL, because there seems to be a lot of
| technical discussion around why it's so great, but the tooling I
| have available to me seems to be immature compared to MySQL's.
|
| And so, I find it really difficult to justify investing time in
| building up infrastructure to support it for my clients' work,
| because I need to be effective from day one.
|
| Realistically that has absolutely nothing to do with PostgreSQL
| but whether or not some little third-party company or _one or two
| individual developers_ have built something solid like Sequel
| Ace. That 's really troubling for me to think about, but these
| CLI tools are insufficient for being productive.
|
| This a great example to me as to why a business can't use a
| potentially superior product because some ecosystem feature is
| non-existent, or is hard to find.
|
| That being said, I'm not too upset because modern MySQL seems
| perfectly fine, and still more widely used than PostgreSQL.
| merb wrote:
| if you use psql you will be way more productive than using any
| gui tool. also Sequel Ace is so barbones that using a cli is
| way way faster.
| andrewmcwatters wrote:
| I understand that, but I'm not interested in writing
| everything by hand. I'd rather have tooling work for me
| instead, and verify correctness by reading the manual for
| this-or-that. It also helps my staff when I have a
| recommended tool for those less SQL inclined.
| ekzy wrote:
| IntelliJ is decent and has drivers for pretty much all
| database systems. It can be downloaded has a stand-alone
| too (DataGrip)
| kaliszad wrote:
| That is for IntelliJ Ultimate if I am not mistaken:
| https://www.jetbrains.com/help/idea/connecting-to-a-
| database... Otherwise as you mention DataGrip
| https://www.jetbrains.com/datagrip/ seems to be really
| sleek.
| andrewmcwatters wrote:
| Thank you for the insight!
| whitepoplar wrote:
| You need you some Postico*! Absolutely fantastic GUI for Mac.
|
| * Postico 1 is great, but the soon-to-be released Postico 2 is
| in beta atm and even better.
| andrewmcwatters wrote:
| Thank you for sharing this!
| frankwiles wrote:
| Been a big fan of PG since the late 90s. I always joke that the
| only time I've lost data with PG is when it was my own damn
| fault!
| doctor_eval wrote:
| Agreed. The last and only time I saw a database corruption with
| PG was in 1999 and the culprit was a memory fault in a raid
| array.
|
| Cross fingers now that my hubris does not come and bite me.
| Andrew_nenakhov wrote:
| [ deleted because of incorrect information ]
| massysett wrote:
| MySQL is also licensed under the GNU GPL.
| da_chicken wrote:
| As I recall, at one point MySQL drew significant criticism
| (or at least skepticism) for being dual licensed and
| requiring contributors to relinquish copyright to their code.
| To many, it felt like the goal was to lure you in and then
| change the license model once you'd adopted it fully.
|
| This was many years ago, before Oracle even entered the
| picture, but my guess is that that is what GP is remembering.
| anarazel wrote:
| PostgreSQL however is not:
| https://www.postgresql.org/about/licence/
| smashed wrote:
| > PostgreSQL is released under the PostgreSQL License, a
| liberal Open Source license, similar to the BSD or MIT
| licenses.
|
| https://www.postgresql.org/about/licence/
___________________________________________________________________
(page generated 2021-08-05 23:02 UTC)