Creating Vector MBTiles?

POSTGIS now supports outputting vector tiles as endpoint to a Query. https://postgis.net/docs/ST_AsMVT.html I honestly don't recommend this approach because currently that's just geometry and no attributes.

You can also pair POSTGIS with GeoServer and GeoWebCache to produce vector tiles but also don't recommend that.

But probably your best bet now is that GDAL 2.3 supports Writting Vector Tiles both in Folder of XYZ PBF and inside sqlite3 container mbtiles. https://www.gdal.org/drv_pg.html OGR2OGR can read from your POSTGIS database and write an mbtiles. It's slower than Tippecanoe and has less options. So you may also want to use MapBox Tippecanoe which is also free and open source. Tippecanoe can be run on Windows SubSystem for Linux (WSL) Bash Ubuntu (as well as Linux and Mac operating systems). It will require the data to be in GeoJSON (and CSV is supported) file to convert to vector tiles. Tippecanoe also has GeoJSON extensions for Zoom Level and Layer Name so you can have super granular control over the production of vector tiles. For polygons use the -pD option to not show tile boundaries and for most of the time don't do buffers. https://github.com/mapbox/tippecanoe

When producing vector tiles pay lots of attention to the attributes (Keys and Values) you are including if you are not using it as a label or popup in the map don't include it otherwise they get huge. Almost All JavaScript mapping engines now support rendering Vector Tiles some require plugins.

Go with MapBox GL JS or ESRI ArcGIS JavaScript API for the easiest integration and access to MapBox GL JSON Stylesheet support. You can serve vector tiles with just an S3 Bucket if uploading a folder of PBF's or use a tileserver with MBTILES file. I like TileServer-GL https://github.com/klokantech/tileserver-gl or TileStrata https://github.com/naturalatlas/tilestrata because you can deliver PNG tiles to clients that don't support vector tiles.
One last point is that you can use ESRI ArcGIS Pro to create vector tiles so you can connect your POSTGIS database. You need to create a Vector Tile Package VTPK and then convert that to mbtiles or extract to folder.

Use Tippcanoe https://github.com/mapbox/tippecanoe.

It accepts geojson and outputs mbtiles. you can serve mbtiles using simple PHP script also.

You can use https://github.com/klokantech/tileserver-php this to serve mbtiles.

If having pre-generated tiles satisfy your requirement/use case then Tippecanoe will be the best option, but if you want to generate tiles on the fly(as data is being updated frequently) then the st_asmvt function from PostGIS is suitable. because ultimately on every request based on your bounding box you will end up making multiple queries to PostGIS which will convert stored geometry to a binary format every time (also you can cache this in another table).

but mbtile is lightweight(based on SQLite DB) and tiles are already generated so you just have to serve those based on your x, y, and z parameters.

Again if you really want to generate tiles on the fly from PostGIS then I would recommend Martin, it is Rust based tile server that can use your PostGIS function which will return mvt, and performance is really good.

Just adding this as an addendum to the accepted answer:

It looks like ST_AsMVT (https://postgis.net/docs/ST_AsMVT.html) actually does support outputting more than just geometries - maybe that's new, I don't know.

From the docs:

The Mapbox Vector Tile format can store features with a different set of attributes per feature. To make use of this feature supply a JSONB column in the row data containing Json objects one level deep. The keys and values in the object will be parsed into feature attributes.